SQL-based continuous block fetch

With SQL-based continuous block fetch, you enable a Db2 client to request multiple query blocks on each transmission. This can reduce network activity and improve performance significantly for applications that use DRDA access to download large amounts of data.

To enable SQL-based continuous block fetch, you include the OPTIMIZE FOR n ROWS clause in each SELECT statement in the application, and set the EXTRASRV subsystem parameter on the server. For a Db2 client, you also set the EXTRAREQ subsystem parameter on the client.

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 non-scrollable cursor. If n is greater than the number of rows that fit in a DRDA query block, OPTIMIZE FOR n ROWS lets the DRDA client request multiple blocks of query data on each network transmission instead of requesting a new block when the first block is full. This use of OPTIMIZE FOR n ROWS is intended only for applications in which the application opens a cursor and downloads great amounts of data. The OPTIMIZE FOR n ROWS clause has no effect on scrollable cursors.

Recommendation: Because the application SQL uses only one connection, do not try to do other SQL work until the entire result set is processed. If the application issues another SQL statement before the previous statement's result set has been received, DDF must buffer them in its address space. You can buffer up to 10 MB in this way.

Because specifying a large number of network blocks can saturate the network, limit the number of blocks according to what your network can handle. You can limit the number of blocks used for these large download operations. When the client supports extra query blocks, Db2 chooses the smallest of the following values when determining the number of query blocks to send:

  • The number of blocks into which the number of rows (n) on the OPTIMIZE clause can fit. For example, assume you specify 10000 rows for n, and the size of each row that is returned is approximately 100 bytes. If the block size used is 32 KB (32768 bytes), the calculation is as follows: 
    (10000 * 100) / 32768 = 31 blocks
  • The Db2 server value for the EXTRASRV subsystem parameter. The maximum value that you can specify is 100.
  • The client's extra query block limit, which is obtained from the DRDA MAXBLKEXT parameter received from the client. When Db2 for z/OS® acts as a DRDA client, you set this parameter at installation time with the EXTRAREQ subsystem parameter. The maximum value that you can specify is 100.

If the client does not support extra query blocks, the Db2 server on z/OS automatically reduces the value of n to match the number of rows that fit within a DRDA query block.

Recommendation for cursors that are defined WITH HOLD: Do not set a large number of query blocks for cursors that are defined WITH HOLD. If the application commits while there are still a lot of blocks in the network, Db2 buffers the blocks in the requester's memory (the ssnmDIST address space if the requester is a Db2 for z/OS) before the commit can be sent to the server.

When you configure your Db2 for z/OS server and requester for SQL-based continuous block fetch, the requester and server perform the following actions when they use continuous block fetch:
  1. The requester sends a message to open a cursor and begins fetching the block of rows at the server.
  2. The server sends back a block of rows and the requester begins processing the first row.
  3. The server continues to send blocks of rows to the requester, without further prompting, until the computed number of extra query blocks have been sent. The requester processes the second and later rows as usual, but fetches them from a buffer on the requester's system.