SYSCOLUMNSTAT
The SYSCOLUMNSTAT view contains one row for every column in a table partition or table member that has a 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:
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:
|
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. This is an estimated value
determined by the statistics engine at the time that the column statistics were last
collected. 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 | The average length of all values stored in the column for this partition. For varying-length character and binary strings, this is the number of bytes of data. For varying-length graphic strings, this is the number of bytes divided by 2. For fixed-length columns, this is the length attribute of the column. |
MAXIMUM_COLUMN_LENGTH | MAXCOLLEN | INTEGER | The maximum length of any value stored in the column for this partition. For varying-length character and binary strings, this is the number of bytes of data. For varying-length graphic strings, this is the number of bytes divided by 2. For fixed-length columns, this is the length attribute of the column. |
LENGTH_AT_90TH_PERCENTILE | LEN_AT_90P | INTEGER Nullable
|
The length where at least 90 percent of the rows in the partition have values in this column with an actual length less than or equal to this length value. Up to 10 percent of the rows exceed this length. This information can help guide the use of the ALLOCATE clause for varying-length columns. A value of 0 is returned if this information has not been collected. NULL if not a varying-length column. |
OVERFLOW_ROWS | OVERFLOW | BIGINT Nullable
|
The number of rows for this partition where the column overflows the fixed storage defined by the ALLOCATE clause. NULL if not a varying-length column. |
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) Nullable
|
Indicates whether the system can automatically age or remove
statistics collections for this table partition.
|
AGING_STATUS | AGING_STS | CHAR(1) Nullable
|
Indicates how current the statistics collection is for this
table partition.
|
BLOCK_OPTION | BLKOPTION | CHAR(1) Nullable
|
Indicates whether automatic statistics collection create requests
are allowed for this table partition.
|
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
|
The number of inserts, updates, and deletes that have ever occurred to this table partition. NULL if statistics are not collected. |
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
|
Number 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. |
TRANSLATION_ATTRIBUTES | TRANSATRS | VARCHAR(10) Nullable
|
Indicates the type of translations
that were used on data values when the statistic was collected.
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. |