Estimating storage for LOBs

Before calculating the storage that is required for LOB table spaces, you must understand the size restrictions for large object (LOBs) data types.

About this task

Tables with LOBs can store byte strings up to 2 GB. A base table can be defined with one or more LOB columns. The LOB columns are logically part of the base table but are physically stored in an auxiliary table. In place of each LOB column, there is an indicator column, which is a column with descriptive information about the LOB. When a base table has LOB columns, then each row of the table has a row identifier, which is a varying-length 17-byte field. You must consider the overhead of the indicator column and row identifiers when estimating table size. If the LOB column is NULL or has a value of zero, no space is allocated in the auxiliary table.

Procedure

To estimate the storage required for LOB table spaces, complete the following steps:

  1. Begin with your estimates from other table spaces
  2. Round the figure up to the next page size
  3. Multiply the figure by 1.1

What to do next

An auxiliary table resides in a LOB table space. There can be only one auxiliary table in a LOB table space. An auxiliary table can store only one LOB column of a base table and there must be one and only one index on this column.

One page never contains more than one LOB. When a LOB value is deleted, the space occupied by that value remains allocated as long as any application might access that value.

When a LOB table space grows to its maximum size, no more data can be inserted into the table space or its associated base table.

Enabling LOB data compression might reduce the size of the data to allow the LOB table space to contain more data.