Dropping and redefining a Db2 index

Dropping an index does not cause Db2 to drop any other objects. The consequence of dropping indexes is that Db2 invalidates packages that use the index and automatically rebinds them when they are next used.

Before you begin

Any primary key, unique key, or referential constraints associated with a unique index must be dropped before you drop the unique index. However, you can drop a unique index for a unique key without dropping the unique constraint if the unique key was created before Version 9.

Commit the drop before you create any new table spaces or indexes by the same name.

Procedure

Begin general-use programming interface information.To drop and re-create an index:

  1. Issue a DROP INDEX statement.
  2. Commit the drop procedure.
    The index space associated with the index is also dropped.
  3. Re-create the modified index by issuing a CREATE INDEX statement.
  4. Rebind any application programs that use the dropped index.End general-use programming interface information.

    If you drop and index and then run an application program using that index (and thereby automatically rebound), that application program does not use the old index. If, at a later time, you re-create the index and the application program is not rebound, the application program cannot take advantage of the new index.