LOB table spaces
Large object (LOB) table spaces (also known as LOB or auxiliary table spaces) hold LOB data, such as graphics, video, or large text strings. If your data does not fit entirely within a data page, you can define one or more columns as LOB columns.
LOB objects can do more than store large object data. If you define your LOB columns for infrequently accessed data, a table space scan on the remaining data in the base table is potentially faster because the scan generally includes fewer pages.
A LOB table space always has a direct relationship with the table space that contains the logical LOB column values. The table space that contains the table with the LOB columns is, in this context, the base table space. LOB data is logically associated with the base table, but it is physically stored in an auxiliary table that resides in a LOB table space. Only one auxiliary table can exist in a large object table space. A LOB value can span several pages. However, only one LOB value is stored per page.
You must have a LOB table space for each LOB column that exists in a table. For example, if your table has LOB columns for both resumes and photographs, you need one LOB table space (and one auxiliary table) for each of those columns. If the base table space is a partitioned table space, you need one LOB table space for each LOB in each partition.
Db2 sometimes implicitly creates the LOB table space, auxiliary table, and index on the auxiliary table for each LOB column in a table or partition. For more information, see LOB table space implicit creation.
If Db2 does not implicitly create the LOB table spaces, auxiliary tables, and indexes on the auxiliary tables, you must create these objects by issuing CREATE TABLESPACE, CREATE AUXILIARY TABLE, and CREATE INDEX statements.
If the base table space is not a partitioned table space, each LOB table space is associated with one LOB column in the base table. If the base table space is a partitioned table space, each partition of the base table space is associated with a LOB table space. Therefore, if the base table space is a partitioned table space, you can store more LOB data for each LOB column.
The following table shows the approximate amount of LOB data that you can store for a LOB column in each of the different types of base table spaces.
Base table space type | Maximum (approximate) LOB data for each column |
---|---|
Segmented | 16 TB |
Partitioned, with NUMPARTS up to 64 | 1000 TB |
Partitioned with DSSIZE, NUMPARTS up to 254 | 4000 TB |
Partitioned with DSSIZE, NUMPARTS up to 4096 | 64000 TB |
Consider defining long string columns as LOB columns when a row does not fit in a 32 KB page. Use the following guidelines to determine if a LOB column is a good choice:
- Defining a long string column as a LOB column might be better if the following conditions are true:
- Table space scans are normally run on the table.
- The long string column is not referenced often.
- Removing the long string column from the base table is likely to improve the performance of table space scans.
- LOBs are physically stored in another table space. Therefore, performance for inserting, updating, and retrieving long strings might be better for non-LOB strings than for LOB strings.
Also Consider specifying a separate buffer pool for large object data.