DB2 Server for VSE & VM: Database Services Utility

REORGANIZE INDEX

The REORGANIZE INDEX command allows you to correct index fragmentation, and correct the skewing of index key values without having to drop the index and then recreate it using the DROP INDEX and CREATE INDEX SQL statements. REORGANIZE INDEX also revalidates an invalid index.

As with other Database Services Utility commands, you can use the REORGANIZE INDEX command with both single user mode and multiple user mode.

Note:

The REORGANIZE INDEX command is not supported if you are using DRDA flow.

REORGANIZE INDEX Format

>>-REORGANIZE INDEX----(index_name)---+----------------------+-><
                                      '-PCTFREE =--integer---'
 

INDEX (index-name)

identifies the index to be reorganized. You can further identify the index by specifying the owner and server-name of the index. For more information about identifying the index, see Qualifying Object Names for details. When reorganizing an invalid index, the database manager uses a sort similar to the one used for a CREATE INDEX statement.

PCTFREE = integer

allows you to control the amount of free space that REORGANIZE reserves in the index for later insertions and updates.

integer

is a number from 0 to 99 representing a percentage of the total index space. For practical purposes, it should not exceed 50.

If you do not specify PCTFREE, the amount of free space remains unchanged from the previous PCTFREE value.

The REORGANIZE INDEX command consists of several separate internal steps. Some steps might be completed even though the whole REORGANIZE INDEX command is not completed successfully.

The following unusual situations can occur when rolling back or recovering from a REORGANIZE INDEX command:

• Although a REORGANIZE INDEX command is successfully completed during forward processing, it might not be completed during rollback or recovery because the system has run out of physical or logical pages (or both). Checkpoints for sufficient storage can interrupt only during forward processing. As a result, the recovered index is marked invalid.
• If an index reorganization is rolled back or undone, the recovered index is nevertheless reorganized. In REORGANIZE INDEX processing, the index is effectively dropped and re-created, resulting in a reorganized index. If you changed the PCTFREE value, however, that value is set back to the original value defined before the REORGANIZE INDEX command.
• If the system ends abnormally after an index reorganization is interrupted by a checkpoint and an attempt is made to restart the database manager without the current log (because of a loss of the log or log reconfiguration), the reorganized index will be marked as invalid. It must be recovered by dropping the dbspace and re-creating it, or by restoring a previous database archive.
• The updating of index statistics is not supported during rollback or recovery. The previous index statistics are recovered, but the recovered index (which is nevertheless reorganized) may not match them.

DBSS prevents a REORGANIZE INDEX or CREATE INDEX command from proceeding if the command can overflow the invalid index limit. During rollback or recovery, if the maximum number of invalid indexes (30) is reached, the system ends abnormally. When the system is brought up again for recovery, it is very likely to end again for the same reason. If the large number of invalid indexes exists because of a lack of physical pages, add a dbextent to the system before attempting to recover again. If the large number of invalid indexes exists because of a lack of logical index pages, use filtered log recovery to skip over the index reorganizations that are causing the invalid indexes. For information about adding a dbextent, see the DB2 Server for VM System Administration |and DB2 Server for VSE System |Administration manuals. To find out more about using filtered log recovery, see the DB2 Server for VSE & VM Diagnosis Guide and Reference manual. Additional information on the REORGANIZE INDEX command is in the DB2 Server for VSE & VM Database Administration manual.

|You cannot reorganize a catalog index by using the REORGANIZE INDEX |command. To reorganize the catalog index in VM, use the SQLCIREO |utility. In VSE, set the STARTUP initialization parameter to I to |reorganize the catalog index.

Note:

You can only reorganize a primary key index or a unique index by using the ALTER TABLE ACTIVATE PRIMARY KEY and ALTER TABLE ACTIVATE UNIQUE statements.