Administration Guide

Row Blocking

Row blocking is a technique that reduces database manager overhead by retrieving a block of rows in a single operation. These rows are stored in a cache, and each FETCH request in the application gets the next row from the cache. When all the rows in a block have been processed, another block of rows is retrieved by the database manager.

The cache is allocated when an application issues an OPEN CURSOR request and is deallocated when the cursor is closed. The size of the cache is determined by a configuration parameter which is used to allocate memory for the I/O block. The parameter used depends on whether the client is local or remote:

• For local applications, the parameter aslheapsz is used to allocate the cache for row blocking. (See Application Support Layer Heap Size (aslheapsz) for information about this parameter.)

• For remote applications, the parameter rqrioblk on the client workstation is used to allocate the cache for row blocking. The cache is allocated on the database client. (See Client I/O Block Size (rqrioblk) for information about this parameter.)

For local applications, you can use the following formula to estimate how many rows are returned per block, where:

• aslheapsz is in pages of memory
• 4 096 is the number of bytes per page
• orl is the output row length in bytes:

Rows per block = aslheapsz * 4096 / orl

For remote applications, you can use the following formula to estimate how many rows are returned per block, where:

• rqrioblk is in bytes of memory
• orl is the output row length in bytes:

Rows per block = rqrioblk / orl

Note that if you use the FETCH FIRST n ROWS ONLY clause or the OPTIMIZE FOR n ROWS clause in a SELECT statement, the number of rows per block will be the minimum of the following:

• The value calculated in the above formula
• The value of n in the FETCH FIRST clause
• The value of n in the OPTIMIZE FOR clause

Use the BLOCKING option on the PREP and BIND commands to specify one of the following types of row blocking:

UNAMBIG

Blocking occurs for read-only cursors and cursors not specified as "FOR UPDATE OF". Ambiguous cursors are treated as updateable.

ALL

Blocking occurs for read-only cursors and cursors not specified as "FOR UPDATE OF". Ambiguous cursors are treated as read-only.

NO

Blocking does not occur for any cursors. Ambiguous cursors are treated as read-only.

For details of these types of row blocking, refer to the PREP and BIND command descriptions in the Command Reference manual.

If no option is specified on the PREP and BIND commands, the default row blocking type is UNAMBIG. For the command line processor and call level interface, the default row blocking type is ALL.

Refer to the SQL Reference for more information about cursors.

[ DB2 List of Books | Search the DB2 Books ]