History statistics in the Db2 catalog

When Db2 adds or changes rows in a catalog table, Db2 might also write information about the rows to a corresponding catalog history table. Although the catalog history tables are not identical to their counterpart tables, they do contain the same columns for access path information and space utilization information. The history statistics provide a way to study trends, to determine when utilities, such as REORG, should be run for maintenance, and to aid in space management.

Each catalog history table has the same name of the catalog table that it describes appended by '_HIST'. The following tables describe some of the relevant columns in the catalog history tables:

SYSIBM.SYSCOLDIST_HIST

Table 1. Historical statistics columns in the SYSCOLDIST_HIST catalog table

Column name	Provides access path statistics1	Provides space statistics	Description
CARDF	Yes	No	For TYPE C, Number of distinct values gathered in the column group; for TYPE='H', the number of distinct values in the column group of the interval indicated by the value in the QUANTILENO column
COLGROUPCOLNO	Yes	No	Identifies the columns involved in multi-column statistics
COLVALUE	Yes	No	Frequently occurring value in the key distribution
FREQUENCYF	Yes	No	A number, which multiplied by 100, gives the percentage of rows that contain the value of COLVALUE; for TYPE='H', the percentage of rows with the value of COLVALUE that fall into the range between LOWVALUE and HIGHVALUE for the interval indicated by the value of the QUANTILENO column.
HIGHVALUE	Yes	No	For TYPE='H', the value of the high bound for the interval indicated by the value of the QUANTILENO column.
LOWVALUE	Yes	No	For TYPE='H', the value of the low bound for the interval indicated by the value of the QUANTILENO column.
NUMCOLUMNS	Yes	No	Number of columns involved in multi-column statistics
TYPE	Yes	No	The type of statistics gathered: C Cardinality F Frequent value P Non-padded H Histogram statistics
QUANTILENO	Yes	No	For histogram statistics, the ordinary sequence number of the quantile in the whole consecutive value range from low to high.

SYSIBM.SYSCOLUMNS_HIST

Table 2. Historical statistics columns in the SYSCOLUMNS_HIST catalog table

Column name	Provides access path statistics1	Provides space statistics	Description
COLCARDF	Yes	No	Estimated number of distinct values in the column
HIGH2KEY	Yes	No	Second highest value of the column, or blank
LOW2KEY	Yes	No	Second lowest value of the column, or blank

SYSIBM.SYSINDEXES_HIST

Table 3. Historical statistics columns in the SYSINDEXES_HIST catalog table

Column name	Provides access path statistics1	Provides space statistics	Description
CLUSTERING	Yes	No	Whether the index was created with CLUSTER
CLUSTERRATIOF	Yes	No	A number, when multiplied by 100, gives the percentage of rows in the clustering order. For a sparse index, the statistics are based on the actual contents of the index.
FIRSTKEYCARDF	Yes	No	Number of distinct values in the first key column
FULLKEYCARDF	Yes	No	Number of distinct values in the full key
NLEAF	Yes	No	Number of active leaf pages
NLEVELS	Yes	No	Number of levels in the index tree
DATAREPEATFACTORF	Yes	No	The number of times that data pages are repeatedly scanned after the index key is ordered. This number is -1 if statistics have not been collected. Valid values are -1 or any value that is equal to or greater than 1. For a sparse index, the statistics are based on the actual contents of the index.

SYSIBM.SYSINDEXPART_HIST

Table 4. Historical statistics columns in the SYSINDEXPART_HIST catalog table

Column name	Provides access path statistics1	Provides space statistics	Description
CARDF	No	No	The number of RIDs in the index that refer to data rows or LOBs. The value is -1 if statistics have not been gathered. For a sparse index, the statistics are based on the actual contents of the index.
DSNUM	No	Yes	Number of data sets
EXTENTS	No	Yes	Number of data set extents (for multiple pieces, the value is for the extents in the last data set)
FAROFFPOSF	No	Yes	Number of rows referenced far from the optimal position. For a sparse index, the statistics are based on the actual contents of the index.
LEAFDIST	No	Yes	100 times the number of pages between successive leaf pages
LEAFFAR	No	Yes	Number of leaf pages located physically far away from previous leaf pages for successive active leaf pages accessed in an index scan
LEAFNEAR	No	Yes	Number of leaf pages located physically near previous leaf pages for successive active leaf pages
NEAROFFPOSF	No	Yes	Number of rows referenced near but not at the optimal position. For a sparse index, the statistics are based on the actual contents of the index.
PQTY	No	Yes	Primary space allocation in 4K blocks for the data set
PSEUDO_DEL_ENTRIES	No	Yes	Number of pseudo-deleted keys
SECQTYI	No	Yes	Secondary space allocation in 4K blocks for the data set.
SPACEF	No	Yes	Disk storage in KB

SYSIBM.SYSINDEXSTATS_HIST

Table 5. Historical statistics columns in the SYSINDEXSTATS_HIST catalog table

