How Db2 implicitly creates an index

In certain circumstances, Db2 implicitly creates the unique indexes that are used to enforce the uniqueness of the primary keys or unique keys.

These circumstances include:
  • When the PRIMARY KEY or UNIQUE clause is specified in the CREATE TABLE statement and the CREATE TABLE statement is processed by the schema processor
  • When the table space that contains the table is implicitly created
When a ROWID column is defined as GENERATED BY DEFAULT in the CREATE TABLE statement, and the CREATE TABLE statement is processed by SET CURRENT RULES = 'STD', or the table space that contains the table is implicitly created, Db2 implicitly creates the unique indexes used to enforce the uniqueness of the ROWID column. The privilege set must include the USE privilege of the buffer pool. Each index is created as if the following CREATE INDEX statement were issued: 
CREATE UNIQUE INDEX xxx ON table-name (column1,...)
Where:
  • xxx is the name of the index that Db2 generates.
  • table-name is the name of the table that is specified in the CREATE TABLE statement.
  • (column1,...) is the list of column names that were specified in the UNIQUE or PRIMARY KEY clause of the CREATE TABLE statement, or the column is a ROWID column that is defined as GENERATED BY DEFAULT.

In addition, if the table space that contains the table is implicitly created, Db2 will check the DEFINE DATA SET subsystem parameter to determine whether to define the underlying data set for the index space of the implicitly created index on the base table.

If DEFINE DATA SET is NO, the index is created as if the following CREATE INDEX statement is issued: 
CREATE UNIQUE INDEX xxx ON table-name (column1,...) DEFINE NO

When you create a table and specify the organization-clause of the CREATE TABLE statement, Db2 implicitly creates an index for hash overflow rows. This index contains index entries for overflow rows that do not fit in the fixed hash space. If the hash space that is specified in the organization-clause is adequate, the hash overflow index should have no entries, or very few entries. The hash overflow index for a table in a partition-by-range table space is a partitioned index. The hash overflow index for a table in a partition-by-growth table space is a non-partitioned index.

Db2 determines how much space to allocate for the hash overflow index. Because this index will be sparsely populated, the size is relatively small compared to a normal index.