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.
Such 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.
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:
- Specify the RESET ACCESSPATH option.
- 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.
RUNSTATS TABLESPACE db-name.ts-name RESET ACCESSPATH
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.
- 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.