Controlling the collection of statistics feedback

Db2 collects data about missing or conflicting statistics to certain catalog and EXPLAIN tables, for use by SQL optimization tools.

About this task

When Db2 selects the access path for a SQL statement, it identifies missing or conflicting statistics values that might, if collected, improve the chances for selection of an efficient access path. Db2 holds this statistics feedback data in memory until an interval that you specify, and then collects the data in the SYSIBM.SYSSTATFEEDBACK catalog table. When you use certain methods to capture access path information for SQL statements into EXPLAIN tables, Db2 collects similar information in the DSN_STAT_FEEDBACK table, if it exists.

The statistics feedback data is collected only for EXPLAIN operations that use the access path selection process. For example, no statistics data is collected if you issue EXPLAIN statements with the PACKAGE or STMTCACHE options. In those cases, Db2 extracts previously selected access path information instead of using the access path selection process.

Certain SQL optimization tools can use this data to help you improve access path selection for SQL statements.

When the RUNSTATS utility collects the recommended statistics, it removes the recommendation rows from the SYSIBM.SYSSTATFEEDBACK catalog table.

Tip: Enhanced query tuning capabilities that can help you with this task are available in IBM® Db2 Query Workload Tuner for z/OS® and IBM Db2 Administration Foundation for z/OS.

To control the collection of statistics feedback data, use the following approaches:

• Set the value of the STATFDBK_SCOPE subsystem parameter.
  This value controls whether Db2 collects the statistics feedback at all, whether Db2 collects statistics feedback information for static SQL statements only, for dynamic SQL statements only, or for both types of statements.
• Set the value of the STATSINT subsystem parameter.
  Db2 externalizes statistics feedback data to the SYSIBM.SYSSTATFEEDBACK catalog table, for each SQL statement that was bound or prepared during the preceding interval.

  Important: This value also separately controls the interval at which Db2 externalizes in-memory statistics to the real-time statistics tables.

  You can issue the following command to immediately externalize any statistics feedback recommendations that are held in memory to the catalog:

  ACCESS DB(*) SP(*) MODE(STATS)

• Set the value of the STATS_FEEDBACK column of the SYSIBM.SYSTABLES catalog table.
  When the value of this column is 'N', Db2 does not collect statistics feedback data for the object.
• Set the value of the BLOCK_RUNSTATS column of the SYSIBM.SYSSTATFEEDBACK catalog table.
  Optimization tools can use this column to determine whether to actually collect a specific statistic recommended by Db2. For information about whether and how this column is used, see the information for the tool that you use.

What to do next

You can also apply statistics recommendations to statistics profiles automatically. For details, see Applying statistics recommendations to statistics profiles automatically