Deciding when to reorganize table spaces

You can used values in the SYSIBM.SYSTABLESPACESTATS catalog table determine when to reorganize a table space.

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

  • Reorganize table spaces when any of the following conditions are true.
    These conditions are based on values in the SYSIBM.SYSTABLESPACESTATS table.
    • REORGUNCLUSTINS/FLOAT(TOTALROWS) > 10%

      Do not use REORGUNCLUSTINS to determine if you should run REORG if access to the table space is predominantly random access.

    • (REORGNEARINDREF+REORGFARINDREF)/FLOAT(TOTALROWS) > 5% in a data sharing environment, or (REORGNEARINDREF+REORGFARINDREF)/FLOAT(TOTALROWS) >10% in non-data sharing environment
    • REORGINSERTS/FLOAT(TOTALROWS) > 25%
    • REORGDELETES/FLOAT(TOTALROWS) > 25%
    • EXTENTS > 254
    • REORGDISORGLOB/FLOAT(TOTALROWS) > 50%
    • SPACE > 2 * (DATASIZE / 1024)
    • REORGMASSDELETE > 0
    • REORGCLUSTSENS > 0
  • Reorganize table spaces if any of the following conditions are true:
    • The table space is in the advisory REORG-pending state (AREO*) as a result of an ALTER TABLE statement.
    • An index on a table in the table space is in the advisory REBUILD-pending state (ARBDP) as result an ALTER TABLE statement.