How LOAD affects real-time statistics

When you run LOAD REPLACE on a table space or table space partition, you change the statistics associated with that table space or partition.

The table below shows how running LOAD REPLACE on a table space or table space partition affects the SYSTABLESPACESTATS statistics.

Table 1. Changed SYSTABLESPACESTATS values during LOAD REPLACE
Column name Settings for LOAD REPLACE after RELOAD phase
COPYLASTTIME Current timestamp3
COPYUPDATEDPAGES 03
COPYCHANGES 03
COPYUPDATELRSN Null3
COPYUPDATETIME Null3
DATASIZE Actual value
EXTENTS Actual value
GETPAGES 0
LOADRLASTTIME Current timestamp
NPAGES Actual value
NACTIVE Actual value
SPACE Actual value
REORGINSERTS 0
REORGDELETES 0
REORGUPDATES 0
REORGDISORGLOB 0
REORGUNCLUSTINS 0
REORGMASSDELETE 0
REORGNEARINDREF 0
REORGFARINDREF 0
STATSLASTTIME Current timestamp2
STATSINSERTS 02
STATSDELETES 02
STATSUPDATES 02
STATSMASSDELETE 02
TOTALROWS Number of loaded rows or LOBs1
Notes:
  1. Under certain conditions, such as a utility restart, the LOAD 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. Db2 sets this value only if the LOAD invocation includes the STATISTICS option.
  3. Db2 sets this value only if the LOAD invocation includes the COPYDDN option.

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

Table 2. Changed SYSINDEXSPACESTATS values during LOAD REPLACE
Column name Settings for LOAD REPLACE after BUILD phase
COPYCHANGES 03
COPYLASTTIME Current timestamp3
COPYUPDATEDPAGES 03
COPYUPDATELRSN Null3
COPYUPDATETIME Null3
EXTENTS Actual value
GETPAGES 0
NACTIVE Actual value
NLEAF Actual value
NLEVELS Actual value
SPACE Actual value
LOADRLASTTIME Current timestamp
REORGAPPENDINSERT 0
REORGDELETES 0
REORGINSERTS 0
REORGLEAFNEAR 0
REORGLEAFFAR 0
REORGMASSDELETE 0
REORGNUMLEVELS 0
REORGPSEUDODELETES 0
STATSDELETES 02
STATSINSERTS 02
STATSLASTTIME Current timestamp2
STATSMASSDELETE 02
TOTALENTRIES Number of index entries added1
Notes:
  1. Under certain conditions, such as a utility restart, the LOAD utility might not have an accurate count of loaded records. In those cases, Db2 sets this value to null.
  2. Db2 sets this value only if the LOAD invocation includes the STATISTICS option.
  3. Db2 sets this value only if the LOAD invocation includes the COPYDDN option.

For a logical index partition:

  • A LOAD operation without the REPLACE option behaves similar to a SQL INSERT operation in that the number of records loaded are counted in the incremental counters such as REORGINSERTS, REORGAPPENDINSERT, STATSINSERTS, and COPYCHANGES. A LOAD operation without the REPLACE option affects the organization of the data and can be a trigger to run REORG, RUNSTATS or COPY.
  • Db2 does not reset the nonpartitioned index when it does a LOAD REPLACE on a partition. Therefore, Db2 does not reset the statistics for the index. The REORG counters from the last REORG are still correct. Db2 updates LOADRLASTTIME when the entire nonpartitioned index is replaced.
  • When Db2 does a LOAD RESUME YES on a partition, after the BUILD phase, Db2 increments TOTALENTRIES by the number of index entries that were inserted during the BUILD phase.