Block fetch

You can use block fetch to retrieve a set of rows and transmit them all in one message over the network.

DB2® uses a block fetch to group the rows that an SQL query retrieves into as large a "block" of rows as can fit in a message buffer, and then transmits the block over the network. By sending multiple rows in a block, DB2 avoids sending a message for every row.

A block fetch is used only with cursors that do not update data.

DB2 can use two different types of block fetch:

Limited block fetch

An operation that optimizes data transfer by minimizing the number of messages that are transmitted from the requester whenever a remote fetch operation is performed.

Continuous block fetch

An operation that sends a single request from the requester to the server. The server fills a buffer with data that it retrieves and transmits it back to the requester. Processing at the requester is asynchronous with the server; the server continues to send blocks of data to the requester with minimal or no further prompting.

To use block fetch, DB2 must determine that the cursor is not used for update or delete. You can indicate in your program by adding the clause FOR READ ONLY or FOR FETCH ONLY to the query. If you do not specify FOR READ ONLY or FOR FETCH ONLY, the way in which DB2 uses the cursor determines whether it uses block fetch. For scrollable cursors, the sensitivity of the cursor and the bind options affect whether DB2 can use block fetch.