DB2 10.5 for Linux, UNIX, and Windows

Index cleanup and maintenance

After you create an index, performance might degrade with time unless you keep the index compact and organized.

The following recommendations help you keep indexes as small and efficient as possible:

Enable online index defragmentation.
Create indexes with the MINPCTUSED clause. Drop and recreate existing indexes, if necessary.
Perform frequent commits, or acquire table-level X locks, either explicitly or through lock escalation, if frequent commits are not possible.
Index keys that are marked deleted can be physically removed from the table after a commit. X locks on tables enable the deleted keys to be physically removed when they are marked deleted.
Use the REORGCHK command to help determine when to reorganize indexes or tables, and when to use the REORG INDEXES command with the CLEANUP parameter.
To allow read and write access to the index during reorganization, use the REORG INDEXES command with the ALLOW WRITE ACCESS option.

With DB2® Version 9.7 Fix Pack 1 and later releases, issue the REORG INDEXES command with the ON DATA PARTITION parameter on a data partitioned table to reorganize the partitioned indexes of the specified partition. During index reorganization, the unaffected partitions remain read and write accessible access is restricted only to the affected partition.
If the objective is to reclaim space, use the REORG INDEXES command with the CLEANUP and RECLAIM EXTENTS parameters. The CLEANUP parameter maximizes the amount of reclaimable space.
The RECLAIMABLE_SPACE output of the ADMIN_GET_INDEX_INFO function shows how much space is reclaimable, in kilobytes.

Index keys that are marked deleted are cleaned up:

During subsequent insert, update, or delete activity
During key insertion, keys that are marked deleted and that are known to have been committed are cleaned up if that might avoid the need to perform a page split and prevent the index from increasing in size.

During key deletion, when all keys on a page have been marked deleted, an attempt is made to find another index page where all the keys are marked deleted and all those deletions have committed. If such a page is found, it is deleted from the index tree. If there is an X lock on the table when a key is deleted, the key is physically deleted instead of just being marked deleted. During physical deletion, any deleted keys on the same page are also removed if they are marked deleted and known to be committed.
When you execute the REORG INDEXES command with the CLEANUP parameter:
The CLEANUP PAGES option searches for and frees index pages on which all keys are marked deleted and known to be committed.

The CLEANUP ALL option frees not only index pages on which all keys are marked deleted and known to be committed, but it also removes record identifiers (RIDs) that are marked deleted and known to be committed from pages that contain some non-deleted RIDs. This option also tries to merge adjacent leaf pages if doing so results in a merged leaf page that has at least PCTFREE free space. The PCTFREE value is defined when an index is created. The default PCTFREE value is 10 percent. If two pages can be merged, one of the pages is freed.

For data partitioned tables, it is recommended that you invoke the RUNSTATS command after an asynchronous index cleanup has completed. To determine whether there are detached data partitions in the table, query the STATUS field in the SYSCAT.DATAPARTITIONS catalog view and look for the value 'L' (logically detached), 'D' (detached partition having detach dependent tables such as a materialized query tables) or 'I' (index cleanup).
When an index is rebuilt (or, in the case of data partitioned indexes, when an index partition is rebuilt)
Utilities that rebuild indexes include the following:
- REORG INDEXES with the default REBUILD parameter
- REORG TABLE without the INPLACE or RECLAIM EXTENTS parameter
- IMPORT with the REPLACE parameter
- LOAD with the INDEXING MODE REBUILD parameter