Optimizing for small result sets for DRDA

When a client does not need all the rows from a potentially large result set, preventing the Db2 server from returning all the rows for a query can reduce network activity and improve performance significantly for DRDA applications.

About this task

Begin general-use programming interface information. You can use either the OPTIMIZE FOR n ROWS clause or the FETCH FIRST n ROWS ONLY clause of a SELECT statement to limit the number of rows returned to a client program.

Using OPTIMIZE FOR n ROWS: When you specify OPTIMIZE FOR n ROWS, and n is less than the number of rows that fit in the DRDA query block (default size on z/OS® is 32 KB), the Db2 server does not fill the query block with data. Instead, it prefetches and returns only n rows. For example, if the client application is interested in seeing only one screen of data, specify OPTIMIZE FOR n ROWS, choosing a small number for n, such as 3 or 4. The OPTIMIZE FOR n ROWS clause has no effect on scrollable cursors.

Using FETCH FIRST n ROWS ONLY: The FETCH FIRST n ROWS ONLY clause does not affect network blocking. If FETCH FIRST n ROWS ONLY is specified and OPTIMIZE FOR n ROWS is not specified, Db2 uses the FETCH FIRST value to optimize the access path. However, DRDA does not consider this value when it determines network blocking.

When both the FETCH FIRST n ROWS ONLY clause and the OPTIMIZE FOR n ROWS clause are specified, the value for the OPTIMIZE FOR n ROWS clause is used for access path selection.

Example: Suppose that you submit the following SELECT statement:

SELECT * FROM EMP
FETCH FIRST 5 ROWS ONLY
OPTIMIZE FOR 20 ROWS;

The OPTIMIZE FOR value of 20 rows is used for network blocking and access path selection.

When you use FETCH FIRST n ROWS ONLY, Db2 might use a fast implicit close. Fast implicit close means that during a distributed query, the Db2 server automatically closes the cursor when it prefetches the nth row if FETCH FIRST n ROWS ONLY is specified or when there are no more rows to return. Fast implicit close can improve performance because it can save an additional network transmission between the client and the server.

Db2 uses fast implicit close when the following conditions are true:
  • The query uses limited block fetch.
  • The query retrieves no LOBs.
  • The query retrieves no XML data.
  • The cursor is not a scrollable cursor.
  • Either of the following conditions is true:
    • The cursor is declared WITH HOLD, and the package or plan that contains the cursor is bound with the KEEPDYNAMIC(YES) option.
    • The cursor is declared WITH HOLD and the DRDA client passes the QRYCLSIMP parameter set to SERVER MUST CLOSE or SERVER DECIDES.
    • The cursor is not defined WITH HOLD.

When you use FETCH FIRST n ROWS ONLY and Db2 does a fast implicit close, the Db2 server closes the cursor after it prefetches n rows, or when there are no more rows.End general-use programming interface information.