DB2 Version 9.7 for Linux, UNIX, and Windows

Dropping indexes

Other than changing the COMPRESSION attribute of an index, you cannot change any clause of an index definition; you must drop the index and create it again. (Dropping an index does not cause any other objects to be dropped but might cause some packages to be invalidated.) Use the DROP statement to drop indexes.

About this task

A primary key or unique key index cannot be explicitly dropped. You must use one of the following methods to drop it:

If the primary index or unique constraint was created automatically for the primary key or unique key, dropping the primary key or unique key will cause the index to be dropped. Dropping is done through the ALTER TABLE statement.
If the primary index or the unique constraint was user-defined, the primary key or unique key must be dropped first, through the ALTER TABLE statement. After the primary key or unique key is dropped, the index is no longer considered the primary index or unique index, and it can be explicitly dropped.

To drop an index using the command line, enter:

    DROP INDEX <index_name>

The following statement drops the index called PH:

    DROP INDEX PH

Any packages and cached dynamic SQL and XQuery statements that depend on the dropped indexes are marked invalid. The application program is not affected by changes resulting from adding or dropping indexes.