Column name	Provides access path statistics1	Provides space statistics	Description
CLUSTERRATIOF	Yes	No	A number, which when multiplied by 100, gives the percentage of rows in the clustering order. For a sparse index, the statistics are based on the actual contents of the index.
FIRSTKEYCARDF	Yes	No	Number of distinct values of the first key column
FULLKEYCARDF	Yes	No	Number of distinct values of the full key
KEYCOUNTF	Yes	No	Total number of RIDs in the index partition. The value is -1 if statistics have not been gathered. For a sparse index, the statistics are based on the actual contents of the index.
NLEAF	Yes	No	Number of leaf pages
NLEVELS	Yes	No	Number of levels in the index tree
DATAREPEATFACTORF	Yes	No	The number of times that data pages are repeatedly scanned after the index key is ordered. This number is -1 if statistics have not been collected. Valid values are -1 or any value that is equal to or greater than 1. For a sparse index, the statistics are based on the actual contents of the index.

SYSIBM.SYSKEYTARGETS_HIST

Table 6. Historical statistics columns in the SYSKEYTARGETS_HIST catalog table

Column name	Provides access path statistics1	Provides space statistics	Description
KEYCARDF	Yes	No	For type C statistics, the number of distinct values for key-target
HIGH2KEY	Yes	No	The second highest key value
LOW2KEY	Yes	No	The second lowest key value
STATS_FORMAT	Yes	No	Type of statistics gathered: blank No statistics have been collected, or VARCHAR column statistical values are padded N Varchar statistical values are not padded

SYSIBM.SYSKEYTGTDIST_HIST

Table 7. Historical statistics columns in the SYSKEYTGTDIST_HIST catalog table

Column name	Provides access path statistics1	Provides space statistics	Description
CARDF	Yes	No	For TYPE C, Number of distinct values gathered in the key group; for TYPE='H', the number of distinct values in the key group of the interval indicated by the value in the QUANTILENO column
KEYGROUPKEYNO	Yes	No	Identifies the keys involved in multi-column statistics
KEYVALUE	Yes	No	Frequently occurring value in the key distribution
HIGHVALUE	Yes	No	For TYPE='H', the value of the high bound for the interval indicated by the value of the QUANTILENO column.
FREQUENCYF	Yes	No	A number, which multiplied by 100, gives the percentage of rows that contain the value of KEYVALUE; for TYPE='H', the percentage of rows with the value of KEYVALUE that fall into the range between LOWVALUE and HIGHVALUE for the interval indicated by the value of the QUANTILENO column.
LOWVALUE	Yes	No	For TYPE='H', the value of the low bound for the interval indicated by the value of the QUANTILENO column.
NUMKEYS	Yes	No	Number of keys involved in multi-key statistics
TYPE	Yes	No	The type of statistics gathered: C Cardinality F Frequent value P Non-padded H Histogram statistics
QUANTILENO	Yes	No	For histogram statistics, the ordinary sequence number of the quantile in the whole consecutive value range from low to high.

SYSIBM.SYSLOBSTATS_HIST

Table 8. Historical statistics columns in the SYSLOBSTATS_HIST catalog table

Column name	Provides access path statistics1	Provides space statistics	Description
FREESPACE	No	Yes	The amount of free space in the LOB table space
ORGRATIO	No	Yes	The percentage of organization in the LOB table space. A value of 100 indicates perfect organization of the LOB table space. A value of 1 indicates that the LOB table space is disorganized. A value of 0.00 indicates that the LOB table space is totally disorganized. An empty LOB table space has an ORGRATIO value of 100.00.

SYSIBM.SYSTABLEPART_HIST

Table 9. Historical statistics columns in the SYSTABLEPART_HIST catalog table

Column name	Provides access path statistics1	Provides space statistics	Description
CARDF	No	Yes	Number of rows in the table space or partition
DSNUM	No	Yes	Number of data sets
EXTENTS	No	Yes	Number of data set extents (for multiple pieces, the value is for the extents in the last data set)
FARINDREF	No	Yes	Number of rows relocated far from their original position
NEARINDREF	No	Yes	Number of rows relocated near their original position
PAGESAVE	No	Yes	Percentage of pages saved by data compression
PERCACTIVE	No	Yes	Percentage of space occupied by active pages
PERCDROP	No	Yes	Percentage of space occupied by pages from dropped tables
PQTY	No	Yes	Primary space allocation in 4K blocks for the data set
SECQTYI	No	Yes	Secondary space allocation in 4K blocks for the data set.
SPACEF	No	Yes	The number of KB of space currently used

SYSIBM.SYSTABLES_HIST

Table 10. Historical statistics columns in the SYSTABLES_HIST catalog table

Column name	Provides access path statistics1	Provides space statistics	Description
AVGROWLEN	No	Yes	Average row length of the table specified in the table space
CARDF	Yes	No	Number of rows in the table or number of LOBs in an auxiliary table
NPAGESF	Yes	No	Number of pages used by the table
PCTPAGES	No	Yes	Percentage of pages that contain rows
PCTROWCOMP	Yes	No	Percentage of active rows compressed

SYSIBM.SYSTABSTATS_HIST

Table 11. Historical statistics columns in the SYSTABSTATS_HIST catalog table

Column name	Provides access path statistics1	Provides space statistics	Description
CARDF	Yes	No	Number of rows in the partition
NPAGES	Yes	No	Total number of pages with rows