Statistics used for access path selection

Db2 uses certain values from the catalog tables directly when selecting access paths.

For example, the SYSTABLES and SYSTABLESPACE catalog tables indicate how much data the tables referenced by a query contain and how many pages hold data, the SYSINDEXES table indicates the most efficient index for a query, and the SYSCOLUMNS and SYSCOLDIST catalog tables indicate estimated filter factors for predicates.

The following tables list columns in catalog tables that Db2 uses for access path selection, values that trigger the use of a default value, and corresponding default values. Catalog table columns that are used directly by Db2 during access path selection are identified by a "Yes" value in the Used for access paths? column of the following tables.

Every table that RUNSTATS updates

As shown in the following table, the STATSTIME column is updated in every table that RUNSTATS updates.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
STATSTIME	Yes	Yes	No	If updated most recently by RUNSTATS, the date and time of that update, not updatable in SYSINDEXPART and SYSTABLEPART. Used for access path selection for SYSCOLDIST if duplicate column values exist for the same column (by user insertion).

SYSCOLDIST catalog table

Contains table level frequency, histogram, and multi-column cardinality statistics used by the Db2 to estimate filter factors.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
CARDF	Yes	Yes	Yes	For TYPE C, the number of distinct values gathered in the column group; for TYPE F, the number of distinct values for the column group -1; for TYPE='H', the number of distinct values in the column group of the interval indicated by the value of the QUANTILENO column. 5
COLGROUPCOLNO	Yes	Yes	Yes	The set of columns associated with the statistics. Contains an empty string if NUMCOLUMNS = 1.
COLVALUE	Yes	Yes	Yes	Frequently occurring value in the distribution.5
FREQUENCYF	Yes	Yes	Yes	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.5
HIGHVALUE	Yes	No	Yes	For TYPE='H', the high bound for the interval indicated by the value of the QUANTILENO column. 5
LOWVALUE	Yes	No	Yes	For TYPE='H', the low bound for the interval indicated by the value of the QUANTILENO column. 5
NUMCOLUMNS	Yes	Yes	Yes	The number of columns that are associated with the statistics. The default value is 1.
TYPE	Yes	Yes	Yes	The type of statistics gathered: C Cardinality F Frequent value N Non-padded H Histogram statistics
QUANTILENO	Yes	No	Yes	For histogram statistics, the ordinary sequence number of the quantile in the whole consecutive value range from low to high.

SYSCOLDISTSTATS catalog table

Contains partition-level frequency, histogram, and multi-column cardinality statistics that are used by RUNSTATS to aggregate table-level frequency, histogram, and multi-column cardinality statistics that are stored in SYSIBM.SYSCOLDIST.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1, 2	Description
CARDF	Yes	Yes	No	A number, which multiplied by 100, gives the percentage of rows that contain the value of COLVALUE; for TYPE='F' or TYPE='N' the number of rows or keys in the partition for which the FREQUENCYF value applies; 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. 5
COLGROUPCOLNO	Yes	Yes	No	The set of columns associated with the statistics.
COLVALUE	Yes	Yes	No	Frequently occurring value in the distribution. 5
FREQUENCYF	Yes	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. 5
HIGHVALUE	Yes	No	No	For TYPE='H', the high bound for the interval indicated by the value of the QUANTILENO column. 5
KEYCARDDATA	Yes	Yes	No	The internal representation of the estimate of the number of distinct values in the partition.
LOWVALUE	Yes	No	No	For TYPE='H', the low bound for the interval indicated by the value of the QUANTILENO column. 5
NUMCOLUMNS	Yes	Yes	No	The number of columns associated with the statistics. The default value is 1.
TYPE	Yes	Yes	No	The type of statistics gathered: C Cardinality F Frequent value N Non-padded H Histogram statistics
QUANTILENO	Yes	No	No	For histogram statistics, the ordinary sequence number of the quantile in the whole consecutive value range from low to high.

SYSCOLSTATS catalog table

