Autonomic statistics overview

The following diagram illustrates the relationships between the various objects that Db2 uses for autonomic statistics maintenance. Db2 uses the following actions for autonomic statistics maintenance:

1. The administrative task scheduler issues calls to the ADMIN_UTL_MONITOR stored procedure according to the schedule that you specify.
2. When the ADMIN_UTL_MONITOR detects missing, out-of-date, or conflicting statistics, it issues a call to the ADMIN_TASK_ADD stored procedure to schedule an immediate execution of the ADMIN_UTL_EXECUTE stored procedure.
3. The administrative scheduler calls the ADMIN_UTL_EXECUTE stored procedure.
4. When the call to ADMIN_UTL_EXECUTE stored procedure occurs within a time window that you specify, it invokes the RUNSTATS utility to solve alerts.
5. When the call to the ADMIN_UTL_EXECUTE stored procedure occurs outside of a specified time window, the ADMIN_UTL_EXECUTE stored procedure issues a call to the ADMIN_TASK_ADD stored procedure to reschedule its own execution to the next time window.

The administrative scheduler and the following stored procedures and tables have the following roles when you enable autonomic statistics in Db2:

Administrative scheduler

Calls each of the stored procedures:

• Calls ADMIN_UTL_MONITOR stored procedure as scheduled for statistics monitoring.
• Calls the ADMIN_UTL_EXECUTE stored procedure to solve alerts according to tasks added by the ADMIN_UTL_MONITOR and ADMIN_UTL_EXECUTE stored procedures.
• Calls the ADMIN_UTL MODIFY stored procedure as scheduled to remove old alert history information from the SYSIBM.SYSAUTOALERTS catalog table.

ADMIN_UTL_MONITOR stored procedure

Monitors statistics in the catalog and identifies stale, missing, or conflicting statistics through the following interactions:

• Reading catalog tables to assess existing statistics, and generate alerts as necessary
• Reading the RUNSTATS profiles to determine the set of columns, column groups, and indexes to check.
• When alerts are found, adding tasks for the ADMIN_UTL_EXECUTE stored procedure to the administrative scheduler.
• Writing statistics alerts.

ADMIN_UTL_EXECUTE stored procedure

Invokes the RUNSTATS utility to fix stale, missing or conflicting statistics that are identified by the ADMIN_UTL_MONITOR stored procedure through the following interactions:

• Reading alerts and updating alerts when they are solved.
• Reading the time windows to determine when to solve alerts.
• Adding tasks for its own execution at the next time window
• During time windows, invoking the RUNSTATS utility to collect statistics.

SYSIBM.SYSTABLES_PROFILES catalog table

Contains RUNSTATS profiles that are used to specify the options for the invocation of the RUNSTATS utility by the ADMIN_UTL_EXECUTE stored procedure. Each row creates a single profile for a different table.

SYSIBM.SYSAUTOTIMEWINDOWS catalog table

Contains information that describes time windows during which the ADMIN_UTL_EXECUTE stored procedure is allowed to invoke the RUNSTATS utility to resolve alerts.

SYSIBM.SYSAUTOALERTS catalog table

Contains information about statistics alerts that are identified by the ADMIN_UTL_MONITOR stored procedure.

SYSIBM.SYSAUTORUNS_HIST catalog table

Contains historical information about the actions of the ADMIN_UTL_MONITOR, ADMIN_UTL_EXECUTE, and ADMIN_UTL_MODIFY stored procedures.

ADMIN_UTL_MODIFY stored procedure

Reads the SYSIBM.SYSAUTORUNS_HIST and SYSIBM.SYSAUTOALERTS catalog table and deletes old records in that table based on the options that are specified when the call is scheduled in the administrative task scheduler.