Index versions

Db2 uses index versions to maximize data availability. Index versions enable Db2 to keep track of schema changes and provides users with access to data in altered columns that are contained in indexes.

When users retrieve rows from a table with an altered column, the data is displayed in the format that is described by the most recent schema definition, even though the data is not currently stored in this format. The most recent schema definition is associated with the current index version.

Db2 creates an index version each time you commit one of the following schema changes:

Table 1. Situations when Db2 creates an index version
When you commit this change to a schema Db2 creates this type of corresponding index version
Use the ALTER TABLE statement to change the data type of a non-numeric column that is contained in one or more indexes. A new index version for each index that is affected by this operation.
Use the ALTER TABLE statement to change the length of a VARCHAR column that is contained in one or more PADDED indexes. A new index version for each index that is affected by this operation.
Use the ALTER TABLE statement to extend the length of a CHAR column in a table. A new index version for each index that is affected by this operation.
Use the ALTER INDEX statement to add a column to an index. One new index version; only one index is affected by this operation.

The index is set to REBUILD-pending status if the column was not added to the table in the same commit operation.
Add a new column to both a table and an index in the same commit operation. A new index version for each index that is affected by this operation.
Exceptions: Db2 does not create an index version under the following circumstances:
  • When the index was created with DEFINE NO
  • When you extend the length of a varying-length character (VARCHAR data type) or varying-length graphic (VARGRAPHIC data type) column that is contained in one or more indexes that are defined with the NOT PADDED option
  • When you specify the same data type and length that a column (which is contained in one or more indexes) currently has, such that its definition does not actually change

Db2 creates only one index version if, in the same unit of work, you make multiple schema changes to columns that are contained in the same index. If you make these same schema changes in separate units of work, each change results in a new index version.