Optimization for large and small result sets

Several options on the SELECT statement let you limit the number of rows that are returned to a client program.

Enabling a Db2 client to request that multiple query blocks on each transmission can reduce network activity and improve performance significantly for applications that use DRDA access to download large amounts of data.

You can specify a large value of n in the OPTIMIZE FOR n ROWS clause of a SELECT statement to increase the number of DRDA query blocks that a Db2 server returns in each network transmission for a nonscrollable cursor.

If n is greater than the number of rows that fit in a single DRDA query block, the OPTIMIZE FOR n ROWS clause lets the DRDA client request multiple blocks of query data on each network transmission instead of requesting another block when the first block is full. This use of the OPTIMIZE FOR n ROWS clause is intended for applications that open a cursor and download large amounts of data. The OPTIMIZE FOR n ROWS clause does not affect scrollable cursors.

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. 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 that are returned to a client program.