Adding a column to an index when you add the column to a table

When you use the ALTER INDEX statement to add a column to an existing index, the new column becomes the rightmost column of the index key.

About this task

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

Procedure

To add a column to an existing index:

Issue the ALTER INDEX ADD COLUMN SQL statement when you add a column to a table.
Commit the alter procedure.

Results

If the column that is being added to the index is already part of the table on which the index is defined, the index is left in a REBUILD-pending (RBDP) status. However, if you add a new column to a table and to an existing index on that table within the same unit of work, the index is left in advisory REORG-pending (AREO*) status and can be used immediately for data access.

If you add a column to an index and to a table within the same unit of work, this will cause table and index versioning.

Example

Begin general-use programming interface information. For example, assume that you created a table with columns that include ACCTID, STATE, and POSTED:

CREATE TABLE TRANS
   (ACCTID ..., 
    STATE ...,
    POSTED ...,
    ... , ...)
    ...;

You have an existing index on the STATE column:

CREATE INDEX STATE_IX ON TRANS(STATE);

To add a ZIPCODE column to the table and the index, issue the following statements:

ALTER TABLE TRANS ADD COLUMN ZIPCODE CHAR(5);
ALTER INDEX STATE_IX ADD COLUMN (ZIPCODE);
COMMIT;

Because the ALTER TABLE and ALTER INDEX statements are executed within the same unit of work, Db2 immediately can use the new index with the key STATE, ZIPCODE for data access.

End general-use programming interface information.