SYSCOLUMNSTAT

The SYSCOLUMNSTAT view contains one row for every column in a table partition or table member Start of changethat has aEnd of change column statistics collection. If the table is a distributed table, the partitions that reside on other database nodes are not contained in this catalog view.

They are contained in the catalog views of the other database nodes. The following table describes the columns in the SYSCOLUMNSTAT view:

Table 1. SYSCOLUMNSTAT view
Column name System Column Name Data Type Description
TABLE_SCHEMA TABSCHEMA VARCHAR(128) Name of the SQL schema that contains the table.
TABLE_NAME TABNAME VARCHAR(128) Name of the table.
TABLE_PARTITION TABPART VARCHAR(128) Name of the table partition or member.
PARTITION_TYPE PARTTYPE CHAR(1) The type of the table partitioning:
blank
The table is not partitioned.
H
This is data hash partitioning.
R
This is data range partitioning.
D
This is distributed database hash partitioning.
PARTITION_NUMBER PARTNBR INTEGER

Nullable

 The partition number of this partition. If the table is a distributed table, contains null.
NUMBER_DISTRIBUTED_PARTITIONS DSTPARTS INTEGER

Nullable

 If the table is a distributed table, contains the total number of partitions. If the table is not a distributed table, contains null.
NUMBER_COLUMN_NAMES NBRCOLS INTEGER Number of column names in this collection. If only individual column statistics are wanted, only select rows where NUMBER_COLUMN_NAMES is one.

Currently, only one name is returned.
COLUMN_NAME COLNAME VARCHAR(1280) Name of the column(s). Up to 10 columns may be returned.

Currently, only one name is returned.
NUMBER_DISTINCT_VALUES COLCARD BIGINT Number of distinct values in the column. Contains -1 if statistics are not collected.
HIGH2KEY HIGH2KEY VARCHAR(254) Not applicable for DB2® for i. Contains the empty string.
LOW2KEY LOW2KEY VARCHAR(254) Not applicable for DB2 for i. Contains the empty string.
AVERAGE_COLUMN_LENGTH AVGCOLLEN INTEGER Not applicable for DB2 for i. Will always be -1.
NUMBER_NULLS NUMNULLS BIGINT The estimated number of NULL values. -1 if statistics are not collected.
SUB_COUNT SUB_COUNT SMALLINT Not applicable for DB2 for i. Will always be -1.
SUB_DELIM_LENGTH SUBDLENGTH SMALLINT Not applicable for DB2 for i. Will always be -1.
NUMBER_HISTOGRAM_RANGES NQUANTILES INTEGER Number of histogram ranges available for this statistics collection. The actual histogram range values can be obtained using the List Statistics Collection Details (QDBSTLDS, QdbstListDetailStatistics) API. Contains -1 if statistics are not collected.
NUMBER_MOST_FREQUENT_VALUES NMOSTFREQ INTEGER Number of most frequent values available. The actual most frequent values can be obtained using the List Statistics Collection Details (QDBSTLDS, QdbstListDetailStatistics) API. Contains -1 if statistics are not collected.
AVGDISTINCTPERPAGE AVGDSTPAGE DOUBLE

Nullable

 Not applicable for DB2 for i. Will always be NULL.
PAGEVARIANCERATIO PVARRATIO DOUBLE

Nullable

 Not applicable for DB2 for i. Will always be NULL.
STATISTICS_NAME STATNAME VARCHAR(128)

Nullable

 Unique name of this statistics collection for this table partition. NULL if statistics are not collected.
INTERNAL_STATISTICS_ID STATID VARCHAR(16) FOR BIT DATA

Nullable

 Internal statistics identifier of this statistics collection for this table partition. NULL if statistics are not collected.
STATISTIC_CREATED STATCREATE TIMESTAMP

Nullable

 Timestamp when the statistics collection was created. NULL if statistics are not collected.
STATISTIC_CREATOR STATCUSER VARCHAR(128)

Nullable

 User that created the statistic collection. *SYS if the system created the statistic collection. NULL if statistics are not collected.
STATISTIC_LAST_UPDATED UPDATEDTS TIMESTAMP

Nullable

 Timestamp when the statistics collection was last updated. NULL if statistics are not collected.
STATISTIC_UPDATER STATUUSER VARCHAR(128)

Nullable

 User that last updated the statistic collection. *SYS if the system automatically updated the statistic collection. NULL if statistics are not collected.
STATISTICS_SIZE STATSIZE BIGINT

Nullable

 Size of the statistics collection for this table partition. NULL if statistics are not collected.
AGING_MODE AGING_MODE VARCHAR(10) Indicates whether the system can automatically age or remove statistics collections for this table partition.
*SYS
The statistic collection will be automatically refreshed or removed by the system when necessary.
*USER
The statistic collection will only be refreshed or removed when explicitly requested by the user.
AGING_STATUS AGING_STS CHAR(1)

Nullable

 Indicates how current the statistics collection is for this table partition.
0
There are no indications that the statistics data needs to be refreshed.
1
There are indications that the statistics data needs to be refreshed.
NULL if statistics are not collected.
BLOCK_OPTION BLKOPTION CHAR(1) Indicates whether automatic statistics collection create requests are allowed for this table partition.
0
Automatic system initiated statistics collections are not blocked.
1
Automatic system initiated statistics collections are blocked.
CURRENT_LAST_CHANGE UPDATED TIMESTAMP

Nullable

 Timestamp when the data in the table partition was last changed. NULL if statistics are not collected.
CURRENT_ROWS CURROWS BIGINT

Nullable

 Current number of valid rows in the table partition. NULL if statistics are not collected.
CURRENT_DELETED_ROWS CURDELROWS BIGINT

Nullable

 Current number of deleted rows in the table partition. NULL if statistics are not collected.
CURRENT_DATA_CHANGES CURDATACHG BIGINT

Nullable

 Start of changeThe number of inserts, updates, and deletes that have ever occurred to this table partition. NULL if statistics are not collected.End of change
STATISTICS_ROWS STATROWS BIGINT

Nullable

 Number of valid rows in the table partition at the time the statistic was collected. NULL if statistics are not collected.
STATISTICS_DELETED_ROWS STATDELROW BIGINT

Nullable

 Number of deleted rows in the table partition at the time the statistic was collected. NULL if statistics are not collected.
STATISTICS_DATA_CHANGES STATDATCHG BIGINT

Nullable

 Start of changeNumber of inserts, updates, and deletes that had occurred to the table partition at the time the statistic was collected. NULL if statistics are not collected.End of change
TRANSLATION_ATTRIBUTES TRANSATRS VARCHAR(10)

Nullable

 Indicates the type of translations that were used on data values when the statistic was collected.
0
Unique weight translation.
1
Shared weight translation.
9
No translation.
If multiple columns are used in this collection, multiple translations are possible.

Currently, only one translation is returned.
TRANSLATION_TABLES TRANSTBLS VARCHAR(210)

Nullable

 Qualified names of the translation tables, if translation tables were used on the statistic collection.

The empty string is returned if no translation table was used. NULL if statistics are not collected.

If multiple columns are used in this collection, multiple translation tables are possible.

Currently, only one translation table is returned.
SYSTEM_TABLE_SCHEMA SYS_DNAME CHAR(10) System schema name.
SYSTEM_TABLE_NAME SYS_TNAME CHAR(10) System table name.
SYSTEM_TABLE_MEMBER SYS_MNAME CHAR(10) System member name.
SYSTEM_COLUMN_NAME SYS_CNAME VARCHAR(100) System column name. An array of up to 10 names are possible.

Currently, only one name is returned.
Parent topic: IBM i catalog tables and views