Contains partition-level column statistics that are used by Db2 to determine the degree of parallelism, and are also sometimes used to bound filter factor estimates.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1, 3	Description
COLCARD	Yes	Yes	Yes 3	The number of distinct values in the partition. Do not update this column manually without first updating COLCARDDATA to a value of length 0. For XML column indicators, NODEID columns, and XML tables, this value of this column is set to -2.4
COLCARDDATA	Yes	Yes	No	The internal representation of the estimate of the number of distinct values in the partition. A value appears here only if RUNSTATS TABLESPACE is run on the partition. Otherwise, this column contains a string of length 0, indicating that the actual value is in COLCARD.
HIGHKEY	Yes	Yes	Yes 3	First 2000 bytes of the highest value of the column within the partition.If the partition is empty, the value is set to a string of length 0. For LOB columns, XML column indicators, NODEID columns and XML tables, the value of this column is set to blank. 4
HIGH2KEY	Yes	Yes	Yes 3	First 2000 bytes of the second highest value of the column within the partition. If the partition is empty, the value is set to a string of length 0. For LOB columns, XML column indicators, NODEID columns and XML tables, the value of this column is set to blank. This column is updated with decoded values if the column is a randomized key column. 4
LOWKEY	Yes	Yes	Yes 3	First 2000 bytes of the lowest value of the column within the partition. If the partition is empty, the value is set to a string of length 0.For LOB columns, XML column indicators, NODEID columns and XML tables, the value of this column is set to blank. 4
LOW2KEY	Yes	Yes	Yes 3	First 2000 bytes of the second lowest value of the column within the partition.If the partition is empty, the value is set to a string of length 0.For LOB columns, XML column indicators, NODEID columns and XML tables, the value of this column is set to blank. This column is updated with decoded values if the column is a randomized key column. 4
PARTITION	Yes	Yes	Yes	Partition number for the table space that contains the table in which the column is defined.

SYSCOLUMNS catalog table

Contains one row for every column of each table and view.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
COLCARDF	Yes	Yes	Yes	Estimated number of distinct values in the column, -1 to trigger use of the default value (25) and -2 for auxiliary indexes, XML column indicators, NODEID columns, and XML tables. 4
HIGH2KEY	Yes	Yes	Yes	First 2000 bytes of the second highest value in this column.If the table is empty, the value is set to a string of length 0. For auxiliary indexes, XML column indicators, NODEID columns and XML tables, the value of this column is set to blank.RUNSTATS does not update HIGH2KEY if the column is a randomized key column. 4
LOW2KEY	Yes	Yes	Yes	First 2000 bytes of the second lowest value in this column.If the table is empty, the value is set to a string of length 0. For auxiliary indexes, XML column indicators, NODEID columns and XML tables, the value of this column is set to blank.RUNSTATS does not update LOW2KEY if the column is a randomized key column. 4

SYSINDEXES catalog table

Contains table-level index statistics, that are used by Db2 for index costing.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
AVGKEYLEN	Yes	No	No	Average key length. For an EXCLUDE NULL KEYS index, the statistics are based on the actual contents of the index.
CLUSTERED	Yes	Yes	No	Whether the table is actually clustered by the index. The value of this column is set to blank for auxiliary indexes, NODEID indexes, and XML indexes. For an EXCLUDE NULL KEYS index, the statistics are based on the actual contents of the index.
CLUSTERING	No	No	Yes	Whether the index was created using CLUSTER.
CLUSTERRATIOF	Yes	Yes	Yes	A number which, when multiplied by 100, gives the percentage of rows in clustering order. For example, 1 indicates that all rows are in clustering order and .87825 indicates that 87.825% of the rows are in clustering order. For a partitioned index, it is the weighted average of all index partitions in terms of the number of rows in the partition. The value of this column is set to -2 for auxiliary indexes, NODEID indexes, and XML indexes. If this columns contains the default, 0, Db2 uses the value in CLUSTERRATIO, a percentage, for access path selection. For an EXCLUDE NULL KEYS index, the statistics are based on the actual contents of the index.
FIRSTKEYCARDF	Yes	Yes	Yes	Number of distinct values of the first key column, or an estimate if updated while collecting statistics on a single partition, -1 to trigger use of the default value (25).
FULLKEYCARDF	Yes	Yes	Yes	Number of distinct values of the full key, -1 to trigger use of the default value (25).
NLEAF	Yes	Yes	Yes	Number of active leaf pages in the index, -1 to trigger use of the default value (SYSTABLES.CARD/300).
NLEVELS	Yes	Yes	Yes	Number of levels in the index tree, -1 to trigger use of the default value (2).
SPACEF	Yes	Yes	No	Disk storage in KB.
DATAREPEATFACTORF	Yes	Yes	Yes	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 an EXCLUDE NULL KEYS index, the statistics are based on the actual contents of the index.

