Programming techniques for database performance: Improve SQL blocking performance when using FETCH FOR n ROWS

Special performance considerations should be made for the following points when using FETCH FOR n ROWS. You can improve SQL blocking performance with the following:

• The attribute information in the host structure array or the descriptor associated with the row storage area should match the attributes of the columns retrieved.
• The application should retrieve as many rows as possible with a single multiple-row FETCH call. The blocking factor for a multiple-row FETCH request is not controlled by the system page sizes or the SEQONLY parameter on the OVRDBF command. It is controlled by the number of rows that are requested on the multiple-row FETCH request.
• Single- and multiple-row FETCH requests against the same cursor should not be mixed within a program. If one FETCH against a cursor is treated as a multiple-row FETCH, all fetches against that cursor are treated as multiple-row fetches. In that case, each of the single-row FETCH requests would be treated as a multiple-row FETCH of one row.
• The PRIOR, CURRENT, and RELATIVE scroll options should not be used with multiple-row FETCH statements. To allow random movement of the cursor by the application, the database manager must maintain the same cursor position as the application. Therefore, the SQL run-time treats all FETCH requests against a scrollable cursor with these options specified as multiple-row FETCH requests.