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.
| 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.
| 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
- 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.
- 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.
- This is the actual number of inserts, updates, or deletes that are due to applying the log to the shadow copy.
- Db2 sets this value only if the REORG invocation includes the STATISTICS option.
- Db2 sets this value only if the REORG invocation includes the COPYDDN option.
- This is the LRSN or timestamp for the first update that is due to applying the log to the shadow copy.
- Inline COPY is not supported for SHRLEVEL CHANGE or SHRLEVEL REFERENCE.