Correlations in the catalog

Important relationships exist among certain columns in Db2 catalog tables. Consider these relationships if you choose to modify the catalog statistics to achieve a more favorable access path.

Begin program-specific programming interface information.

Correlations exist among columns in the following catalog tables:

  • Columns within the SYSIBM.SYSCOLUMNS catalog table
  • Columns in the SYSIBM.SYSCOLUMNS and SYSIBM.SYSINDEXES catalog tables
  • Columns in the tables SYSIBM.SYSCOLUMNS and SYSIBM.SYSCOLDIST catalog tables
  • Columns in the tables SYSIBM.SYSCOLUMNS, SYSIBM.SYSCOLDIST, and SYSIBM.SYSINDEXES catalog tables
  • Columns with table space statistics and columns for partition-level statistics.

If you plan to modify the values of statistics in the catalog tables, consider the following correlations:

COLCARDF and FIRSTKEYCARDF
For a column that is the first column of an index, those two values are equal. If the index has only that one column, the two values are also equal to the value of FULLKEYCARDF.
COLCARDF, LOW2KEY, and HIGH2KEY
If the COLCARDF value is not '-1' or'-2', Db2 assumes that statistics exist for the column. In particular, it uses the values of LOW2KEY and HIGH2KEY in calculating filter factors. If COLDARDF = 1 or if COLCARDF = 2, Db2 uses HIGH2KEY and LOW2KEY as domain statistics, and generates frequencies on HIGH2KEY and LOW2KEY.
CARDF in SYSCOLDIST
CARDF is related to COLCARDF in SYSIBM.SYSCOLUMNS and to FIRSTKEYCARDF and FULLKEYCARDF in SYSIBM.SYSINDEXES. CARDF must be the minimum of the following values:
  • A value between FIRSTKEYCARDF and FULLKEYCARDF if the index contains the same set of columns
  • A value between MAX(COLCARDF of each column in the column group) and the product of multiplying together the COLCARDF of each column in the column group

For example, Assume the following set of statistics:

CARDF = 1000
NUMCOLUMNS = 3
COLGROUPCOLNO = 2,3,5
 
INDEX1 on columns 2,3,5,7,8
FIRSTKEYCARDF = 100                 CARDF must be between 100
FULLKEYCARDF = 10000                                and 10000
 
column 2 COLCARDF = 100
column 3 COLCARDF =  50
column 5 COLCARDF =  10

The range between FIRSTKEYCARDF and FULLKEYCARDF is 100 and 10,000. The maximum of the COLCARDF values is 50,000. Thus, the allowable range is 100–10,000.

CARDF in SYSTABLES
CARDF must be equal to or larger than any of the other cardinalities, SUCH AS COLCARDF, FIRSTKEYCARDF, FULLKEYCARDF, and CARDF in SYSIBM.SYSCOLDIST.
FREQUENCYF and COLCARDF or CARDF
The number of frequencies collected must be less than or equal to COLCARDF for the column or CARDF for the column group.
FREQUENCYF
The sum of frequencies collected for a column or column group must be less than or equal to 1.
End program-specific programming interface information.