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 (MDC) 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.
To specify row blocking: