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 optimizes data transfer by minimizing the number of messages that are transmitted from the requester to retrieve the entire result set. In addition, overlapped processing is performed at the requester and the server.

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.