SYSINDEXPART catalog table

Contains statistics for index space utilization and index organization. For partitioning index of an index controlled partitioned table space, the limit key column is also used in limited partition scan scenarios.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
AVGKEYLEN	Yes	No	No	Average key length. For an EXCLUDE NULL KEYS index, the statistics are based on the actual contents of the index.
CARDF	Yes	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 an EXCLUDE NULL KEYS index, the statistics are based on the actual contents of the index.
DSNUM	Yes	Yes	No	Number of data sets.
EXTENTS	Yes	Yes	No	Number of data set extents (for multiple pieces, the value is for the extents in the last data set).
FAROFFPOSF	Yes	No	No	Number of times that accessing a different, far-off page is necessary when accessing all the data records in index order. Each time that Db2 accesses a far-off page, accessing the next record in index order probably requires I/O activity. For nonsegmented table spaces, a page is considered far-off from the present page if the two page numbers differ by 16 or more. For segmented table spaces, a page is considered far-off from the present page if the two page numbers differ by SEGSIZE * 2 or more. Together, NEAROFFPOSF and FAROFFPOSF indicate how well the index follows the cluster pattern of the table space. For a clustering index, NEAROFFPOSF and FAROFFPOSF approach a value of 0 as clustering improves. A reorganization should bring them nearer their optimal values; however, if a nonzero FREEPAGE value is specified on the CREATE TABLESPACE statement, the NEAROFFPOSF after reorganization reflects the table on which the index is defined. Do not expect optimal values for non-clustering indexes. The value is -1 if statistics have not been gathered. The value is -2 if the index is a hash index, node ID index, or an XML index. For an EXCLUDE NULL KEYS index, the statistics are based on the actual contents of the index.
LEAFDIST	Yes	No	No	100 times the number of pages between successive leaf pages.The value is -2 if the index is a node ID index, AUX index, hash index,or an XML index.
LEAFFAR	Yes	Yes	No	Number of leaf pages located physically far away from previous leaf pages for successive active leaf pages accessed in an index scan. See LEAFNEAR and LEAFFAR columns for more information.The value is -2 if the index is a hash index, node ID index, or an XML index.
LEAFNEAR	Yes	Yes	No	Number of leaf pages located physically near previous leaf pages for successive active leaf pages. See LEAFNEAR and LEAFFAR columns for more information.The value is -2 if the index is a hash index, node ID index, or an XML index.
LIMITKEY	No	No	Yes	The limit key of the partition in an internal format, 0 if the index is not partitioned.
NEAROFFPOSF	Yes	No	No	Number of times that accessing a different, near-off page would be necessary when accessing all the data records in index order. Each time that Db2 accesses a near-off page, accessing the next record in index order would probably require I/O activity. For more information about NEAROFFPOSF, see the description of FAROFFPOSF. NEAROFFPOSF is incremented if the current indexed row is not on the same or next data page of the previous indexed row, and if the distance between the two data pages does not qualify for FAROFFPOSF. For nonsegmented table spaces, a page is considered near-off from the present page if the difference between the two page numbers is greater than or equal to 2, and less than 16. For segmented table spaces, a page is considered near-off from the present page if the difference between the two page numbers is greater than or equal to 2, and less than SEGSIZE * 2. A nonzero value in the NEAROFFPOSF field after a REORG might be attributed to the number of space map pages that are contained in the segmented table space. The value is -2 if the index is a hash index, node ID index, or an XML index. For an EXCLUDE NULL KEYS index, the statistics are based on the actual contents of the index.
PQTY	Yes	No	No	The primary space allocation in 4K blocks for the data set.
PSEUDO_DEL_ENTRIES	Yes	Yes	No	Number of pseudo-deleted keys.
SECQTYI	Yes	No	No	Secondary space allocation in units of 4 KB, stored in integer format instead of small integer format supported by SQTY. If a storage group is not used, the value is 0.
SPACE	Yes	No	No	The number of KB of space currently allocated for all extents (contains the accumulated space used by all pieces if a page set contains multiple pieces)
SQTY	Yes	No	No	The secondary space allocation in 4 KB blocks for the data set.
SPACEF	Yes	Yes	No	Disk storage in KB.

