Altering DB2 indexes

FUNCTION OF ALTER INDEX :

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:

v Add a new column to an index.

v Alter the PADDED or NOT PADDED attribute to change how varying-length

columns are stored in the index.

v Alter the CLUSTER or NOT CLUSTER attribute to change how data is stored.

v Alter the compression setting using ALTER COMPRESS YES or ALTER

COMPRESS NO.

v 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 recreate 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.

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 a column to an index that enforces a primary key,

unique key, or referential constraint. Also, you cannot add columns to IBM-defined

indexes on the DB2 catalog.

Procedure

To add a column to an existing index:

1. Issue the ALTER INDEX ADD COLUMN SQL statement when you add a

column to a table.

2. 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.

需要注意的是：修改索引的方式不同，可能导致的索引的状态也肯能是不同的，

一个是，列已经存在，然后你修改索引，增加列，这是索引的状态被置为REBUILD-pending (RBDP) status.

一个是，表先增加列，然后将该列增加到索引中，这是索引的状态被置为 REORG-pending (AREO*) status

The total number of columns for the index cannot exceed 64.

create index 

DEFINE

Specifies when the underlying data sets for the index are physically created.

The SPACE column in catalog table SYSINDEXPART is used to record the

status of the data sets (undefined or allocated).

YES

The data sets are created when the index is created (the CREATE INDEX

statement is executed). YES is the default.

NO The data sets are not created until data is inserted into the index.

DEFER

Indicates whether the index is built during the execution of the CREATE

INDEX statement. Regardless of the option specified, the description of the

index and its index space is added to the catalog. If the table is determined to

be empty and DEFER YES is specified, the index is neither built nor placed in

a rebuild-pending status. Refer to DB2 Administration Guide for more

information about using DEFER. Do not specify DEFER for an index on a

declared temporary table or an auxiliary table.

NO The index is built. This is the default.

YES

The index is not built. If the table is populated, the index is placed in a

rebuild-pending status and a warning message is issued; the index must be

rebuilt by the REBUILD INDEX utility.