You can use data in the SYSIBM.SYSINDEXSPACESTATS table to determine when to reorganize
an index.
Before you begin
For best results, consider calling the DSNACCOX stored procedure to determine whether to reorganize database objects. For more information, see DSNACCOX stored procedure.
Procedure
To investigate whether to reorganize indexes:
-
Reorganize the indexes if any of the following conditions are true in the
SYSIBM.SYSINDEXSPACESTATS catalog table:
- REORGPSEUDODELETES/FLOAT(TOTALENTRIES) > 10% in a non-data sharing
environment, or REORGPSEUDODELETES/TOTALENTRIES > 5% in a data sharing environment.
- REORGINSERTS/FLOAT(TOTALENTRIES) > 25%
- REORGDELETES/FLOAT(TOTALENTRIES) > 25%
- REORGAPPENDINSERT/FLOAT(TOTALENTRIES) > 20%
- EXTENTS > 254
-
Reorganize the indexes if any of the following conditions are true:
- Advisory REORG-pending state (AREO*) as a result of an ALTER statement.
- Advisory REBUILD-pending state (ARBDP) as a result an ALTER statement.