SYSINDEXES

The SYSINDEXES view contains one row for every index in the SQL schema created using the SQL CREATE INDEX statement, including indexes on the SQL catalog.

The following table describes the columns in the SYSINDEXES view:

Table 1. SYSINDEXES view
Column Name System Column Name Data Type Description
INDEX_NAME NAME VARCHAR(128) Name of the index. This will be the SQL index name if one exists; otherwise, it will be the system index name.
INDEX_OWNER CREATOR VARCHAR(128) Owner of the index
TABLE_NAME TBNAME VARCHAR(128) Name of the table on which the index is defined. This will be the SQL table name if one exists; otherwise, it will be the system table name.
TABLE_OWNER TBCREATOR VARCHAR(128) Owner of the table
TABLE_SCHEMA TBDBNAME VARCHAR(128) Name of the SQL schema that contains the table on which the index is defined
IS_UNIQUE UNIQUERULE CHAR(1) If the index is unique:
D
No (duplicates are allowed)
V
Yes (duplicate NULL values are allowed)
U
Yes
E
Encoded vector index
COLUMN_COUNT COLCOUNT INTEGER Number of columns in the key
INDEX_SCHEMA DBNAME VARCHAR(128) Name of the SQL schema that contains the index
SYSTEM_INDEX_NAME SYS_IXNAME CHAR(10) System index name
SYSTEM_INDEX_SCHEMA SYS_IDNAME CHAR(10) System index schema name
SYSTEM_TABLE_NAME SYS_TNAME CHAR(10) System table name
SYSTEM_TABLE_SCHEMA SYS_DNAME CHAR(10) System table schema name
LONG_COMMENT REMARKS VARGRAPHIC(2000) CCSID 1200
Nullable
 A character string supplied with the COMMENT statement.

Contains the null value if there is no long comment.
IASP_NUMBER IASPNUMBER SMALLINT Specifies the independent auxiliary storage pool (IASP) number.
INDEX_TEXT LABEL VARGRAPHIC(50) CCSID 1200 A character string supplied with the LABEL statement.
IS_SPANNING_INDEX SPANNING VARCHAR(3)
Nullable
 Indicates whether the index is built over multiple partitions or members:
DISTRIBUTED
The index is built over a distributed table.
NO
The index is not built over multiple partitions or members.
YES
The index is built over multiple partitions or members.

Contains the null value if the base table is not a partitioned table.
INDEX_DEFINER DEFINER VARCHAR(128)
Nullable
 Name of the user that defined the index.
ROUNDING_MODE DECFLTRND CHAR(1)
Nullable
 Indicates the DECFLOAT rounding mode of the index:
C
ROUND_CEILING
D
ROUND_DOWN
F
ROUND_FLOOR
G
ROUND_HALF_DOWN
E
ROUND_HALF_EVEN
H
ROUND_HALF_UP
U
ROUND_UP

Contains the null value if the index does not have an expression that references a DECFLOAT column, function, or constant.
INDEX_HAS_SEARCH_CONDITION IXHASWHERE CHAR(1) If the index has a search condition:
N
The index does not have a search condition.
Y
The index has a search condition.
SEARCH_CONDITION_HAS_UDF IXWHEREUDF CHAR(1) If the index search condition contains a user-defined function:
N
The index is not sparse or does not contain a user-defined function.
Y
The index is sparse and the search condition contains a UDF.
SEARCH_CONDITION IXWHERECON DBCLOB(2M)
CCSID 1200
Nullable
 If the index is sparse, contains the search condition.

Contains the null value if the index is not sparse.
INDEX_HAS_INCLUDE_EXPRESSION IXHASINCEX CHAR(1) If the index contains an INCLUDE clause:
N
The index is does not contain an INCLUDE clause.
Y
The index contains an INCLUDE clause.
INCLUDE_EXPRESSION IXINCEXPR DBCLOB(2M)
CCSID 1200
Nullable
 If the index has an INCLUDE clause, contains the list of INCLUDE expressions.

Contains the null value if there is no include clause.
Start of changeCREATEDEnd of change Start of changeCREATEDEnd of change Start of changeTIMESTAMPEnd of change Start of changeThe timestamp when the SQL index was created.End of change