SYSINDEXSPACESTATS catalog table

Contains real-time statistics (RTS) for index spaces.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
NLEVELS	Yes	Yes	Yes	The number of levels in the index tree.
NLEAF	Yes	Yes	Yes	The number of leaf pages in the index.

SYSINDEXSTATS catalog table

Contains partition-level index statistics that are used by RUNSTATS to aggregate table-level index statistics that are stored in SYSIBM.SYSINDEXES.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
CLUSTERRATIOF	Yes	Yes	No	A number which, when multiplied by 100, gives the percentage of rows in clustering order. For example, 1 indicates that all rows are in clustering order and .87825 indicates that 87.825% of the rows are in clustering order. For an EXCLUDE NULL KEYS index, the statistics are based on the actual contents of the index.
FIRSTKEYCARDF	Yes	Yes	No	Number of distinct values of the first key column, or an estimate if updated while collecting statistics on a single partition.
FULLKEYCARDDATA	Yes	Yes	No	The internal representation of the number of distinct values of the full key.
FULLKEYCARDF	Yes	Yes	No	Number of distinct values of the full key.
KEYCOUNTF	Yes	Yes	No	Total number of RIDs in the index partition. The value is -1 if statistics have not been gathered. For an EXCLUDE NULL KEYS index, the statistics are based on the actual contents of the index.
NLEAF	Yes	Yes	No	Number of leaf pages in the index.
NLEVELS	Yes	Yes	No	Number of levels in the index tree.
DATAREPEATFACTORF	Yes	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 an EXCLUDE NULL KEYS index, the statistics are based on the actual contents of the index.

SYSKEYTARGETS catalog table

Contains table-level frequency, histogram, and multi-column cardinality statistics for column-expression index keys. The values are used by Db2 in filter factor estimation algorithms for matched expressions.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
CARDF	Yes	No	Yes	Number of distinct values for the key-target. The value of this column is set to -2 for NODEID indexes and XML indexes.
HIGH2KEY	Yes	Yes	Yes	Second highest key value
LOW2KEY	Yes	Yes	Yes	Second lowest key value
STATS_FORMAT	Yes	Yes	Yes	Type of statistics gathered: blank No statistics have been collected, or VARCHAR column statistical values are padded N Varchar statistical values are not padded

SYSKEYTARGETSTATS catalog table

Contains partition-level key statistics for keys in column-expression indexes. The values are used by RUNSTATS to aggregate table-level key column-expression statistics.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
HIGHKEY	Yes	Yes	No	Highest key value
HIGH2KEY	Yes	Yes	No	Second highest key value
LOWKEY	Yes	Yes	No	Lowest key value
LOW2KEY	Yes	Yes	No	Second lowest key value
STATS_FORMAT	Yes	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

SYSKEYTGTDIST catalog table

Contains table-level frequency, histogram, and multi-column cardinality statistics for column-expression index keys. The values are used by Db2 in filter factor estimation algorithms for matched expressions.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
CARDF	Yes	Yes	Yes	For TYPE C, Number of distinct values gathered in the key group; for TYPE F, number of distinct values for the key group -1; for TYPE='H', the number of distinct values in the column group of the interval indicated by the value in the QUANTILENO column.
KEYGROUPKEYNO	Yes	Yes	Yes	The set of KEYS associated with the statistics. Contains an empty string if NUMKEYS = 1.
KEYVALUE	Yes	Yes	Yes	Frequently occurring value in the distribution.
FREQUENCYF	Yes	Yes	Yes	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.
HIGHVALUE	Yes	Yes	Yes	For TYPE='H', the high bound for the interval indicated by the value of the QUANTILENO column.
LOWVALUE	Yes	Yes	Yes	For TYPE='H', the low bound for the interval indicated by the value of the QUANTILENO column.
NUMKEYS	Yes	Yes	Yes	The number of keys associated with the statistics. The default value is 1.
TYPE	Yes	Yes	Yes	The type of statistics gathered: C Cardinality F Frequent value N Non-padded H Histogram statistics
QUANTILENO	Yes	Yes	Yes	For histogram statistics, the ordinary sequence number of the quantile in the whole consecutive value range from low to high.

