Monitoring catalog statistics

You can improve access path selection for SQL statements by monitoring the health of statistics for database objects. The health of these statistics is of critical importance for efficient access paths for SQL statements.

About this task

Begin program-specific programming interface information. Db2 uses a cost-based optimizer, which makes decisions based on the statistics that are available for the tables and indexes. When accurate statistics are not available for database objects, the cost estimates that Db2 relies on for choosing the most efficient access path become incorrect.

Procedure

To monitor the health of statistics for database objects, use one or more of the following approaches:

  • Call the DSNACCOX stored procedure to discover whether to invoke the REORG or RUNSTATS utilities for database objects.
    The DSNACCOX stored procedures uses real-time statistics values to recommend activities for maintaining data organization and statistics for database objects.
  • Use optimization feedback data in the following tables to check for missing and conflicting data statistics:
    • DSN_STAT_FEEDBACK table.
    • SYSIBM.SYSSTATFEEDBACK catalog table.
  • Specify the REPORT YES option when you invoke the RUNSTATS utility.
  • Issue the SELECT statement to query the catalog statistics.
    You can find a set of sample SELECT statements for querying statistics information from the catalog in member DSNTESP of the SDSNSAMP data set.

What to do next

If the statistics in the Db2 catalog no longer correspond to the organization and content of your data (or the DSNACCOX stored procedure recommends), take the following actions:
  1. Run the REORG utility to reorganize the data.
  2. Run the RUNSTATS utility to collect accurate statistics for database objects.
  3. Rebind the applications that use static SQL statements so that Db2 can choose the most efficient access paths.
End program-specific programming interface information.