Determining when to reorganize indexes

You can use data in the SYSIBM.SYSINDEXSPACESTATS table to determine when to reorganize an index.

Before you begin

Start of changeFor best results, consider calling the DSNACCOX stored procedure to determine whether to reorganize database objects.End of change

Procedure

To investigate whether to reorganize indexes:

  • Reorganize the indexes if any of the following conditions are true in the SYSIBM.SYSINDEXSPACESTATS catalog table:
    • Start of changeREORGPSEUDODELETES/FLOAT(TOTALENTRIES) > 10% in a non-data sharing environment, or REORGPSEUDODELETES/TOTALENTRIES > 5% in a data sharing environment. End of change
    • Start of changeREORGINSERTS/FLOAT(TOTALENTRIES) > 25%End of change
    • Start of changeREORGDELETES/FLOAT(TOTALENTRIES) > 25%End of change
    • Start of changeREORGAPPENDINSERT/FLOAT(TOTALENTRIES) > 20%End of change
    • 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.