Catalog statistics tables

Statistical information about the size of database tables, indexes, and statistical views is stored in system catalog tables.

The following tables provide a brief description of this statistical information and show where it is stored.
  • The Table column indicates whether a particular statistic is collected if the FOR INDEXES or AND INDEXES parameter on the RUNSTATS command is not specified.
  • The Indexes column indicates whether a particular statistic is collected if the FOR INDEXES or AND INDEXES parameter is specified.
Some statistics can be provided only by the table, some can be provided only by the indexes, and some can be provided by both. The multi-column distribution statistics listed in Table 4. Multi-column Distribution Statistics (SYSCAT.COLGROUPDIST and SYSSTAT.COLGROUPDIST) and Table 5. Multi-column Distribution Statistics (SYSCAT.COLGROUPDISTCOUNTS and SYSSTAT.COLGROUPDISTCOUNTS) are not collected by the RUNSTATS utility. You cannot update them manually.
Table 1. Table Statistics (SYSCAT.TABLES and SYSSTAT.TABLES)
Statistic Description RUNSTATS Option
Table Indexes
FPAGES Number of pages being used by a table Yes Yes
NPAGES Number of pages containing rows Yes Yes
OVERFLOW Number of rows that overflow Yes No
CARD Number of rows in a table (cardinality) Yes Yes (Note 1)
ACTIVE_BLOCKS For MDC tables, the total number of occupied blocks Yes No
Note:
  1. If the table has no indexes defined and you request statistics for indexes, no CARD statistics are updated. The previous CARD statistics are retained.
Table 2. Column Statistics (SYSCAT.COLUMNS and SYSSTAT.COLUMNS)
Statistic Description RUNSTATS Option
Table Indexes
COLCARD Column cardinality Yes Yes (Note 1)
AVGCOLLEN Average length of a column Yes Yes (Note 1)
HIGH2KEY Second highest value in a column Yes Yes (Note 1)
LOW2KEY Second lowest value in a column Yes Yes (Note 1)
NUMNULLS The number of null values in a column Yes Yes (Note 1)
SUB_COUNT The average number of sub-elements Yes No (Note 2)
SUB_DELIM_LENGTH The average length of each delimiter separating sub-elements Yes No (Note 2)
Note:
  1. Column statistics are collected for the first column in the index key.
  2. These statistics provide information about data in columns that contain a series of sub-fields or sub-elements that are delimited by blanks. The SUB_COUNT and SUB_DELIM_LENGTH statistics are collected only for columns of type CHAR and VARCHAR with a code page attribute of single-byte character set (SBCS), FOR BIT DATA, or UTF-8.
Table 3. Multi-column Statistics (SYSCAT.COLGROUPS and SYSSTAT.COLGROUPS)
Statistic Description RUNSTATS Option
Table Indexes
COLGROUPCARD Cardinality of the column group Yes No
Table 4. Multi-column Distribution Statistics (SYSCAT.COLGROUPDIST and SYSSTAT.COLGROUPDIST)
Statistic Description RUNSTATS Option
Table Indexes
TYPE
F = Frequency value
Q = Quantile value
 Yes No
ORDINAL Ordinal number of the column in the group Yes No
SEQNO Sequence number n that represents the nth TYPE value Yes No
COLVALUE The data value as a character literal or a null value Yes No
Table 5. Multi-column Distribution Statistics (SYSCAT.COLGROUPDISTCOUNTS and SYSSTAT.COLGROUPDISTCOUNTS)
Statistic Description RUNSTATS Option
Table Indexes
TYPE
F = Frequency value
Q = Quantile value
 Yes No
