Adding columns to the set of index keys of a unique index

You can use the ALTER INDEX statement to specify that additional columns be appended to the set of index key columns of a unique index.

About this task

Restriction: You cannot add columns to IBM®-defined indexes on the Db2 catalog.

If you want to add a column to a unique index to allow index-only access of the data, you first must determine whether existing indexes on a unique table are being used to query the table. You can use the RUNSTATS utility, real-time statistics, or the EXPLAIN statement to find this information. Those indexes with the unique constraint in common are candidates for consolidation. Other non-unique indexes might be candidates for consolidation, depending on their frequency of use.

Procedure

To specify that additional columns be appended to the set of index key columns of a unique index:

  1. Issue the ALTER INDEX statement with the INCLUDE clause.
    Any column that is included with the INCLUDE clause is not used to enforce uniqueness. These included columns might improve the performance of some queries through index only access. Using this option might eliminate the need to access data pages for more queries and might eliminate redundant indexes.
  2. Commit the alter procedure.
    As a result of this alter procedure, the index is placed into page set REBUILD-pending (PSRBD) status, because the additional columns preexisted in the table.
  3. To remove the PSRBD status from the index, complete one of the following options:
    • Run the REBUILD INDEX utility on the index that you ran the alter procedure on.
    • Run the REORG TABLESPACE utility on the index that you ran the alter procedure on, or you can wait to run the alter procedure until just before the REORG TABLESPACE utility is scheduled to run.
  4. Run the RUNSTATS utility.
    The results will be used after the next step.
  5. Perform REBIND on the static plans and packages.
  6. Run the EXPLAIN statement to verify that the optimizer is choosing the index with the included columns.
  7. Drop the indexes that are consolidated and no longer needed.
  8. Verify that the new index is satisfying your query needs by using the RUNSTATS utility, real-time statistics, or the EXPLAIN statement.