How to use SYSSTATFEEDBACK to drive your RUNSTATS

DB2 v11 introduced a new catalog table SYSIBM.SYSSTATFEEDBACK, which contains information about missing or conflicting catalog statistics for SQL statements [more info].

Some users are very cautious about collection of statistics, some others would rather prefer this process to be automatic.

If you are part of the second group, this new feature is for you! However, the process is not fully automatic: DB2 will only fill the SYSSTATFEEDBACK catalog table with suggestions, when he/she thinks that executing RUNSTATS may be beneficial. During the last IDUG, when asked if there is any plan to “close the loop”, IBMers answered that there is no such plan. In other words, we should not expect this process to be automated by DB2 itself.

A related note in the DB2 11 for z/OS Technical Overview

Tooling available

In Version 18.0, CA Database Analyzer™ for DB2 for z/OS has been enhanced with additional support for DB2 11 Optimizer recommended RUNSTATS statistics. This tool now provides by default an extract object condition (EOC), that evaluates the SYSSTATFEEDBACK table and currently selects all objects (at the partition level) with a REASON value of BASIC, KEYCARD, or CONFLICT. The sample Real Time Object Selection RTOS$S$C is provided for this purpose:

The sample RTOS$S$C is provided,

under creator INSTALL.

The sample RTOS$S$C extracts objects 

from the SYSSTATFEEDBACK table.

Create an Action Procedure (to execute RUNSTATS) 

tied to RTOS$S$C

CA Database Analyzer generated the RUNSTATS commands 

based on SYSSTATFEEDBACK recommendations.

A couple of useful notes

• The column STATS_FEEDBACK (in SYSIBM.SYSTABLES catalog table) controls whether the data is collected for a particular table (so you can exclude some specific tables from this process).
• When the recommended statistics are collected, the RUSNTATS utility removes data from the SYSIBM.SYSSTATFEEDBACK catalog table.