Enabling block fetch for distributed applications

Block fetch can significantly decrease the number of messages sent across the network.

About this task

Introductory concepts

With block fetch, Db2 groups the rows that are retrieved by an SQL query into as large a block of rows as can fit in a message buffer. Db2 then transmits the block over the network, without requiring a separate message for each row.

Db2 can use different types of block fetch:

  • Limited block fetch
  • Continuous block fetch

To enable limited or continuous block fetch, Db2 must determine that the cursor is not used for updating or deleting. Block fetch is used only with cursors that do not update or delete data.

Db2 triggers block fetch for static SQL only when it can detect that no updates or deletes are in the application. For dynamic statements, because Db2 cannot detect what follows in the program, the decision to use block fetch is based on the declaration of the cursor.

Db2 does not use continuous block fetch if the following conditions are true:
  • The cursor is referred to in the statement DELETE WHERE CURRENT OF elsewhere in the program.
  • The cursor statement appears that it can be updated at the requesting system. (Db2 does not check whether the cursor references a view at the server that cannot be updated.)

Procedure

To ensure that Db2 uses block fetch:

Begin general-use programming interface information. The easiest way to indicate that the cursor does not modify data is to add the FOR FETCH ONLY or FOR READ ONLY clause to the query in the DECLARE CURSOR statement as in the following example: 
EXEC SQL
  DECLARE THISEMP CURSOR FOR
    SELECT EMPNO, LASTNAME, WORKDEPT, JOB
    FROM DSN8C10.EMP
    WHERE WORKDEPT = 'D11'
    FOR FETCH ONLY
END-EXEC.
If you do not use FOR FETCH ONLY or FOR READ ONLY, Db2 still uses block fetch for the query if the following conditions are true:
  • The cursor is a non-scrollable cursor, and the result table of the cursor is read-only. This applies to static and dynamic cursors except for read-only views.
  • For limited block fetch or SQL-based continuous block fetch only:
    • The cursor is a scrollable cursor that is declared as INSENSITIVE, and the result table of the cursor is read-only.
    • The cursor is a scrollable cursor that is declared as SENSITIVE, the result table of the cursor is read-only, and the value of bind option CURRENTDATA is NO.
    • The result table of the cursor is not read-only, but the cursor is ambiguous, and the value of bind option CURRENTDATA is NO. A cursor is ambiguous when:
      • It is not defined with the clauses FOR FETCH ONLY, FOR READ ONLY, or FOR UPDATE OF.
      • It is not defined on a read-only result table.
      • It is not the target of a WHERE CURRENT clause on an SQL UPDATE or DELETE statement.
      • It is in a plan or package that contains the SQL statements PREPARE or EXECUTE IMMEDIATE.

Results

The following tables summarize the conditions under which a Db2 server uses block fetch.

The following table shows the conditions for a non-scrollable cursor.
Table 1. Effect of CURRENTDATA and cursor type on block fetch for a non-scrollable cursor
Isolation level CURRENTDATA Cursor type Block fetch
CS, RR, or RS Yes Read-only Yes
Updatable No
Ambiguous No
No Read-only Yes
Updatable No
Ambiguous Yes
UR Yes Read-only Yes
No Read-only Yes
The following table shows the conditions for a scrollable cursor that is not used to retrieve a stored procedure result set.
Table 2. Effect of CURRENTDATA and isolation level on block fetch for a scrollable cursor that is not used for a stored procedure result set
Isolation level Cursor sensitivity CURRENTDATA Cursor type Block fetch
CS, RR, or RS INSENSITIVE Yes Read-only Yes
No Read-only Yes
SENSITIVE Yes Read-only No
Updatable No
Ambiguous No
No Read-only Yes
Updatable No
Ambiguous Yes
UR INSENSITIVE Yes Read-only Yes
No Read-only Yes
SENSITIVE Yes Read-only Yes
No Read-only Yes
The following table shows the conditions for a scrollable cursor that is used to retrieve a stored procedure result set.
Table 3. Effect of CURRENTDATA and isolation level on block fetch for a scrollable cursor that is used for a stored procedure result set
Isolation level Cursor sensitivity CURRENTDATA Cursor type Block fetch
CS, RR, or RS INSENSITIVE Yes Read-only Yes
No Read-only Yes
SENSITIVE Yes Read-only No
No Read-only Yes
UR INSENSITIVE Yes Read-only Yes
No Read-only Yes
SENSITIVE Yes Read-only Yes
No Read-only Yes
End general-use programming interface information.