SYSTABLEINDEXSTAT
The SYSTABLEINDEXSTAT view contains one row for every index that has at least one partition or member built over a table. If the index is over more than one partition or member, the statistics include all those partitions and members. If the table is a distributed table, the partitions that reside on other database nodes are not included. They are contained in the catalog views of the other database nodes.
The following table describes the columns in the SYSTABLEINDEXSTAT 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. |
PARTITION_TYPE | PARTTYPE | CHAR(1) | The type of the table partitioning:
|
NUMBER_PARTITIONS | NBRPARTS | INTEGER | Number of partitions or members of the table. |
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. |
INDEX_SCHEMA | INDSCHEMA | VARCHAR(128) | Name of the SQL schema that contains the index, logical file, or constraint. |
INDEX_NAME | INDNAME | VARCHAR(128) | Name of the index, logical file, or constraint. |
INDEX_TYPE | INDTYPE | VARCHAR(11) | The type of the index:
|
NUMBER_KEY_COLUMNS | INDKEYS | BIGINT | Number of columns that define the index key. |
COLUMN_NAMES | COLNAMES | VARCHAR(1024) | A comma separated list of column names that define the index key. If the length of all the column names exceeds 1024, '...' is returned at the end of the column value. |
NUMBER_LEAF_PAGES | NLEAF | BIGINT | Not applicable for Db2® for i. Will always be -1. |
NUMBER_LEVELS | NLEVELS | SMALLINT | Not applicable for Db2 for i. Will always be -1. |
FIRSTKEYCARD | KEYCARD1 | BIGINT | The total number of distinct first key values for all index partitions. If the index is an encoded vector index, this is the total number of unique values for the entire index key. |
FIRST2KEYCARD | KEYCARD2 | BIGINT | The total number of distinct keys using the first two columns for all index partitions. If the index is an encoded vector index, -1 is returned. |
FIRST3KEYCARD | KEYCARD3 | BIGINT | The total number of distinct keys using the first three columns for all index partitions. If the index is an encoded vector index, -1 is returned. |
FIRST4KEYCARD | KEYCARD4 | BIGINT | The total number of distinct keys using the first four columns for all index partitions. If the index is an encoded vector index, -1 is returned. |
FULLKEYCARD | KEYCARDF | BIGINT | The total number of distinct full key values for all index partitions. If the index has more than 4 key columns or is an encoded vector index, -1 is returned. |
CLUSTERRATIO | CLSRATIO | SMALLINT | Not applicable for Db2 for i. Will always be -1. |
CLUSTERFACTOR | CLSFACTOR | DOUBLE | Not applicable for Db2 for i. Will always be -1. |
SEQUENTIAL_PAGES | SEQPAGES | BIGINT | Not applicable for Db2 for i. Will always be -1. |
DENSITY | DENSITY | INTEGER | Not applicable for Db2 for i. Will always be -1. |
PAGE_FETCH_PAIRS | FETCHPAIRS | VARCHAR(520) | Not applicable for Db2 for i. Will always be -1. |
NUMBER_KEYS | NUMRIDS | BIGINT | The total number of keys for all index partitions. If the index is invalid or is an encoded vector index, -1 is returned. |
NUMRIDS_DELETED | NUMRIDSDLT | BIGINT | Not applicable for Db2 for i. Will always be 0. |
NUM_EMPTY_LEAFS | EMPTYLEAFS | BIGINT | Not applicable for Db2 for i. Will always be 0. |
AVERAGE_RANDOM_FETCH_PAGES | AVGRNDFTCH | DOUBLE | Not applicable for Db2 for i. Will always be -1. |
AVERAGE_RANDOM_PAGES | AVGRNDPAGE | DOUBLE | Not applicable for Db2 for i. Will always be -1. |
AVERAGE_SEQUENCE_GAP | AVGSEQGAP | DOUBLE | Not applicable for Db2 for i. Will always be -1. |
AVERAGE_SEQUENCE_FETCH_GAP | AVGSEQFGAP | DOUBLE | Not applicable for Db2 for i. Will always be -1. |
AVERAGE_SEQUENCE_PAGES | AVGSEQPAGE | DOUBLE | Not applicable for Db2 for i. Will always be -1. |
AVERAGE_SEQUENCE_FETCH_PAGES | AVGSEQFPAG | DOUBLE | Not applicable for Db2 for i. Will always be -1. |
AVGPARTITION_CLUSTERRATIO | PCLSRATIO | SMALLINT | Not applicable for Db2 for i. Will always be -1. |
AVGPARTITION_CLUSTERFACTOR | PCLSFACTOR | DOUBLE | Not applicable for Db2 for i. Will always be -1. |
AVGPARTITION_PAGE_FETCH_PAIRS | PFETCHPAIR | VARCHAR(520) | Not applicable for Db2 for i. Will always be an empty string. |
DATAPARTITION_CLUSTERFACTOR | DCLSFACTOR | DOUBLE | A statistic measuring the "clustering" of the index keys with regard to data partitions. It is a number between 0 and 1, with 1 representing perfect clustering and 0 representing no clustering. |
INDCARD | INDCARD | BIGINT | Number of keys in the index. If the index is invalid or is an encoded vector index, -1 is returned. |
INDEX_VALID | VALID | CHAR(1) | An indication of whether any index is invalid and needs to be
rebuilt:
|
INDEX_HELD | HELD | CHAR(1) | An indication of whether a pending rebuild of the index is
currently held by the user:
|
CREATE_TIMESTAMP | CREATED | TIMESTAMP | Maximum timestamp when any partition or member of the index was created. |
LAST_BUILD_TIMESTAMP | LASTBUILD | TIMESTAMP | Maximum timestamp when any partition or member of the index was last rebuilt. |
LAST_QUERY_USE | LASTQRYUSE | TIMESTAMP Nullable
|
Maximum timestamp of the last time any partition or member of the index was used in a query since the last time the usage statistics were reset. If no partition or member of the index has ever been used in a query since the last time the usage statistics were reset, contains null. |
LAST_STATISTICS_USE | LASTSTUSE | TIMESTAMP Nullable
|
Maximum timestamp of the last time any partition or member of the index was used by the optimizer for statistics since the last time the usage statistics were reset. If no partition or member of the index has ever been used for statistics since the last time the usage statistics were reset, contains null. |
QUERY_USE_COUNT | QRYUSECNT | BIGINT | Total number of times any partition or member of the index was used in a query since the last time the usage statistics were reset. If no partition or member of the index has ever been used in a query since the last time the usage statistics were reset, contains 0. |
QUERY_STATISTICS_COUNT | QRYSTCNT | BIGINT | Total number of times any partition or member of the index was used by the optimizer for statistics since the last time the usage statistics were reset. If no partition or member of the index has ever been used for statistics since the last time the usage statistics were reset, contains 0. |
LAST_USED_TIMESTAMP | LASTUSED | TIMESTAMP Nullable
|
Maximum timestamp of the last time any partition or member of the index was used directly by an application for native record I/O or SQL operations. If no partition or member of the index has ever been used, contains null. |
DAYS_USED_COUNT | DAYSUSED | INTEGER | Maximum number of days any partition or member of the index was used directly by an application for native record I/O or SQL operations since the last time the usage statistics were reset. If no partition or member of the index has ever been used since the last time the usage statistics were reset, contains 0. |
LAST_RESET_TIMESTAMP | LASTRESET | TIMESTAMP Nullable
|
Maximum timestamp of the last time the usage statistics were reset for the index. For more information see the Change Object Description (CHGOBJD) command. If the index's last used timestamp has never been reset, contains null. |
INDEX_SIZE | SIZE | BIGINT | Total size (in bytes) of the binary trees or encoded vector indexes of all partitions or members of the index. |
ESTIMATED_BUILD_TIME | ESTBLDTIME | INTEGER | Maximum estimated time (in seconds) required to rebuild any partition or member of the index. |
LAST_BUILD_TIME | LSTBLDTIME | INTEGER Nullable
|
Elapsed time (in seconds) the last time the index was built. Contains null if the last build information is not available. |
LAST_BUILD_KEYS | LSTBLDKEYS | BIGINT Nullable
|
Number of keys the last time the index was built. Contains null if the last build information is not available. |
LAST_BUILD_DEGREE | LSTBLDDEG | SMALLINT Nullable
|
Parallel degree the last time the index was built. Contains null if the last build information is not available. |
DELAYED_MAINT_KEYS | DLYKEYS | INTEGER Nullable
|
Maximum number of keys that need to be inserted into the binary tree of any partition or member of a delayed maintenance index. If the index is not a delayed maintenance index, contains null. |
SPARSE | SPARSE | CHAR(1) | Indicates whether the index contains
keys for all the rows of its depended on table:
|
DERIVED_KEY | DERIVED | CHAR(1) | Indicates whether the any key columns
in the index are expressions:
|
PARTITIONED | PARTITION | CHAR(1) | Indicates whether the index is partitioned
or not partitioned:
|
ACCPTH_TYPE | ACCPTHTYPE | CHAR(1) | Indicates the type of index:
|
UNIQUE | UNIQUE | CHAR(1) | Indicates whether an index is unique:
|
SRTSEQ_TYPE | SRTSEQ | CHAR(1) | Indicates whether the index uses
a collating sequence:
|
LOGICAL_PAGE_SIZE | PAGE_SIZE | INTEGER Nullable
|
The logical page size of the index. If the index is an encoded vector index, contains null. |
OVERFLOW_VALUES | OVERFLOW | INTEGER Nullable
|
Maximum number of distinct key values that have overflowed any partition or member of the encoded vector index. If the index is not an encoded vector index, contains null. |
EVI_CODE_SIZE | CODE_SIZE | INTEGER Nullable
|
The size of the byte code of the encoded vector index. If the index is not an encoded vector index, contains null. |
LOGICAL_READS | LGLREADS | BIGINT | Total number of logical read operations for any partition or member of the index since the last IPL. |
PHYSICAL_READS | PHYREADS | BIGINT | Not applicable for Db2 for i. Will always be 0. |
SEQUENTIAL_READS | SEQREADS | BIGINT | Number of sequential read operations for the index since the last IPL. |
RANDOM_READS | RANREADS | BIGINT | Number of random read operations for the index since the last IPL. |
SEARCH_CONDITION | IXWHERECON | VARGRAPHIC(1024) CCSID 1200 | If an index is sparse, the search condition of the index. If the length of the search condition exceeds 1024, '...' is returned at the end of the column value. |
KEEP_IN_MEMORY | KEEPINMEM | CHAR(1) | Indicates whether the index should
be kept in memory:
|
MEDIA_PREFERENCE | MEDIAPREF | SMALLINT | Indicates the media preference of
the index:
|
INCLUDE_EXPRESSION | IXINCEXPR | VARGRAPHIC(1024) CCSID 1200 Nullable
|
Index INCLUDE expression. Contains null if the index does not have an INCLUDE expression. |
SYSTEM_TABLE_SCHEMA | SYS_DNAME | CHAR(10) | System schema name. |
SYSTEM_TABLE_NAME | SYS_TNAME | CHAR(10) | System table name. |