Improving performance for LOB data

You can improve the performance of applications that access LOB data by specifying that an inline portion of LOB data columns be stored in the base table space along with data from the other non-LOB columns.

About this task

An inline LOB allows a portion of LOB data to reside in the base table space with the data from non-LOB columns. For LOBs of a size less than or equal to the specified inline length, DB2® stores the complete LOB data in the base table space. DB2 does not need to access the LOB table space or auxiliary indexes for processes that access the LOB data.

In such cases, DB2 can access the data at similar cost, in terms of CPU time and elapsed time, for comparable non-LOB data types. The amount of disk space that is used for LOB data is also reduced when the LOB data can be stored inline in the base table space.

For LOBs of a size greater than the specified inline length, the inline portion of the LOB resides in the base table space, and DB2 stores the remainder of the LOB in the LOB table space. In this case, any process that accesses the LOB data must access both the base table space and the LOB table space.

The benefits of inline LOBs are greatest for frequently accessed LOB columns. For LOB column that are accessed only rarely, the presence of the inline LOB data in the base table might reduce the number of row-per-page enough to incur increased I/O costs that outweigh any benefits of keeping the LOB data inline.

To specify a length for inline LOBs, use the following approaches:

• Use the LOB_INLINE_LENGTH subsystem parameter to specify a default inline length for any new LOB column in universal table spaces on the DB2 subsystem. Valid values for the LOB_INLINE_LENGTH subsystem parameter are 0 to 32680 inclusive, in bytes. The default value is 0, which means no inline attribute is used for LOB columns. A non-zero value specifies that new LOB columns created on the subsystem have an inline attribute, and the value indicates how many bytes of data DB2 stores in the base table space with data from non-LOB columns. For example, assuming that 1001 is specified for the value of the LOB_INLINE_LENGTH parameter:
  • If the length of the LOB data is 200 bytes, DB2 stores all 200 bytes in the base table space.
  • If the length of the LOB is 2000 bytes, DB2 stores 1001 bytes in the base table space, and 999 bytes in the LOB table space.

  DB2 interprets the value specified for the LOB_INLINE_LENGTH parameter in bytes regardless of the data type or sub-type of the LOB column. If an odd number is specified for this parameter, DB2 rounds the value up to the next even number for any DBCLOB column.

• Specify the INLINE LENGTH clause of a CREATE TYPE statement. Any LOB-based column in a universal table space can inherit the inline attribute from the distinct type. You can specify a value from 0 to 32680 bytes inclusive for types based on BLOB or CLOB, and 0 to 16340 characters inclusive for types base on DBCLOB.
• Specify the INLINE LENGTH clause of a CREATE TABLE or ALTER TABLE ADD statement for a table in a universal table space. You can specify a value from 0 to 32680 inclusive for BLOB and CLOB columns, and from 0 to 16340 inclusive for DBCLOB columns. For example, consider the columns created by the following statements:

  CREATE TABLE myLOBtable 
  (myLOBcolumn DBCLOB (500K) INLINE LENGTH 300);

  If the actual length of the LOB is 200 bytes (100 characters) all 200 bytes are stored in the base table space. If the length of the LOB is 2000 bytes (1000 characters), 600 bytes (300 characters) are stored in the base table space and 1400 bytes (700 characters) are stored in the LOB table space.