SYSINDEXSTAT
The SYSINDEXSTAT view contains one row for every SQL index partition.
Use this view when you want to see information for a specific SQL index or set of SQL indexes. The information is similar to that returned via Show Indexes in System i® Navigator.
The following table describes the columns in the SYSINDEXSTAT view:
Column name | System Column Name | Data Type | Description |
---|---|---|---|
INDEX_SCHEMA | INDSCHEMA | VARCHAR(128) | Name of the SQL schema that contains the SQL index. |
INDEX_NAME | INDNAME | VARCHAR(128) | Name of the SQL index. |
INDEX_PARTITION | INDPART | VARCHAR(128) | Partition or member name of the SQL index. |
INDEX_OWNER | INDOWNER | VARCHAR(128) | SQL index owner. |
INDEX_TEXT | LABEL | VARGRAPHIC(50) CCSID 1200 Nullable
|
Text of the SQL index. Contains null if text does not exist for the SQL index. |
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. |
INDEX_VALID | VALID | VARCHAR(3) | An indication or whether the SQL
index is invalid and needs to be rebuilt:
|
INDEX_CREATE_TIMESTAMP | INDCREATED | TIMESTAMP | The timestamp when the SQL index was created. |
CREATE_TIMESTAMP | CREATED | TIMESTAMP | The timestamp when the SQL index partition was created. |
LAST_BUILD_TIMESTAMP | LASTBUILD | TIMESTAMP Nullable
|
The timestamp when the SQL index was last rebuilt. Contains null if the SQL index has never been built. |
LAST_QUERY_USE | LASTQRYUSE | TIMESTAMP Nullable
|
The timestamp of the last time the SQL index was used in a query since the last time the usage statistics were reset. If the SQL 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 SQL index was used by the optimizer for statistics since the last time the usage statistics were reset. If the SQL 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 SQL index was used in a query since the last time the usage statistics were reset. If the SQL 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 SQL index was used by the optimizer for statistics since the last time the usage statistics were reset. If the SQL 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 SQL index was used directly by an application for native record I/O or SQL operations. If the SQL index has never been used, contains null. |
DAYS_USED_COUNT | DAYSUSED | INTEGER | The number of days the SQL 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 SQL 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 SQL index. For more information see the Change Object Description (CHGOBJD) command. If the SQL index's last used timestamp has never been reset, contains null. |
NUMBER_KEY_COLUMNS | INDKEYS | BIGINT | Number of columns that define the SQL index key. |
COLUMN_NAMES | COLNAMES | VARCHAR(1024) | A comma separated list of column names that define the SQL index key. If the length of all the column names exceeds 1024, '...' is returned at the end of the column value. |
NUMBER_KEYS | NUMRIDS | BIGINT | Number of keys in the SQL index. If the SQL index is invalid, -1 is returned. |
INDEX_SIZE | SIZE | BIGINT | Size (in bytes) of the data space index used by the SQL index. |
NUMBER_PAGES | PAGES | BIGINT Nullable
|
Number of pages in the SQL index. The page unit is the LOGICAL_PAGE_SIZE of the index. If the SQL index is invalid or is an encoded vector index, contains null. |
LOGICAL_PAGE_SIZE | PAGE_SIZE | INTEGER Nullable
|
The logical page size of the index. If the index is an encoded vector index, contains null. |
UNIQUE | UNIQUE | VARCHAR(21) | Indicates whether an SQL index is
unique:
|
MAXIMUM_KEY_LENGTH | KEY_LENGTH | INTEGER Nullable
|
Maximum key length of an SQL index. If the SQL index is an encoded vector index, contains null. |
UNIQUE_PARTIAL_KEY_VALUES | KEYCARDS | VARCHAR(96) Nullable
|
The unique partial key values for the SQL index. If the index is an encoded vector index, the first unique partial key value is the total number of unique values for the entire index key. The remaining unique partial key values returned are not applicable. |
OVERFLOW_VALUES | OVERFLOW | INTEGER Nullable
|
The number of distinct key values that have overflowed the encoded vector index. If the SQL 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 SQL index is not an encoded vector index, contains null. |
SPARSE | SPARSE | VARCHAR(3) | Indicates whether the SQL index contains
keys for all the rows of its depended on table:
|
DERIVED_KEY | DERIVED | VARCHAR(3) | Indicates whether the any key columns
in the SQL index are expressions:
|
PARTITIONED | PARTITION | VARCHAR(3) | Indicates whether the SQL index is
partitioned or not partitioned:
Contains the null value if the base table is not a partitioned table. |
ACCPTH_TYPE | ACCPTHTYPE | VARCHAR(4) | Indicates the type of SQL index:
|
SORT_SEQUENCE | SRTSEQ | VARCHAR(12) | Indicates whether the SQL index uses
a collating sequence:
|
LANGUAGE_IDENTIFIER | LANGID | CHAR(3) Nullable
|
The language ID of the SQL index. Contains null if the collating sequence is hex. |
SORT_SEQUENCE_SCHEMA | SRTSEQSCH | CHAR(10) Nullable
|
Schema name of the sort sequence to use. Contains null if there is no sort sequence schema name. |
SORT_SEQUENCE_NAME | SRTSEQNAM | CHAR(10) Nullable
|
Name of the sort sequence to use. Contains null if there is no sort sequence name. |
ESTIMATED_BUILD_TIME | ESTBLDTIME | INTEGER | Estimated time (in seconds) required to rebuild the SQL 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:
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. |
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]()
![]() |
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]() When
LAST_INVALIDATION_REASON_CODE is 1, the following reason types can be returned:
When LAST_INVALIDATION_REASON_CODE is 3, the following reason types can be returned:
When LAST_INVALIDATION_REASON_CODE is 5, the following reason type can be returned:
If the index has never been invalidated, contains null.
![]() |
INDEX_HELD | HELD | VARCHAR(3) | An indication of whether a pending
rebuild of the SQL index is currently held by the user:
|
MAINTENANCE | MAINT | VARCHAR(11) Nullable
|
The maintenance of the SQL index:
|
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 SQL index is not a delayed maintenance index, contains null. |
RECOVERY | RECOVERY | VARCHAR(10) Nullable
|
The recovery attribute of the SQL
index:
|
ROUNDING_MODE | DECFLTRND | VARCHAR(8) Nullable
|
Indicates the DECFLOAT rounding mode of the
index:
Contains the null value if the index does not have an expression that references a DECFLOAT column, function, or constant. |
DECFLOAT_WARNING | DECFLTWRN | VARCHAR(3) Nullable
|
Indicates whether DECFLOAT warnings
are returned:
Contains the null value if the index does not have an expression that references a DECFLOAT column, function, or constant. |
LOGICAL_READS | LGLREADS | BIGINT | Number of logical read operations for the SQL index since the last IPL. |
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 Nullable
|
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. Contains null if the index is not sparse. |
SEARCH_CONDITION_HAS_UDF | IXWHEREUDF | VARCHAR(3) Nullable
|
If an index is sparse, indicates
whether the search condition of the index contains a user-defined
function. Contains null if the index is not sparse.
|
KEEP_IN_MEMORY | KEEPINMEM | VARCHAR(3) | Indicates whether the index should
be kept in memory:
|
MEDIA_PREFERENCE | MEDIAPREF | VARCHAR(3) | 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. |
OWNING_INDEX_SCHEMA | OWNINDSCH | VARCHAR(128) | Name of the schema of the object that owns the index. |
OWNING_INDEX_NAME | OWNINDNAME | VARCHAR(128) | Name of the object that owns the index. |
OWNING_INDEX_TYPE | OWNINDTYPE | VARCHAR(11) | The type of the object that owns
the index:
|
OWNING_INDEX_OWNER | OWNINDOWN | VARCHAR(128) | The owner of the object that owns the index. |
OWNING_SYSTEM_INDEX_SCHEMA | OWNSYS_IXD | CHAR(10) Nullable
|
System index schema name of the owner of the index. Contains null if the owner is a constraint. |
OWNING_SYSTEM_INDEX_NAME | OWNSYS_IXN | CHAR(10) Nullable
|
The system name of the owner of the index. Contains null if the owner is a constraint. |
OWNING_INDEX_TEXT | OWNLABEL | VARGRAPHIC(50) CCSID 1200 Nullable
|
Text of the object that owns the index. Contains null if text does not exist for the object. |
OWNING_INDEX_PARTITION | OWNINDMMBR | VARCHAR(128) Nullable
|
Partition or member name of the object that owns the index. Contains null if the owner is a constraint. |
SYSTEM_INDEX_SCHEMA | SYS_IXDNAM | CHAR(10) | System index schema name. |
SYSTEM_INDEX_NAME | SYS_IXNAME | CHAR(10) | System index name. |
SYSTEM_TABLE_SCHEMA | SYS_DNAME | CHAR(10) | System table schema name. |
SYSTEM_TABLE_NAME | SYS_TNAME | CHAR(10) | System table name. |
SYSTEM_TABLE_MEMBER | SYS_MNAME | CHAR(10) | System member name. |