Large object table spaces

Large object (LOB) table spaces (also known as auxiliary table spaces) hold large object 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.

Start of changeIf 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.End of change

Start of changeThe 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. End of change

Start of change
Table 1. Base table space types and approximate maximum size of LOB data for a LOB column
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
End of change
Recommendations:
  • 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.
  • Consider specifying a separate buffer pool for large object data.