SYSPARTITIONINDEXES

The SYSPARTITIONINDEXES view contains one row for every index built over a table partition or table member. 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.

Use this view when you want to see index information for indexes built on a specified table or set of tables. The information is similar to that returned via Show Indexes in System i® Navigator.

The following table describes the columns in the SYSPARTITIONINDEXES view:

Table 1. SYSPARTITIONINDEXES 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.
INDEX_NAME INDNAME VARCHAR(128) Name of the index, logical file, or constraint. If the index type indicates one or more temporary indexes, INDEX_NAME contains the number of maintained temporary indexes that currently exist on the table followed by the string 'MAINTAINED TEMPORARY INDEXES'.
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 a 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.
TEMPORARY
Indicates one or more temporary indexes exist on the table.
INDEX_SCHEMA INDSCHEMA VARCHAR(128)

Nullable

Name of the SQL schema that contains the index, logical file, or constraint. Contains null if the row indicates one or more maintained temporary indexes.
INDEX_OWNER INDOWNER VARCHAR(128)

Nullable

Index owner. Contains null if the row indicates one or more maintained temporary indexes.
SYSTEM_INDEX_SCHEMA SYS_IXDNAM CHAR(10)

Nullable

System index schema name. Contains null unless the index type is INDEX or LOGICAL.
SYSTEM_INDEX_NAME SYS_IXNAME CHAR(10)

Nullable

System index name. Contains null unless the index type is INDEX or LOGICAL.
INDEX_TEXT LABEL VARGRAPHIC(50) CCSID 1200

Nullable

Text of the index, logical file, or constraint. Contains null if text does not exist for the index.
INDEX_PARTITION INDMEMBER VARCHAR(128)

Nullable

Partition or member name of the index. Contains null if the row indicates one or more maintained temporary indexes.
INDEX_VALID VALID VARCHAR(3) An indication or whether the index is invalid and needs to be rebuilt:
NO
The index is invalid.
YES
The index is valid.
CREATE_TIMESTAMP CREATED TIMESTAMP

Nullable

The timestamp when the index was created. Contains null if the row indicates one or more maintained temporary indexes.
LAST_BUILD_TIMESTAMP LASTBUILD TIMESTAMP

Nullable

The timestamp when the index was last rebuilt. Contains null if the row indicates one or more maintained temporary indexes.
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 or if the row indicates one or more maintained temporary indexes, 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 or if the row indicates one or more maintained temporary indexes, 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   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 or if the row indicates one or more maintained temporary indexes, 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.
NUMBER_KEY_COLUMNS INDKEYS BIGINT

Nullable

Number of columns that define the index key. Contains null if the row indicates one or more maintained temporary indexes.
COLUMN_NAMES COLNAMES VARCHAR(1024)

Nullable

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. Contains null if the row indicates one or more maintained temporary indexes.
NUMBER_KEYS NUMRIDS BIGINT

Nullable

Number of keys in the index. If the index is invalid or is an encoded vector index, -1 is returned. Contains null if the row indicates one or more maintained temporary indexes.
INDEX_SIZE SIZE BIGINT Size (in bytes) of the binary tree or encoded vector index of the index.
NUMBER_PAGES PAGES BIGINT

Nullable

Number of pages in the index. If the 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 or if the row indicates one or more maintained temporary indexes, contains null.
UNIQUE UNIQUE VARCHAR(21)

Nullable

Indicates whether an index is unique:
UNIQUE
The index is a UNIQUE index.
UNIQUE WHERE NOT NULL
The index is a UNIQUE WHERE NOT NULL index.
FIFO
The index is a non-unique first-in-first-out (FIFO) index.
LIFO
The index is a non-unique last-in-last-out (LIFO) index.
FCFO
The index is a non-unique first-change-first-out (FCFO) index.
Contains null if the row indicates one or more maintained temporary indexes.
MAXIMUM_KEY_LENGTH KEY_LENGTH INTEGER

Nullable

Maximum key length of an index. If the index is an encoded vector index, contains null.
UNIQUE_PARTIAL_KEY_VALUES KEYCARDS VARCHAR(96)

Nullable

Start of changeThe unique partial key values for the 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. If the index is one or more maintained temporary indexes, contains null.End of change
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.
SPARSE SPARSE VARCHAR(3)

Nullable

Indicates whether the 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 index is a select/omit logical file or an SQL index with a WHERE clause and does not contain keys for all the rows of its depended on table.
Contains null if the row indicates one or more maintained temporary indexes.
DERIVED_KEY DERIVED VARCHAR(3)

Nullable

Indicates whether the any key columns in the index are expressions:
NO
No key columns of the index are expressions.
YES
At least one key column is an expression.
Contains null if the row indicates one or more maintained temporary indexes.
PARTITIONED PARTITION VARCHAR(20)

Nullable

Indicates whether the index is partitioned or not partitioned:
NO
An SQL index is not partitioned (spans multiple partitions).
YES
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).
MULTI-MEMBER LOGICAL
The index is a logical file built over multiple partitions or members.
Contains null if the row indicates one or more maintained temporary indexes.
ACCPTH_TYPE ACCPTHTYPE VARCHAR(4)

