Start of change

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
  • BOTH
  • LEAST
  • INDEX
  • KEYCARD
  • NUMCOLS
  • COUNT
  • MOST
  • BOTH
  • LEAST
  • 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.

>>-| column-spec |--+----------------------------------+-------><
                    '-| colgroup-spec |-| index-spec |-'   

column-spec:

   .-COLUMN--(--ALL--)-----------------.   
>>-+-----------------------------------+-----------------------><
   |            .-,---------------.    |   
   |            V             (1) |    |   
   '-COLUMN--(----column-name-----+--)-'   
Notes:
  1. The same column name must not be specified more than once. If all columns are listed in the COLUMN option, RUNSTATS treats it as-is, and does not replace the list with the COLUMN(ALL) option. You must specify the COLUMN(ALL) option explicitly.

colgroup-spec:

                .-,-----------.                               
                V             |                               
>>-COLGROUP--(----column-name-+--)--| colgroup-stats-spec |----><

index-spec:

>>-+-------------------------------------------------------------+-><
   '-+-INDEX--(--ALL--)--| correlation-stats-spec |------------+-'   
     |             (1)                                         |     
     +-INDEX--(--*------)--| correlation-stats-spec |----------+     
     |           .-,--------------------------------------.    |     
     |           V                                        |    |     
     '-INDEX--(----index-name--| correlation-stats-spec |-+--)-'     
Notes:
  1. 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.

colgroup-stats-spec:

>>-+--------------------------------------------------------------------------+-><
   |                          .-MOST--.             .-NUMQUANTILES--100-----. |   
   '-FREQVAL--COUNT--integer--+-------+--HISTOGRAM--+-----------------------+-'   
                              +-BOTH--+             '-NUMQUANTILES--integer-'     
                              '-LEAST-'                                           

correlation-stats-spec:

             (1)     
   .-KEYCARD-----.   
>>-+-------------+---------------------------------------------->

   .----------------------------------------------------------------.   
   V .-FREQVAL -NUMCOLS--1--COUNT--10--MOST-----------------------. |   
>----+------------------------------------------------------------+-+-><
     |                                            .-MOST--.       |     
     +-FREQVAL -NUMCOLS--integer--COUNT--integer--+-------+-------+     
     |                                            +-BOTH--+       |     
     |                                            '-LEAST-'       |     
     |            .-NUMCOLS--1--NUMQUANTILES--100---------------. |     
     '-HISTOGRAM--+---------------------------------------------+-'     
                  |                   .-NUMQUANTILES--100-----. |       
                  '-NUMCOLS--integer--+-----------------------+-'       
                                      '-NUMQUANTILES--integer-'         
Notes:
  1. The KEYCARD option is deprecated. The KEYCARD functionality is now built into the normal execution of the RUNSTATS utility and cannot be disabled.

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.

End of change