Start of change

LOB table space implicit creation

Db2 can sometimes implicitly create a LOB table space and related objects when you create a LOB column or add a table partition that contains a LOB column.

When Db2 implicitly creates objects for LOB columns

When a CREATE TABLE statement creates a table that contains a LOB column, or an ALTER TABLE statement specifies ADD COLUMN to add a new column, Db2 implicitly creates one or more LOB table spaces, auxiliary tables, and auxiliary indexes if the following conditions are true:

  • Db2 implicitly created the table space that contains the base table with the LOB column.
  • The table space for the base table was created explicitly, but the CURRENT RULES special register value is 'STD' when the CREATE or ALTER TABLE statement executes.

Start of changeOtherwise, you must explicitly create these objects. See Creating LOB table spaces, auxiliary tables, and auxiliary indexes explicitly.End of change

For partitioned tables, each partition of the base table requires a separate LOB table space, auxiliary table, and auxiliary index for each LOB column.

When a new partition is added for to a table that contains an existing LOB column, Db2 always implicitly creates a LOB table space, auxiliary table, and auxiliary indexes in the following situations:

  • An ALTER TABLE statement specifies the ADD PARTITION clause to add a partition to a table that contains a LOB column.
  • Db2 adds a partition to a partition-by-growth table space.

Naming conventions for implicitly created objects for LOB columns

Db2 chooses the names of the implicitly created objects according to the following naming conventions:

LOB table space
The name of the LOB table spaces is 8 characters long, consisting of the letter 'L' and 7 random characters.
Auxiliary table
The name of the auxiliary table is 18 characters long. It consists of the first 5 characters of the name of the base table, the first 5 characters of the name of the LOB column, and 8 characters that are randomly generated. If a base table name or a LOB column name contains fewer than 5 characters, Db2 adds underscore characters to the name to pad the length of 5 characters.
Index on the auxiliary table
The name of the index on the auxiliary table is 18 characters long. It consists of the letter 'I', the first 10 characters of the auxiliary table name, and 7 characters that are randomly generated. The index has the COPY NO attribute.

Attributes of LOB table spaces for newly created LOB columns

The implicitly created LOB table space and related objects for new LOB columns have the following attributes:

  • Object names as specified in the preceding naming convention.
  • The database name is the database name of the base table.
  • The buffer pool specified by the TBSBPLOB subsystem parameter. The appropriate USE privilege is required on that buffer pool.
  • Other attributes use the default values of the respective CREATE statements with the optional clauses omitted.

Inherited attributes for new LOB table spaces for existing LOB columns

When a LOB table space is implicitly created for a new partition, it inherits the following attributes from the LOB table space for the previous base table partition:

  • BUFFERPOOL
  • CLOSERULE
  • COMPRESS
  • DSSIZE
  • ERASERULE
  • GBPCACHE
  • LOCKRULE
  • LOCKMAX
  • LOG
  • STORNAME
  • VCATNAME

If the base table is involved in a clone relationship, implicitly created LOB table spaces and implicitly created indexes are always created with the DEFINE YES attribute.

Identifying implicitly created objects for LOB columns

You can use the REPORT utility with the TABLESPACESET option to identify the LOB table spaces that Db2 implicitly created.

End of change