Automating statistics maintenance

You can reduce the amount of labor-intensive work for identifying, collecting, and maintaining accurate statistics in Db2. Doing so might also improve performance by increasing the likelihood that accurate statistics are available when Db2 needs them.

Before you begin

Begin program-specific programming interface information.
  • Configure the following stored procedures (use job DSNTIJRT, as described in Installing Db2-supplied routines during installation)
    • ADMIN_COMMAND_DB2
    • ADMIN_INFO_SSID
    • ADMIN_TASK_ADD
    • ADMIN_TASK_UPDATE
    • ADMIN_UTL_EXECUTE
    • ADMIN_UTL_MODIFY
    • ADMIN_UTL_MONITOR
    • ADMIN_UTL_SCHEDULE
    • ADMIN_UTL_SORT
    • DSNUTILU
    • DSNWZP
  • Configure the following user-defined functions (use job DSNTIJRT):
    • ADMIN_TASK_LIST()
    • ADMIN_TASK_STATUS()
  • Ensure that your authorization ID has the following privileges:
    • CALL for the preceding stored procedures and user-defined functions.
    • Read and modify data in the following catalog tables:
      • SYSIBM.SYSAUTOALERTS
      • SYSIBM.SYSAUTORUNS_HIST
      • SYSIBM.SYSAUTOTIMEWINDOWS
      • SYSIBM.SYSTABLES_PROFILES
    • Read data in the following catalog tables:
      • SYSIBM.SYSTABLESPACESTATS
      • SYSIBM.SYSTABLESPACE
      • SYSIBM.SYSDATABASE
      • SYSIBM.SYSTABLES
      • SYSIBM.SYSINDEXES
      • SYSIBM.SYSKEYS
      • SYSIBM.SYSCOLUMNS
      • SYSIBM.SYSCOLDIST
      • SYSIBM.SYSDUMMY1
      • SYSIBM.UTILITY_OBJECTS

About this task

Whether you configure autonomic monitoring directly within Db2, or use an administrative tool outside of Db2, the high-level steps for configuring autonomic monitoring are similar.

After you configure autonomic monitoring, Db2 relies on scheduled calls to the ADMIN_UTL_MONITOR stored procedure to monitor your statistics. When stale, missing, or conflicting, are identified, the ADMIN_UTL_EXECUTE stored procedure invokes RUNSTATS within defined maintenance windows and resolves the problems. The ADMIN_UTL_EXECUTE stored procedure uses the options that are defined in statistics profiles to invoke the RUNSTATS utility. The ADMIN_UTL_MODIFY stored procedure is called at regular intervals to clean up the log file and alert history.

Procedure

To configure autonomic monitoring:

  1. Schedule time windows for autonomic statistics collection.
    The time windows are defined in the SYSIBM.SYSAUTOTIMEWINDOWS catalog table.
  2. Schedule monitoring activities and define the level of detail, thresholds, objects to exclude from monitoring, and other options.
    The monitoring activities are scheduled through one or more tasks defined in the administrative task scheduler. Each task calls the ADMIN_UTL_MONITOR stored procedure and can define a different level of detail, different thresholds, and different objects to exclude from monitoring.
  3. Schedule maintenance of the log and alert history for autonomic statistics.
    The monitoring activities are scheduled through a single task that is defined in the administrative task scheduler that calls the ADMIN_UTL_MODIFY stored procedure.End program-specific programming interface information.
Start of change

What to do next

Consider setting the value of the STATFDBK_PROFILE subsystem parameter to YES. When you specify that value, Db2 automatically modifies and creates statistics profiles when it writes statistics recommendations to the SYSIBM.SYSSTATFEEDBACK catalog table. For more information about this approach, see Applying statistics recommendations to statistics profiles automatically.

End of change