Nullable

Indicates the type of index:
1 TB
The index is a maximum 1 terabyte (*MAX1TB) binary radix index.
4 GB
The index is a maximum 4 gigabyte (*MAX4GB) binary radix index.
EVI
The index is an encoded vector index.
Contains null if the row indicates one or more maintained temporary indexes.
SORT_SEQUENCE SRTSEQ Start of changeVARCHAR(12)

Nullable

End of change
Indicates whether the index uses a collating sequence:
BY HEX VALUE
The index does not use a collating table.
*LANGIDSHR
The index uses a shared weight sort sequence (SRTSEQ).
*LANGIDUNQ
The index uses a unique weight sort sequence (SRTSEQ).
Start of changeALTSEQEnd of change
The index uses an alternate collating sequence (ALTSEQ).
Contains null if the row indicates one or more maintained temporary indexes.
LANGUAGE_IDENTIFIER LANGID CHAR(3)

Nullable

The language ID of the index. Contains null if the sort sequence is hex or if the row indicates one or more maintained temporary indexes.
Start of changeSORT_SEQUENCE_SCHEMAEnd of change Start of changeSRTSEQSCHEnd of change Start of changeCHAR(10)

Nullable

End of change
Start of changeSchema name of the sort sequence to use. Contains null if there is no schema name.End of change
Start of changeSORT_SEQUENCE_NAMEEnd of change Start of changeSRTSEQNAMEnd of change Start of changeCHAR(10)

Nullable

End of change
Start of changeName of the sort sequence to use. Contains null if there is no sort sequence name.End of change
ESTIMATED_BUILD_TIME ESTBLDTIME INTEGER

Nullable

Start of changeEstimated time (in seconds) required to rebuild the index. Contains null if the row indicates one or more maintained temporary indexes.End of change
Start of changeLAST_BUILD_TIMEEnd of change Start of changeLSTBLDTIMEEnd of change Start of changeINTEGER

Nullable

End of change
Start of changeElapsed time (in seconds) the last time the index was built. Contains null if the last build information is not available.End of change
Start of changeLAST_BUILD_KEYSEnd of change Start of changeLSTBLDKEYSEnd of change Start of changeBIGINT

Nullable

End of change
Start of changeNumber of keys the last time the index was built. Contains null if the last build information is not available.End of change
Start of changeLAST_BUILD_DEGREEEnd of change Start of changeLSTBLDDEGEnd of change Start of changeSMALLINT

Nullable

End of change
Start of changeParallel degree the last time the index was built. Contains null if the last build information is not available.End of change
INDEX_HELD HELD VARCHAR(3) An indication or whether a pending rebuild of the index is currently held by the user:
NO
A rebuild of the index is not pending or is not held.
YES
A pending rebuild of the index is held.
MAINTENANCE MAINT VARCHAR(11)

Nullable

The maintenance of the index:
REBUILD
The index is not maintained and is rebuilt at open time.
DELAYED
The index maintenance is delayed until the index is opened.
DO NOT WAIT
The index is immediately maintained.
If the index is an encoded vector index or if the row indicates one or more maintained temporary indexes, 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 index is not a delayed maintenance index, contains null.
RECOVERY RECOVERY VARCHAR(10)

Nullable

The recovery attribute of the index:
DURING IPL
The index is recovered, if necessary, at IPL.
AFTER IPL
The index is recovered, if necessary, after IPL.
NEXT OPEN
The index is recovered, if necessary, on the next open.
If the index is an encoded vector index or if the row indicates one or more maintained temporary indexes, 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; or if the row indicates one or more maintained temporary indexes.

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; or if the row indicates one or more maintained temporary indexes.

LOGICAL_READS LGLREADS BIGINT

Nullable

Number of logical read operations for the index since the last IPL. Contains null if the row indicates one or more maintained temporary indexes.
Start of changeSEQUENTIAL_READSEnd of change Start of changeSEQREADSEnd of change Start of changeBIGINTEnd of change Start of changeNumber of sequential read operations for the index since the last IPL.End of change
Start of changeRANDOM_READSEnd of change Start of changeRANREADSEnd of change Start of changeBIGINTEnd of change Start of changeNumber of random read operations for the index since the last IPL.End of change
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.
Start of changeKEEP_IN_MEMORYEnd of change Start of changeKEEPINMEMEnd of change Start of changeVARCHAR(3)End of change Start of changeIndicates whether the index should be kept in memory:
NO
No memory preference.
YES
The index should be kept in memory, if possible.
End of change
Start of changeMEDIA_PREFERENCEEnd of change Start of changeMEDIAPREFEnd of change Start of changeVARCHAR(3)End of change Start of changeIndicates the media preference of the index:
ANY
No media preference.
SSD
The index should be allocated on Solid State Disk (SSD), if possible.
End of change
Start of changeINCLUDE_EXPRESSIONEnd of change Start of changeIXINCEXPREnd of change Start of changeVARGRAPHIC(1024) CCSID 1200

Nullable

End of change
Start of changeIndex INCLUDE expression. Contains null if the index does not have an INCLUDE expression.End of change
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.