The PREFETCH pragma controls the block mode of the SQL statement FETCH (for positioning the cursor). Block mode accelerates the execution of the FETCH statement. It is effective only when FETCH positions the cursor on the next record in the cursor table (FETCH NEXT...).
The PREFETCH pragma allows you to activate block mode and specify a blocking factor (n). When the first FETCH NEXT... statement is executed, the column values of the current record are read, and the next n -1 records of the associated cursor table are stored in a buffer. When the next n-1 FETCH NEXT... statements that specify the same cursor are executed, the next record can be accessed directly without involving the DBH.
The PREFETCH pragma is effective only in the following SQL statements:
DECLARE CURSOR (for a static cursor)
cursor description (for dynamic cursors)
If the cursor description of the DECLARE CURSOR statement or the cursor description for dynamic cursors contains a FOR UPDATE clause, the PREFETCH pragma is ignored and block mode is not activated.
When block mode is activated, it makes the cursor defined in the DECLARE CURSOR statement or the cursor description the prefetch cursor.
Block mode cursors are not supported in linked-in mode.
PREFETCH blocking_factor
blocking_factor
You must enter an integer without a preceding sign as the blocking factor (data type SMALLINT).
If the blocking factor (n) is greater than 0, up to n-1 records of the specified cursor table are stored in a buffer.
If the blocking factor is 0, the PREFETCH pragma has no effect.
You can enable/disable the pragma and thus activate/deactivate block mode by specifying either a value greater than 0 or the value 0 itself for n.
When block mode is activated, the following restrictions apply:
Only the FETCH NEXT statement is permitted for the prefetch cursor cursor in the same compilation unit. The following SQL statements can no longer be executed:
UPDATE ... WHERE CURRENT of cursor
DELETE ... WHERE CURRENT of cursor
STORE cursor
FETCH cursor with a cursor position other than NEXT or with a different INTO clause to the first FETCH NEXT statement.
After the execution of a FETCH NEXT statement whose INTO clause contains the name of an SQL descriptor area, this SQL descriptor area must not be modified by a SET DESCRIPTOR, DESCRIBE or DEALLOCATE DESCRIPTOR statement.
The prefetch cursor must always be addressed by the same FETCH NEXT statement, i.e. by the same statement in a loop or subroutine.