SYSKEYTGTDISTSTATS catalog table

Contains partition-level frequency, histogram, and multi-column cardinality statistics for column-expression index keys. The values are used by RUNSTATS to aggregate table-level statistics that are stored in SYSIBM.SYSCOLDIST.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
CARDF	Yes	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 COLVALUE that fall into the range between LOWVALUE and HIGHVALUE for the interval indicated by the value of the QUANTILENO column.
KEYVALUE	Yes	Yes	No	The set of keys associated with the statistics
KEYGROUPKEYNO	Yes	Yes	No	Frequently occurring value in the distribution.
FREQUENCYF	Yes	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.
HIGHVALUE	Yes	Yes	No	For TYPE='H', the high bound for the interval indicated by the value of the QUANTILENO column.
LOWVALUE	Yes	Yes	No	For TYPE='H', the low bound for the interval indicated by the value of the QUANTILENO column.
QUANTILENO	Yes	Yes	No	For histogram statistics, the ordinary sequence number of the quantile in the whole consecutive value range from low to high.

SYSLOBSTATS catalog table

Contains LOB table space statistics.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
AVGSIZE	Yes	Yes	No	Average size of a LOB in bytes.
FREESPACE	Yes	Yes	No	The number of KB of available space in the LOB table space.
ORGRATIO	Yes	Yes	No	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.

SYSROUTINES catalog table

Contains statistics for table functions.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
CARDINALITY	No	Yes	Yes	The predicted cardinality of a table function, -1 to trigger use of the default value (10000)
INITIAL_INSTS	No	Yes	Yes	Estimated number of instructions executed the first and last time the function is invoked, -1 to trigger use of the default value (40000)
INITIAL_IOS	No	Yes	Yes	Estimated number of IOs performed the first and last time the function is invoked, -1 to trigger use of the default value (0)
INSTS_PER_INVOC	No	Yes	Yes	Estimated number of instructions per invocation, -1 to trigger use of the default value (4000)
IOS_PER_INVOC	No	Yes	Yes	Estimated number of IOs per invocation, -1 to trigger use of the default value (0)

SYSTABLEPART catalog table

Contains statistics for space utilization.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
AVGROWLEN	Yes	No	No	Average row length
CARDF	Yes	No	No	Total number of rows in the table space or partition. For LOB table spaces, the number of LOBs in the table space.
DSNUM	Yes	Yes	No	Number of data sets.
EXTENTS	Yes	Yes	No	Number of data set extents (for multiple pieces, the value is for the extents in the last data set).
FARINDREF	Yes	No	No	Number of rows that are relocated far from their original page. If an update operation increases the length of a record by more than the amount of available space in the page in which it is stored, the record is moved to another page. Until the table space is reorganized, the record requires an additional page reference when it is accessed. The sum of NEARINDREF and FARINDREF is the total number of such records. For nonsegmented table spaces, a page is considered near the present page if the two page numbers differ by 16 or fewer; otherwise, it is far from the present page. For segmented table spaces, a page is considered near the present page if the two page numbers differ by (SEGSIZE * 2) or less. Otherwise, it is far from its original page. A record that is relocated near its original page tends to be accessed more quickly than one that is relocated far from its original page.
NEARINDREF	Yes	No	No	Number of rows relocated near their original page.
PAGESAVE	Yes	No	No	Percentage of pages, times 100, saved in the table space or partition as a result of using data compression. For example, a value of 25 indicates a savings of 25%, so that the required pages are only 75% of what would be required without data compression. The value is 0 if no savings from using data compression are likely, or if statistics have not been gathered. The value can be negative if using data compression causes an increase in the number of pages in the data set. This calculation includes the overhead bytes for each row, the required bytes for the dictionary, and the required bytes for the current FREEPAGE and PCTFREE specification for the table space and partition. This calculation is based on an average row length, and the result varies depending on the actual lengths of the rows.
PERCACTIVE	Yes	No	No	Percentage of space occupied by active rows, containing actual data from active tables, -2 for LOB table spaces. This value is influenced by the PCTFREE and the FREEPAGE parameters on the CREATE TABLESPACE statement and by unused segments of segmented table spaces.
PERCDROP	Yes	No	No	For non-segmented table spaces, the percentage of space occupied by rows of data from dropped tables; for segmented table spaces, 0.
PQTY	Yes	No	No	The primary space allocation in 4K blocks for the data set.
SECQTYI	Yes	No	No	Secondary space allocation in units of 4 KB, stored in integer format instead of small integer format supported by SQTY. If a storage group is not used, the value is 0.
SPACE	Yes	No	No	The number of KB of space currently allocated for all extents (contains the accumulated space used by all pieces if a page set contains multiple pieces).
SPACEF	Yes	Yes	No	Disk storage in KB.
SQTY	Yes	No	No	The secondary space allocation in 4K blocks for the data set

