Rules for updating column statistics manually

There are certain guidelines that you should follow when updating statistics in the SYSSTAT.COLUMNS catalog view.

• When manually updating HIGH2KEY or LOW2KEY values, ensure that:
  • The values are valid for the data type of the corresponding user column.
  • The values are not NULL.
  • The length of the values must be the smaller of 33 or the maximum length of the target column data type, not including additional quotation marks, which can increase the length of the string to 68. This means that only the first 33 characters of the value in the corresponding user column will be considered in determining the HIGH2KEY or LOW2KEY values.
  • The values are stored in such a way that they can be used with the SET clause of an UPDATE statement, as well as for cost calculations. For character strings, this means that single quotation marks are added to the beginning and at the end of the string, and an extra quotation mark is added for every quotation mark that is already in the string. Examples of user column values and their corresponding values in HIGH2KEY or LOW2KEY are provided in Table 1.

    Table 1. HIGH2KEY and LOW2KEY values by data type

    Data type in user column	User data	Corresponding HIGH2KEY or LOW2KEY value
    INTEGER	-12	-12
    CHAR	abc	'abc'
    CHAR	ab'c	'ab''c'

  • HIGH2KEY is greater than LOW2KEY whenever there are more than three distinct values in the corresponding column. If there are three or less distinct values in the column, HIGH2KEY can be equal to LOW2KEY.
• The cardinality of a column (COLCARD in SYSSTAT.COLUMNS) cannot be greater than the cardinality of its corresponding table or statistical view (CARD in SYSSTAT.TABLES).
• The number of null values in a column (NUMNULLS in SYSSTAT.COLUMNS) cannot be greater than the cardinality of its corresponding table or statistical view (CARD in SYSSTAT.TABLES).
• Statistics are not supported for columns that are defined with LONG or large object (LOB) data types.
• If you insert hex values in UTF-16 encoding but the database is created in UTF-8 encoding, the characters that are stored in the catalog table are invalid. The solution is to insert the hex values in UTF-8 encoding or in the UPDATE statement specify the values with GX, as you can see in the following example:

  UPDATE SYSCAT.COLUMNS SET HIGH2KEY = GX'D48195A4868183A3',LOW2KEY = GX'048195A4868183A3'