SYSCOLDISTSTATS catalog table

The SYSCOLDISTSTATS catalog table contains zero or more rows per partition for the cardinality, frequency, and histogram statistics for a single column or a column group. The schema is SYSIBM.

No row is inserted if the index is a non-partitioned index. Rows in this table can be inserted, updated, and deleted.

Important: Use care when issuing SQL statements or using tools to update statistics values in catalog tables. If such updates introduce invalid data, unpredictable results can occur, including abends for RUNSTATS and other utilities. If such problems occur, you can run the RUNSTATS utility and collect statistics at the table space level to resolve the problems, in most cases.
Table 1. SYSIBM.SYSCOLDISTSTATS table column descriptions
Column name Data type Description Use
FREQUENCY
SMALLINT
NOT NULL
 Db2 utilities might populate this value, but its use is not recommended in DB2® version 5 or later. For best results, use its replacementejbd, the FREQUENCYF column. N
STATSTIME
TIMESTAMP
NOT NULL WITH
DEFAULT

If RUNSTATS or another utility with inline statistics updated the statistics, the date and time when the last utility invocation updated the statistics. The default value is '0001-01-01-00.00.00.000000'. The default value indicates that statistics were not collected. This column can be updated.

 G
IBMREQD
CHAR(1)
NOT NULL
 A value of Y indicates that the row was provided with the Db2 product code. For all other values, see Release dependency indicators.

The value in this field is not a reliable indicator of release dependencies.

 G
PARTITION
SMALLINT
NOT NULL
 Partition number for the table space that contains the table in which the column is defined. G
TBOWNER
VARCHAR(128)
NOT NULL

The schema of the table that contains the column.

 G
TBNAME
VARCHAR(128)
NOT NULL
 Name of the table that contains the column. G
NAME
VARCHAR(128)
NOT NULL
 Name of the column. If NUMCOLUMNS is greater than 1, this name identifies the first column name of the set of columns associated with the statistics. G
COLVALUE
VARCHAR(2000)
NOT NULL
FOR BIT DATA
 Contains the data of a frequently occurring value. Statistics are not collected for an index on a ROWID column. If the value has a non-character data type, the data might not be printable. S
TYPE
CHAR(1)
NOT NULL WITH
DEFAULT 'F'
 The type of statistics gathered:
C
Cardinality
F
Frequent value
H
Histogram statistics
N
Non-padded frequent value
 G
CARDF
FLOAT
NOT NULL WITH
DEFAULT -1

If TYPE is C, the value is the number of distinct values for the column group. If TYPE is N or TYPE is F, the value is the number of rows or keys in the partition for which the FREQUENCYF value applies. If TYPE is H, the number of distinct values for the column group in a quantile indicated by QUANTILENO.

 S
COLGROUPCOLNO
VARCHAR(254)
NOT NULL WITH
DEFAULT
FOR BIT DATA
 Identifies the set of columns associated with the statistics. If the statistics are only associated with a single column, the field contains a zero length. Otherwise, the field is an array of SMALLINT column numbers with a dimension equal to the value in NUMCOLUMNS. This is an updatable column. S
NUMCOLUMNS
SMALLINT
NOT NULL WITH
DEFAULT 1
 Identifies the number of columns associated with the statistics. G
FREQUENCYF
FLOAT
NOT NULL WITH
DEFAULT -1

Gives the percentage of rows in the table with the value specified in COLVALUE when the number is multiplied by 100. For example, a value of '1' indicates 100%. A value of '.153' indicates 15.3%.

When TYPE='H', this is the percentage of rows in table which falls in the quantile indicated by QUANTILENO whose range is limited by [LOWVALUE, HIGHVALUE].

Statistics are not collected for an index on a ROWID column.

 G
—
VARCHAR(1000)
NOT NULL WITH
DEFAULT
FOR BIT DATA
 Internal use only. I
QUANTILENO
SMALLINT
NOT NULL WITH
DEFAULT -1
 Ordinary sequence number of a quantile in the whole consecutive value range, from low to high. This column is not updatable. G
LOWVALUE
VARCHAR(2000)
NOT NULL WITH
DEFAULT FOR
BIT DATA
 For TYPE='H', this is the lower bound for the quantile indicated by QUANTILENO. Not used if TYPE is not 'H'. This column is not updatable. G
HIGHVALUE
VARCHAR(2000)
NOT NULL WITH
DEFAULT FOR
BIT DATA
 For TYPE='H', this is the higher bound for the quantile indicated by QUANTILENO. Not used if TYPE is not 'H'. This column is not updatable. G