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, you can use the following formula to estimate how many rows are returned per block, where:
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:
Rows per block = rqrioblk / orl
Note that if you use the OPTIMIZE FOR n ROWS clause in a SELECT statement, the number of rows per block will be the minimum of the following:
Use the BLOCKING option on the PREP and BIND commands to specify one of the following types of row blocking:
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.