SYSPARTITIONINDEXSTAT

The SYSPARTITIONINDEXSTAT view contains one row for every index built over a table partition or table member. Indexes that share another index’s binary tree are not included. If the table is a distributed table, the indexes over 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 SYSPARTITIONINDEXSTAT view:

Table 1. SYSPARTITIONINDEXSTAT 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.
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_MEMBER INDMEMBER VARCHAR(128)
Nullable
Name of the member of the index or logical file. If the index type is a constraint, the member name is null.
INDEX_TYPE INDTYPE VARCHAR(11) The type of the index:
INDEX
The index is an SQL index.
LOGICAL
The index is part of a logical file.
PHYSICAL
The index is part of a keyed physical file.
PRIMARY KEY
The index is a primary key constraint.
UNIQUE
The index is a unique constraint.
REFERENTIAL
The index is a foreign key constraint.
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 Number of distinct first key values. If the index is an encoded vector index, this is the total number of unique values for the entire index key.
FIRST2KEYCARD KEYCARD2 BIGINT Number of distinct keys using the first two columns of the index. If the index is an encoded vector index, -1 is returned.
FIRST3KEYCARD KEYCARD3 BIGINT Number of distinct keys using the first three columns of the index. If the index is an encoded vector index, -1 is returned.
FIRST4KEYCARD KEYCARD4 BIGINT Number of distinct keys using the first four columns of the index. If the index is an encoded vector index, -1 is returned.
FULLKEYCARD KEYCARDF BIGINT Number of distinct full key values. 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 an empty string.
NUMBER_KEYS NUMRIDS BIGINT Number of keys in the index. 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 the index is invalid and needs to be rebuilt:
0
The index is invalid.
1
The index is valid.
Start of change2End of change
Start of changeThe index was saved with STG(*FREE).End of change
INDEX_HELD HELD CHAR(1) An indication or whether a pending rebuild of the index is currently held by the user:
0
A rebuild of the index is not pending or is not held.
1
A pending rebuild of the index is held.
CREATE_TIMESTAMP CREATED TIMESTAMP The timestamp when the index was created.
LAST_BUILD_TIMESTAMP LASTBUILD TIMESTAMP The timestamp when the index was last rebuilt.
LAST_QUERY_USE LASTQRYUSE TIMESTAMP
Nullable
The timestamp of the last time the index was used in a query since the last time the usage statistics were reset. If the index has never been used in a query since the last time the usage statistics were reset, contains null.
LAST_STATISTICS_USE LASTSTUSE TIMESTAMP
Nullable
The timestamp of the last time the index was used by the optimizer for statistics since the last time the usage statistics were reset. If the index has never been used for statistics since the last time the usage statistics were reset, contains null.
QUERY_USE_COUNT QRYUSECNT BIGINT The number of times the index was used in a query since the last time the usage statistics were reset. If the index has never been used in a query since the last time the usage statistics were reset, contains 0.
QUERY_STATISTICS_COUNT QRYSTCNT BIGINT The number of times the index was used by the optimizer for statistics since the last time the usage statistics were reset. If the index has never been used for statistics since the last time the usage statistics were reset, contains 0.
LAST_USED_TIMESTAMP LASTUSED TIMESTAMP
Nullable
The timestamp of the last time the index was used directly by an application for native record I/O or SQL operations. If the index has never been used, contains null.
DAYS_USED_COUNT DAYSUSED INTEGER The number of days 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 the index has never been used since the last time the usage statistics were reset, contains 0.
LAST_RESET_TIMESTAMP LASTRESET TIMESTAMP
Nullable
The 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 Size (in bytes) of the data space index used by the index.
ESTIMATED_BUILD_TIME ESTBLDTIME INTEGER Estimated time (in seconds) required to rebuild 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.
LAST_BUILD_TYPE LSTBLDTYPE CHAR(1)
Nullable
An indication of whether the last index build was a complete build or a build from the delayed maintenance keys:
0
The last rebuild of the index was from the delayed maintenance keys.
1
The last build or rebuild of the index was a complete build from the rows in the table.

If the index has never been built, contains null.

LAST_INVALIDATION_TIMESTAMP LSTINVAL TIMESTAMP
Nullable
An indication of when the index was last invalidated. If the index has never been invalidated, contains null.
Start of changeLAST_INVALIDATION_REASON_CODEEnd of change Start of changeLSTINVRCEnd of change Start of changeSMALLINT
Nullable
End of change
Start of changeThe reason code when the index was last invalidated.

Reason code values are listed in the SYSINSDEXSTAT view.

If the index has never been invalidated, contains null.End of change
Start of changeLAST_INVALIDATION_REASON_TYPEEnd of change Start of changeLSTINVRTEnd of change Start of changeSMALLINT
Nullable
End of change
Start of changeThe reason type when the index was last invalidated.

Reason type values are listed in the SYSINSDEXSTAT view.

If the index has never been invalidated, contains null.End of change
DELAYED_MAINT_KEYS DLYKEYS INTEGER
Nullable
Number of keys that need to be inserted into the binary tree 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:
0
The index contains keys for all the rows of its depended on table.
1
The index is a select/omit logical file or SQL index with a WHERE clause and does not contain 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:
0
No key columns of the index are expressions.
1
At least one key column is an expression.
PARTITIONED PARTITION CHAR(1) Indicates whether the index is partitioned or not partitioned:
0
An SQL index is not partitioned (spans multiple partitions).
1
The index is not built over a partitioned table or built over a partitioned table and is partitioned (does not span multiple partitions or members).
2
The index is a logical file built over multiple partitions or members.
ACCPTH_TYPE ACCPTHTYPE CHAR(1) Indicates the type of index:
0
The index is a maximum 1 terabyte (*MAX1TB) binary radix index.
1
The index is a maximum 4 gigabyte (*MAX4GB) binary radix index.
2
The index is an encoded vector index.
UNIQUE UNIQUE CHAR(1) Indicates whether an index is unique:
0
The index is a UNIQUE index.
1
The index is a UNIQUE WHERE NOT NULL index.
2
The index is a non-unique first-in-first-out (FIFO) index.
3
The index is a non-unique last-in-last-out (LIFO) index.
4
The index is a non-unique first-change-first-out (FCFO) index.
SRTSEQ_TYPE SRTSEQ CHAR(1) Indicates whether the index uses a collating sequence:
0
The index does not use a collating table.
1
The index uses an alternate collating sequence (ALTSEQ).
2
The index uses a sort sequence (SRTSEQ).
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
The number of distinct key values that have overflowed 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 Number of logical read operations for 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:
0
No memory preference.
1
The index should be kept in memory, if possible.
MEDIA_PREFERENCE MEDIAPREF SMALLINT Indicates the media preference of the index:
0
No media preference.
255
The index should be allocated on Solid State Disk (SSD), if possible.
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.
SYSTEM_TABLE_MEMBER SYS_MNAME CHAR(10) System member name.