SYSINDEXSTAT

The SYSINDEXSTAT view contains one row for every SQL index Start of change partition End of change .

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 binary tree or encoded vector index of the SQL index.
NUMBER_PAGES	PAGES	BIGINT Nullable	Number of pages in the SQL 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.
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.