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 a column is added to a table and index in the same commit scope, the index can be placed in advisory REORG-pending (AREO*) state and be available for immediate use. However, the following situations cause the index to be placed in REBUILD-pending (RBDP) status:

Rows are also inserted in the table in the same commit scope when the column is added to the table and index.
column-name specifies is a ROWID column

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.