Improve SQL blocking performance when using FETCH FOR n ROWS

Use these performance techniques to improve SQL blocking performance when using FETCH FOR n ROWS.

You can improve SQL blocking performance with the following:

Match the attribute information in the host structure array or the descriptor associated with the row storage area with the attributes of the columns retrieved.
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.
Do not mix single- and multiple-row FETCH requests against the same cursor 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 is treated as a multiple-row FETCH of one row.
Do not use the PRIOR, CURRENT, and RELATIVE scroll options 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.