Continuous block fetch

In terms of response time, continuous block fetch is most efficient for larger result sets because fewer messages are transmitted from the requester to retrieve the entire result set and because overlapped processing is performed at the requester and the server.

Introductory concepts

However, continuous block fetch uses more networking resources than limited block fetch. When networking resources are critical, use limited block fetch to run applications.

The requester can use both forms of blocking at the same time and with different servers.

If an application is doing read-only processing and can use continuous block fetch, the sequence goes like this:
  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. The requester processes the second and later rows as usual, but fetches them from a buffer on the requester's system.
For DRDA, only one conversation is used, and it must be made available to the other SQL statements that are in the application. Thus, the server usually sends back a subset of all the rows. The number of rows that the server sends depends on the following factors:
  • The size of each row
  • The number of extra blocks that are requested by the requesting system compared to the number of extra blocks that the server returns

    For a DB2® for z/OS® requester, the EXTRA BLOCKS REQ field on installation panel DSNTIP5 determines the maximum number of extra blocks requested. For a DB2 for z/OS server, the EXTRA BLOCKS SRV field on installation panel DSNTIP5 determines the maximum number of extra blocks allowed.

    Example: Suppose that the requester asks for 100 extra query blocks and that the server allows only 50. The server returns no more than 50 extra query blocks. The server might choose to return fewer than 50 extra query blocks for any number of reasons that DRDA allows.

  • Whether continuous block fetch is enabled, and the number of extra rows that the server can return if it regulates that number.

    To enable continuous block fetch for DRDA and to regulate the number of extra rows sent by a DB2 for z/OS server, you must use the OPTIMIZE FOR n ROWS clause on your SELECT statement.

If you want to use continuous block fetch for DRDA, have the application fetch all the rows of the cursor before doing any other SQL. Fetching all the rows first prevents the requester from having to buffer the data, which can consume a lot of storage. Choose carefully which applications should use continuous block fetch for DRDA.