As tables are updated, index performance can degrade.
If you specify the MINPCTUSED option on the CREATE INDEX statement, the database server automatically merges index leaf pages if a key is deleted and the free space becomes less than the specified value. This process is called online index defragmentation.
When you use the REORG INDEXES command with the ALLOW READ/WRITE ACCESS option and without the CLEANUP ONLY option, a shadow copy of the index object is built while the original index object remains available as read or write access to the table continues. If write access is allowed, then during reorganization, any changes to the underlying table that would affect the indexes are logged. The reorg operation processes these logged changes while rebuilding the indexes.
Changes to the underlying table that would affect the indexes are also written to an internal memory buffer, if such space is available for use. The internal buffer is a designated memory area that is allocated on demand from the utility heap. The use of a memory buffer enables the index reorg utility to process the changes by reading directly from memory first, and then reading through the logs, if necessary, but at a much later time. The allocated memory is freed after the reorg operation completes.
Extra storage space is required in the index tablespace to hold the shadow copy of the index. Once the shadow copy of the index is built and all logs affecting the shadow copy have been processed, then a super-exclusive lock is taken on the table and the original index is discarded. The space that was occupied by the original copy of the index object is free to be reused by any object in the same tablespace, however it is not automatically returned to the filesystem.
Online index reorganization in ALLOW WRITE ACCESS mode (with or without the CLEANUP ONLY option) is not supported for spatial indexes or multidimensional clustering (MDC) tables.
With DB2® V9.7 Fix Pack 1 and later releases, using the REORG INDEXES ALL command on a data partitioned table and specifying a partition with the ON DATA PARTITION clause reorganizes the partitioned indexes for single data partition. During index reorganization, the unaffected partitions remain read and write accessible access is restricted only to the affected partition.
For reorganizing indexes, you can use the task assistant available in IBM® Data Studio Version 3.1 or later. Task assistants can guide you through the process of setting options, reviewing the automatically generated commands to perform the task, and running these commands. For more details, see Administering databases with task assistants.