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:
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:
|
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:
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:
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:
|
SEARCH_CONDITION_HAS_UDF | IXWHEREUDF | CHAR(1) | If the index search condition contains a user-defined
function:
|
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:
|
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. |
CREATED | CREATED | TIMESTAMP | The timestamp when the SQL index was created. |