How REORG affects real-time statistics

When you run the REORG utility Db2 modifies some of the real-time statistics for the involved table space or index.

The table below shows how running REORG on a table space or table space partition affects the SYSIBM.SYSTABLESPACESTATS catalog table statistics.

Table 1. Changed SYSTABLESPACESTATS values during REORG
Column name Settings for REORG SHRLEVEL NONE after RELOAD phase Settings for REORG SHRLEVEL REFERENCE or CHANGE after SWITCH phase
TOTALROWS Number of rows loaded1 For SHRLEVEL REFERENCE: Number of loaded rows or LOBs during RELOAD phase

For SHRLEVEL CHANGE: Number of loaded rows or LOBs during RELOAD phase plus number of rows inserted during LOG APPLY phase minus number of rows deleted during LOG phase

DATASIZE Actual value Actual value
NPAGES Actual value Actual value
NACTIVE Actual value Actual value
SPACE Actual value Actual value
EXTENTS Actual value Actual value
REORGLASTTIME Current timestamp Current timestamp
REORGINSERTS 0 Actual value3
REORGDELETES 0 Actual value3
REORGUPDATES 0 Actual value3
REORGDISORGLOB 0 Actual value3
REORGUNCLUSTINS 0 Actual value3
REORGMASSDELETE 0 Actual value3
REORGNEARINDREF 0 Actual value3
REORGFARINDREF 0 Actual value3
STATSLASTTIME Current timestamp4 Current timestamp4
STATSINSERTS 04 Actual value3
STATSDELETES 04 Actual value3
STATSUPDATES 04 Actual value3
STATSMASSDELETE 04 Actual value3
COPYLASTTIME Current timestamp5 Current timestamp
COPYUPDATEDPAGES 05 Actual value6
COPYCHANGES 05 Actual value6
COPYUPDATELRSN Null5 Actual value6
COPYUPDATETIME Null5 Actual value6

The table below shows how running REORG affects the SYSINDEXSPACESTATS statistics for an index space or physical index partition.

Table 2. Changed SYSINDEXSPACESTATS values during REORG
Column name Settings for REORG SHRLEVEL NONE after RELOAD phase Settings for REORG SHRLEVEL REFERENCE or CHANGE after SWITCH phase
TOTALENTRIES Number of index entries added2 For SHRLEVEL REFERENCE: Number of added index entries during BUILD phase

For SHRLEVEL CHANGE: Number of added index entries during BUILD phase plus number of added index entries during LOG phase minus number of deleted index entries during LOG phase

NLEAF Actual value Actual value
NLEVELS Actual value Actual value
NACTIVE Actual value Actual value
SPACE Actual value Actual value
EXTENTS Actual value Actual value
REORGLASTTIME Current timestamp Current timestamp
REORGINSERTS 0 Actual value3
REORGDELETES 0 Actual value3
REORGAPPENDINSERT 0 Actual value3
REORGPSEUDODELETES 0 Actual value3
REORGMASSDELETE 0 Actual value3
REORGLEAFNEAR 0 Actual value3
REORGLEAFFAR 0 Actual value3
REORGNUMLEVELS 0 Actual value3
STATSLASTTIME Current timestamp4 Current timestamp4
STATSINSERTS 04 Actual value3
STATSDELETES 04 Actual value3
STATSMASSDELETE 04 Actual value3
COPYLASTTIME Current timestamp5 Unchanged7
COPYUPDATEDPAGES 05 Unchanged7
COPYCHANGES 05 Unchanged7
COPYUPDATELRSN Null5 Unchanged7
COPYUPDATETIME Null5 Unchanged7

For a logical index partition, Db2 does not reset the nonpartitioned index when it does a REORG on a partition. Therefore, Db2 does not reset the statistics for the index. The REORG counters and REORGLASTTIME are relative to the last time the entire nonpartitioned index is reorganized. In addition, the REORG counters might be low because, due to the methodology, some index entries are changed during REORG of a partition.

Notes for the preceding tables

  1. Under certain conditions, such as a utility restart, the REORG utility might not have an accurate count of loaded records. In those cases, Db2 sets this value to null. Some rows that are loaded into a table space and are included in this value might later be removed during the index validation phase or the referential integrity check. Db2 includes counts of those removed records in the statistics that record deleted records.
  2. Under certain conditions, such as a utility restart, the REORG utility might not have an accurate count of loaded records. In those cases, Db2 sets this value to null.
  3. This is the actual number of inserts, updates, or deletes that are due to applying the log to the shadow copy.
  4. Db2 sets this value only if the REORG invocation includes the STATISTICS option.
  5. Db2 sets this value only if the REORG invocation includes the COPYDDN option.
  6. This is the LRSN or timestamp for the first update that is due to applying the log to the shadow copy.
  7. Inline COPY is not supported for SHRLEVEL CHANGE or SHRLEVEL REFERENCE.