Statistics profiles

Start of changeA statistics profile is a saved set of options for the RUNSTATS utility or inline statistics from the LOAD or REORG TABLESPACE utilities. Each profile applies for a particular table. Db2 uses statistics profiles for autonomic statistics maintenance. You can also use statistics profiles to quickly run the RUNSTATS utility or inline statistics in other utilities with a predefined set of options.End of change

Start of changeIf the value of the STATFDBK_PROFILE subsystem parameter is YES, Db2 automatically modifies and creates statistics profiles when it writes statistics recommendations to the SYSIBM.SYSTATFEEDBACK catalog table.End of change

You can specify a complete set of RUNSTATS options in a profile, or specify only a few options, or even only a single option. The options that you specify are stored in the PROFILE_TEXT column of the SYSIBM.SYSTABLES_PROFILES catalog table. If you do not specify values for the following options when you create the profile, Db2 uses default values, as in any RUNSTATS invocation:

  • COLUMN
  • COLGROUP
  • FREQVAL
  • COUNT
  • MOST
  • BOTH
  • LEAST
  • INDEX
  • KEYCARD
  • NUMCOLS
  • COUNT
  • HISTOGRAM
  • NUMQUANTILES

Each statistics profile is saved as a single row in the SYSIBM.SYSTABLES_PROFILES catalog table. After your create a profile for a table, you can specify that Db2 uses the same options that were specified in the table when you collect statistics again later. When you automate statistics maintenance, Db2 creates or updates the single profile for each table that is not excluded from autonomic maintenance. Because only a single statistics profile can exist for each table, Db2 uses any options that you have specified in existing profiles for a particular table when the ADMIN_UTL_MONITOR stored procedure first executes for autonomic statistics monitoring.

Regardless of whether profiles exist, or whether autonomic statistics maintenance is enabled, you can always use utilities to collect statistics and specify customized options without using a profile.

When you specify the use of statistics profiles and no profile exists for a target table, a default profile is used:
  • When a table name is not specified, TABLE ALL INDEX ALL is used for the profile specification.
  • When a table name is specified, COLUMN ALL INDEX ALL is used for the profile specification.