Resetting access path statistics

You can use the RUNSTATS utility to remove old and out-of-date access path statistics for Db2 objects.

About this task

When the RUNSTATS utility is invoked over a period of time, statistics are collected incrementally for target objects. The combination of many changes to target objects and many RUNSTATS invocations, perhaps with different options, might result in some previously collected statistics becoming outdated. Such out-of-date statistics might cause Db2 to choose inefficient access paths for SQL statements. One solution is to invoke the RUNSTATS utility again to refresh the statistics. However, the task of formulating RUNSTATS invocations to solve the problem might prove difficult because of the complicated nature of the many previous RUNSTATS invocations.

Start of changeSuch stale statistics can also increase your statistics collection costs, especially after migration to Db2 12, if their collection becomes standardized in a statistics profile that is based on existing statistics. For more information see Cleaning stale or unneeded Db2 catalog statistics.End of change

When the situation occur, you can invoke the RUNSTATS utility to reset the access path statistics for all tables and indexes in a specified table space. When you reset the statistics, the default values are used. No statistics are gathered or reported. Space statistics and real-time statistics are not reset for the specified objects. After your reset access path statistics, the previous values cannot be recovered if no statistics history is available.

Procedure

To reset access path statistics:

Invoke the RUNSTATS utility, and specify the following options:
  1. Specify the RESET ACCESSPATH option.
  2. Optional: Specify the HISTORY ACCESSPATH option to record that the access path statistics were reset in rows in the SYSIBM.SYSTABLES_HIST and SYSIBM.SYSINDEXES_HIST statistics tables.
    This option only records that the reset occurred and does not save the access path statistics values that are reset.
For example, you might issue the following utility control statement:
RUNSTATS TABLESPACE db-name.ts-name RESET ACCESSPATH
Statistics are not collected. Instead, the RUNSTATS utility resets the access path statistics.

Results

Certain catalog table rows are updated with default values, and rows are deleted from other catalog tables. All updated rows in the catalog tables contain the same timestamp value. Real-time statistics and space for the specified object are not reset. However, the dynamic statement cache is invalidated.

The following statistics are reset to the specified values:
SYSIBM.SYSTABLESPACE
The following values are changed:
Column Changed value
NACTIVE -1
NACTIVEF -1
STATSTIME The TIMESTAMP value for the reset operation
SYSIBM.SYSCOLUMNS
The following values are changed:
Column Changed value
COLCARD -1
COLCARDF -1
HIGH2KEY Zero-length blank
LOW2KEY Zero-length blank
STATSTIME The TIMESTAMP value for the reset operation
STATS_FORMAT Blank
SYSIBM.SYSTABLES
The following values are changed:
Column Changed value
CARD -1
CARDF -1
NPAGES -1
NPAGESF -1
PCTPAGES -1
PCTROWCOMP -1
STATSTIME The TIMESTAMP value for the reset operation
SYSIBM.SYSINDEXES
The following values are changed:
Column Changed value
CLUSTERED 'N'
NLEAF -1
NLEVELS -1
FIRSTKEYCARD -1
FULLKEYCARD -1
FIRSTKEYCARDF -1
FULLKEYCARDF -1
CLUSTERRATIO 0
CLSUTERRATIOF 0
DATAREPEATFACTORF -1
STATSTIME The TIMESTAMP value for the reset operation
SYSIBM.SYSKEYTARGETS
The following values are changed:
Column Changed value
CARDF -1
HIGH2KEY Zero-length blank
LOW2KEY Zero-length blank
STATSTIME TIMESTAMP
STATS_FORMAT Blank

Applicable rows are deleted from the following catalog tables for the specified objects:

  • SYSIBM.SYSTABSTATS
  • SYSIBM.SYSCOLSTATS
  • SYSIBM.SYSINDEXSTATS
  • SYSIBM.SYSCOLDIST
  • SYSIBM.SYSCOLDISTSTATS
  • SYSIBM.SYSKEYTARGETSTATS
  • SYSIBM.SYSKEYTGTDIST
  • SYSIBM.SYSKEYTGTDISTSTATS

What to do next

After resetting the access path statistics objects, collect your standard statistics for those objects. For more information, see Collecting statistics by using Db2 utilities.