SEQNO Sequence number n that represents the nth TYPE value Yes No
VALCOUNT If TYPE = F, VALCOUNT is the number of occurrences of COLVALUE for the column group with this SEQNO. If TYPE = Q, VALCOUNT is the number of rows whose value is less than or equal to COLVALUE for the column group with this SEQNO. Yes No
DISTCOUNT If TYPE = Q, this column contains the number of distinct values that are less than or equal to COLVALUE for the column group with this SEQNO. Null if unavailable. Yes No
Table 6. Index Statistics (SYSCAT.INDEXES and SYSSTAT.INDEXES)
Statistic Description RUNSTATS Option
Table Indexes
NLEAF Number of index leaf pages No Yes
NLEVELS Number of index levels No Yes
CLUSTERRATIO Degree of clustering of table data No Yes (Note 2)
CLUSTERFACTOR Finer degree of clustering No Detailed (Notes 1,2)
DENSITY Ratio (percentage) of SEQUENTIAL_PAGES to number of pages in the range of pages that is occupied by the index (Note 3) No Yes
FIRSTKEYCARD Number of distinct values in the first column of the index No Yes
FIRST2KEYCARD Number of distinct values in the first two columns of the index No Yes
FIRST3KEYCARD Number of distinct values in the first three columns of the index No Yes
FIRST4KEYCARD Number of distinct values in the first four columns of the index No Yes
FULLKEYCARD Number of distinct values in all columns of the index, excluding any key value in an index for which all record identifiers (RIDs) are marked deleted No Yes
PAGE_FETCH_PAIRS Page fetch estimates for different buffer sizes No Detailed (Notes 1,2)
AVGPARTITION_CLUSTERRATIO Degree of data clustering within a single data partition No Yes (Note 2)
AVGPARTITION_CLUSTERFACTOR Finer measurement of degree of clustering within a single data partition No Detailed (Notes 1,2)
AVGPARTITION_PAGE_FETCH_PAIRS Page fetch estimates for different buffer sizes, generated on the basis of a single data partition No Detailed (Notes 1,2)
DATAPARTITION_CLUSTERFACTOR Number of data partition references during an index scan No (Note 6) Yes (Note 6)
SEQUENTIAL_PAGES Number of leaf pages located on disk in index key order, with few, or no large gaps between them No Yes
AVERAGE_SEQUENCE_PAGES Average number of index pages that are accessible in sequence; this is the number of index pages that the prefetchers can detect as being in sequence No Yes
AVERAGE_RANDOM_PAGES Average number of random index pages between sequential page accesses No Yes
AVERAGE_SEQUENCE_GAP Gap between sequences No Yes
AVERAGE_SEQUENCE_FETCH_PAGES Average number of table pages that are accessible in sequence; this is the number of table pages that the prefetchers can detect as being in sequence when they fetch table rows using the index No Yes (Note 4)
AVERAGE_RANDOM_FETCH_PAGES Average number of random table pages between sequential page accesses when fetching table rows using the index No Yes (Note 4)
AVERAGE_SEQUENCE_FETCH_GAP Gap between sequences when fetching table rows using the index No Yes (Note 4)
NUMRIDS The number of RIDs in the index, including deleted RIDs No Yes
NUMRIDS_DELETED The total number of RIDs in the index that are marked deleted, except RIDs on those leaf pages where all RIDs are marked deleted No Yes
NUM_EMPTY_LEAFS The total number of leaf pages on which all RIDs are marked deleted No Yes
INDCARD Number of index entries (index cardinality) No Yes
Note:
  1. Detailed index statistics are collected by specifying the DETAILED clause on the RUNSTATS command.
  2. CLUSTERFACTOR and PAGE_FETCH_PAIRS are not collected with the DETAILED clause unless the table is of sufficient size (greater than about 25 pages). In this case, CLUSTERRATIO is -1 (not collected). If the table is relatively small, only CLUSTERRATIO is collected by the RUNSTATS utility; CLUSTERFACTOR and PAGE_FETCH_PAIRS are not collected. If the DETAILED clause is not specified, only CLUSTERRATIO is collected.
  3. This statistic measures the percentage of pages in the file containing the index that belongs to that table. For a table with only one index defined on it, DENSITY should be 100. DENSITY is used by the optimizer to estimate how many irrelevant pages from other indexes might be read, on average, if the index pages were prefetched.
  4. This statistic cannot be computed when the table is in a DMS table space.
  5. Prefetch statistics are not collected during a load or create index operation, even if statistics collection is specified when the command is invoked. Prefetch statistics are also not collected if the seqdetect database configuration parameter is set to NO.
  6. When RUNSTATS options for table is No, statistics are not collected when table statistics are collected; when RUNSTATS options for indexes is Yes, statistics are collected when the RUNSTATS command is used with the INDEXES options.
Table 7. Column Distribution Statistics (SYSCAT.COLDIST and SYSSTAT.COLDIST)
Statistic Description RUNSTATS Option
Table Indexes
DISTCOUNT If TYPE = Q, DISTCOUNT is the number of distinct values that are less than or equal to COLVALUE statistics Distribution (Note 2) No
TYPE Indicator of whether the row provides frequent-value or quantile statistics Distribution No
SEQNO Frequency ranking of a sequence number to help uniquely identify the row in the table Distribution No
COLVALUE Data value for which a frequency or quantile statistic is collected Distribution No
VALCOUNT Frequency with which the data value occurs in a column; for quantiles, the number of values that are less than or equal to the data value (COLVALUE) Distribution No
Note:
  1. Column distribution statistics are collected by specifying the WITH DISTRIBUTION clause on the RUNSTATS command. Distribution statistics cannot be collected unless there is a sufficient lack of uniformity in the column values.
  2. DISTCOUNT is collected only for columns that are the first key column in an index.