Altering Db2 indexes

You can add a new column to an index or change the description of an index at the current server by issuing the ALTER INDEX statement.

About this task

With the ALTER INDEX statement, you can:

  • Add a new column to an index.
  • Alter the PADDED or NOT PADDED attribute to change how varying-length columns are stored in the index.
  • Alter the CLUSTER or NOT CLUSTER attribute to change how data is stored.
  • Alter the compression setting using ALTER COMPRESS YES or ALTER COMPRESS NO.
  • Change the limit key for index-controlled partitioning to rebalance data among the partitions in a partitioned table space.

For other changes, you must drop and re-create the index.

When you add a new column to an index, change how varying-length columns are stored in the index, or change the data type of a column in the index, Db2 creates a new version of the index.
Restrictions:
  • If the padding of an index is changed, the index is placed in REBUILD-pending (RBDP) status and a new version of the index is not created.
  • For an index that is not in a universal table space, any alteration to use index compression places the index in REBUILD-pending (RBDP) status. For indexes in universal table spaces, alterations to index compression are a pending change that place the index in advisory REORG-pending (AREOR) status.
  • You cannot add a column with the DESC attribute to an index if the column is a VARBINARY column or a column with a distinct type that is based on the VARBINARY type.

Procedure

To change the description of an index at the current server:

Issue the ALTER INDEX statement.
The ALTER INDEX statement can be embedded in an application program or issued interactively.