Specifying row blocking to reduce overhead

Row blocking, which is supported for all statements and data types (including LOB data types), reduces database manager overhead for cursors by retrieving a block of rows in a single operation.

About this task

This block of rows represents a number of pages in memory. It is not a multidimensional clustering (MDC) or insert time clustering (ITC) table block, which is physically mapped to an extent on disk.

Row blocking is specified by the following options on the BIND or PREP command:

BLOCKING ALL: Cursors that are declared with the FOR READ ONLY clause or that are not specified as FOR UPDATE will be blocked.
BLOCKING NO: Cursors will not be blocked.
BLOCKING UNAMBIG: Cursors that are declared with the FOR READ ONLY clause will be blocked. Cursors that are not declared with the FOR READ ONLY clause or the FOR UPDATE clause, that are not ambiguous, or that are read-only, will be blocked. Ambiguous cursors will not be blocked.

The following database manager configuration parameters are used during block-size calculations.

The aslheapsz parameter specifies the size of the application support layer heap for local applications. It is used to determine the I/O block size when a blocking cursor is opened.
The rqrioblk parameter specifies the size of the communication buffer between remote applications and their database agents on the database server. It is also used to determine the I/O block size at the data server runtime client when a blocking cursor is opened.

Before enabling the blocking of row data for LOB data types, it is important to understand the impact on system resources. More shared memory will be consumed on the server to store the references to LOB values in each block of data when LOB columns are returned. The number of such references will vary according to the value of the rqrioblk configuration parameter.

To increase the amount of memory allocated to the heap, modify the database_memory database configuration parameter by:

Setting its value to AUTOMATIC
Increasing its value by 256 pages if the parameter is currently set to a user-defined numeric value

To increase the performance of an existing embedded SQL application that references LOB values, rebind the application using the BIND command and specifying either the BLOCKING ALL clause or the BLOCKING UNAMBIG clause to request blocking. Embedded applications will retrieve the LOB values, one row at a time, after a block of rows has been retrieved from the server. User-defined functions (UDFs) returning LOB results might cause the Db2® server to revert to single-row retrieval of LOB data when large amounts of memory are being consumed on the server.

Procedure

To specify row blocking:

Use the values of the aslheapsz and rqrioblk configuration parameters to estimate how many rows are returned for each block. In both formulas, orl is the output row length, in bytes.
- Use the following formula for local applications:
```
   Rows per block = aslheapsz * 4096 / orl
```
  The number of bytes per page is 4096.
- Use the following formula for remote applications:
```
   Rows per block = rqrioblk / orl
```
To enable row blocking, specify an appropriate value for the BLOCKING option on the BIND or PREP command.
If you do not specify the BLOCKING option, the default row blocking type is UNAMBIG. For the command line processor (CLP) and the call-level interface (CLI), the default row blocking type is ALL.