Statistics profile syntax

The options of a statistics profile are stored within the PROFILE_TEXT column of the SYSTABLES_PROFILES catalog table.

You can specify the following statistics collection options in a statistics profile:
  • COLUMN
  • COLGROUP
  • FREQVAL
  • COUNT (MOST, LEAST or BOTH)
  • INDEX
  • KEYCARD
  • NUMCOLS
  • HISTOGRAM
  • NUMQUANTILES

The profile contains the default values for any options that are not specified.

When you update an existing profile that contains a partitioned index, the PART keyword must be specified on all index specifications for that index, or omitted from the index specification for that index. Statistics profile processing enforces this requirement. Any profile modifications done through SQL statements must follow the same restriction, or error messages result when the profile is used.

For a given partitioned index:

  • Any new index specifications without the PART keyword replace all index specifications in the profile regardless of the PART keyword specification.
  • Any new index specification with the PART keyword replaces only the existing index specification with the same PART specified, or a specification without the PART keyword.

The PROFILE functions cannot be executed when there are syntax errors in the statistics profile. Syntax errors can be corrected using RUNSTATS UPDATE PROFILE or SQL UPDATE, or by deleting the profile with RUNSTATS DELETE PROFILE or SQL DELETE.

The following diagrams show the options that you can specify for statistics profiles. For context, see RUNSTATS TABLESPACE syntax and options and RUNSTATS INDEX syntax and options.

column-spec
Read syntax diagramSkip visual syntax diagramCOLUMN(ALL)COLUMN(,column-name1)SORTNUMinteger
colgroup-spec
Read syntax diagramSkip visual syntax diagramCOLGROUP(,column-name)colgroup-stats-spec
index-spec
Read syntax diagramSkip visual syntax diagramINDEX2(ALL)correlation-stats-specINDEX(*)3correlation-stats-specINDEX(,index-namePARTintegercorrelation-stats-spec)
colgroup-stats-spec
Read syntax diagramSkip visual syntax diagramFREQVAL COUNTinteger4MOSTBOTHLEASTHISTOGRAMNUMQUANTILES100NUMQUANTILESinteger
correlation-stats-spec
Read syntax diagramSkip visual syntax diagramKEYCARD5FREQVAL NUMCOLS1COUNT10MOSTFREQVAL NUMCOLSintegerCOUNTinteger4MOSTBOTHLEASTHISTOGRAMNUMCOLS1NUMQUANTILES100NUMCOLSinteger6NUMQUANTILES100NUMQUANTILESinteger
Notes:
  • 1 The same column name must not be specified more than once. If all columns are listed in the COLUMN option, RUNSTAT will treat it as is, and does not replace it with the COLUMN(ALL) option. Users have to state explicitly if they want the COLUMN(ALL) option.
  • 2 You cannot specify INDEX if either USE PROFILE or DELETE PROFILE option is also specified.
  • 3 INDEX(*) is an internal representation of INDEX(ALL) that Db2 uses only in the context of RUNSTATS profiles, and is not valid when specified in any RUNSTATS control statement. When you specify the INDEX(ALL) option in a RUNSTATS control statement that creates a profile, Db2 uses INDEX(*) in the PROFILE_TEXT column of the SYSIBM.SYSTABLES_PROFILES catalog table. However, you must specify INDEX(*) instead of INDEX(ALL)) if you modify the profile by updating the value of the PROFILE_TEXT column directly.
  • 4 When the COUNT keyword is not specified, the utility automatically determines the count value and collects the most frequently occurring values.
  • 5 The KEYCARD option is deprecated. The KEYCARD functionality is now built into the normal execution of the RUNSTATS INDEX utility and cannot be disabled.
  • 6 If the target is an XML index, histogram and frequency statistics are collected on the first key column of the XML index only, regardless of the NUMCOLS value.

Statistics profile options

Statistics profile options have the same meanings as they do when specified directly in a RUNSTATS utility control statement. For more information, see RUNSTATS TABLESPACE syntax and options.