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:

Table 1. 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:
NO
The SQL index is invalid.
YES
The SQL index is valid.
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:
UNIQUE
The SQL index is a UNIQUE index.
UNIQUE WHERE NOT NULL
The SQL index is a UNIQUE WHERE NOT NULL index.
FIFO
The SQL index is a non-unique first-in-first-out (FIFO) index.
LIFO
The SQL index is a non-unique last-in-last-out (LIFO) index.
FCFO
The SQL index is a non-unique first-change-first-out (FCFO) index.
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:
NO
The index contains keys for all the rows of its depended on table.
YES
The SQL index includes a WHERE clause and does not contain 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:
NO
No key columns of the SQL index are expressions.
YES
At least one key column is an expression.
PARTITIONED PARTITION VARCHAR(3) Indicates whether the SQL index is partitioned or not partitioned:
DISTRIBUTED
The index is built over a distributed table.
NO
The SQL index is not partitioned (spans multiple partitions).
YES
The SQL index is not built over a partitioned table or built over a partitioned table and is partitioned (does not span multiple partitions).

Contains the null value if the base table is not a partitioned table.

ACCPTH_TYPE ACCPTHTYPE VARCHAR(4) Indicates the type of SQL index:
1 TB
The SQL index is a maximum 1 terabyte (*MAX1TB) binary radix index.
4 GB
The SQL index is a maximum 4 gigabyte (*MAX4GB) binary radix index.
EVI
The SQL index is an encoded vector index.
SORT_SEQUENCE SRTSEQ VARCHAR(12) Indicates whether the SQL index uses a collating sequence:
BY HEX VALUE
The SQL index does not use a collating table.
*LANGIDSHR
The SQL index uses a shared weight collating sequence (SRTSEQ).
*LANGIDUNQ
The SQL index uses a unique weight collating sequence (SRTSEQ).
ALTSEQ
The SQL index uses an alternate collating sequence (ALTSEQ).
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:
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.
1
User requested. LAST_INVALIDATION_REASON_TYPE contains additional information.
2
Create or build index
3
Load. LAST_INVALIDATION_REASON_TYPE contains additional information.
4
IPL
5
Runtime error. LAST_INVALIDATION_REASON_TYPE contains additional information.
6
Modify index
7
Journal failed to build the index
8
Marked index as fixable during runtime
9
Marked index as fixable during IPL
10
Change end of data
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.
When LAST_INVALIDATION_REASON_CODE is 1, the following reason types can be returned:
1
Reorganize
2
Copy
3
ALTER TABLE or CHGPF
4
Converting new member
5
Change to *FRCRBDAP
6
Change to *UNIQUE
160
Change to *REBLD
When LAST_INVALIDATION_REASON_CODE is 3, the following reason types can be returned:
769
Index was marked for invalidation but the system crashed before the invalidation occurred
770
Index was associated with the overlaid data space header during a load
773
Partial load
774
Delayed maintenance mismatch
775
Pad key mismatch
777
Logical page size mismatch
778
Index not restored. File might have been saved with ACCPTH(*NO) or index did not exist when file was saved.
779
Index not restored. File might have been saved with ACCPTH(*NO) or index did not exist when file was saved.
When LAST_INVALIDATION_REASON_CODE is 5, the following reason type can be returned:
1025
Index out of sync with file
If the index has never been invalidated, contains null.End of change
INDEX_HELD HELD VARCHAR(3) An indication of whether a pending rebuild of the SQL index is currently held by the user:
NO
A rebuild of the SQL index is not pending or is not held.
YES
A pending rebuild of the SQL index is held.
MAINTENANCE MAINT VARCHAR(11)
Nullable
The maintenance of the SQL index:
REBUILD
The SQL index is not maintained and is rebuilt at open time.
DELAYED
The SQL index maintenance is delayed until the index is opened.
DO NOT WAIT
The SQL index is immediately maintained.
If the SQL index is an encoded vector index, contains null.
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:
DURING IPL
The SQL index is recovered, if necessary, at IPL.
AFTER IPL
The SQL index is recovered, if necessary, after IPL.
NEXT OPEN
The SQL index is recovered, if necessary, on the next open.
If the SQL index is an encoded vector index, contains null.
ROUNDING_MODE DECFLTRND VARCHAR(8)
Nullable
Indicates the DECFLOAT rounding mode of the index:
CEILING
ROUND_CEILING
DOWN
ROUND_DOWN
FLOOR
ROUND_FLOOR
HALFDOWN
ROUND_HALF_DOWN
HALFEVEN
ROUND_HALF_EVEN
HALFUP
ROUND_HALF_UP
UP
ROUND_UP

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:
NO
DECFLOAT warnings are not returned.
YES
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.
NO
The index search condition does not contain a UDF.
YES
The index search condition contains a UDF.
KEEP_IN_MEMORY KEEPINMEM VARCHAR(3) Indicates whether the index should be kept in memory:
NO
No memory preference.
YES
The index should be kept in memory, if possible.
MEDIA_PREFERENCE MEDIAPREF VARCHAR(3) Indicates the media preference of the index:
ANY
No media preference.
SSD
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.
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:
INDEX
The owner is an SQL index.
LOGICAL
The owner is part of a logical file.
PHYSICAL
The owner is a part of a keyed physical file.
PRIMARY KEY
The owner is a primary key constraint.
UNIQUE
The owner is a unique constraint.
FOREIGN KEY
The owner is a foreign key constraint.
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.