SYSTABLES catalog table

Contains table-level table statistics that are used by Db2 throughout the query costing process.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
AVGROWLEN	Yes	Yes	No	Average row length of the table specified in the table space.
CARDF	Yes	Yes	Yes	Total number of rows in the table or total number of LOBs in an auxiliary table, -1 to trigger use of the default value (10000).
EDPROC	No	No	Yes	Non-blank value if an edit exit routine is used.
NPAGES	Yes	Yes	Yes	Total number of pages on which rows of this table appear, -1 to trigger use of the default value (CEILING(1 + CARD/20))
NPAGESF	Yes	Yes	Yes	Number of pages used by the table.
PCTPAGES	Yes	Yes	No	For non-segmented table spaces, percentage of total pages of the table space that contain rows of the table; for segmented table spaces, the percentage of total pages in the set of segments assigned to the table that contain rows of the table.
PCTROWCOMP	Yes	Yes	Yes	Percentage of rows compressed within the total number of active rows in the table.
SPACEF	Yes	Yes	No	Disk storage in KB.

SYSTABLESPACE catalog table

Contains table-space level statistics that are used by Db2 for costing of non-segmented table spaces.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
AVGROWLEN	Yes	No	No	Average row length.
NACTIVEF	Yes	Yes	Yes	Number of active pages in the table space, the number of pages touched if a cursor is used to scan the entire file, 0 to trigger use of the value in the NACTIVE column instead. If NACTIVE contains 0, Db2 uses the default value (CEILING(1 + CARD/20)).
SPACE	Yes	No	No	Disk storage in KB.
SPACEF	Yes	Yes	No	Disk storage in KB.

SYSTABLESPACESTATS catalog table

Contains real-time statistics (RTS) for table spaces.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
TOTALROWS	Yes	Yes	Yes	The number of rows or LOBs that are in the table space or partition, calculated from the in-memory RTS counters for inserts and deletes.
NPAGES	Yes	Yes	Yes	The number of distinct pages with active rows in the partition or table space.

SYSTABSTATS catalog table

Contains partition-level table statistics that are used by Db2 when costing limited partition scans, and are also used by RUNSTATS to aggregate table-level table statistics that are stored in SYSIBM.SYSTABLES.

Column name	Set by RUNSTATS?	User can update?	Used for access paths? 1	Description
CARDF	Yes	Yes	Yes	Total number of rows in the partition, -1 to trigger use of the value in the CARD column. If CARD is -1, Db2 uses a default value(10000).
NACTIVE	Yes	Yes	No	Number of active pages in the partition.
NPAGES	Yes	Yes	Yes	Total number of pages on which rows of the partition appear, -1 to trigger use of the default value (CEILING(1 + CARD/20)).
PCTPAGES	Yes	Yes	No	Percentage of total active pages in the partition that contain rows of the table.
PCTROWCOMP	Yes	Yes	No	Percentage of rows compressed within the total number of active rows in the partition, -1 to trigger use of the default value (0).