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.
Procedure
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.