SQL Reference

SYSCAT.INDEXES

Contains one row for each index (including inherited indexes where applicable) that is defined for a table.

Table 67. SYSCAT.INDEXES Catalog View

Column Name Data Type Nullable Description
INDSCHEMA VARCHAR(128)
Name of the index.
INDNAME VARCHAR(18)
DEFINER VARCHAR(128)
User who created the index.
TABSCHEMA VARCHAR(128)
Qualified name of the table or nickname on which the index is defined.
TABNAME VARCHAR(128)
COLNAMES VARCHAR(640)
List of column names, each preceded by + or - to indicate ascending or descending order respectively. Warning: This column will be removed in the future. Use SYSCAT.INDEXCOLUSE for this information.
UNIQUERULE CHAR(1)
Unique rule:

D = Duplicates allowed
P = Primary index
U = Unique entries only allowed

MADE_UNIQUE CHAR(1)

Y = Index was originally non-unique but was converted to a unique index to support a unique or primary key constraint. If the constraint is dropped, the index will revert to non-unique.
N = Index remains as it was created.

COLCOUNT SMALLINT
Number of columns in the key plus the number of include columns if any.
UNIQUE_COLCOUNT SMALLINT
The number of columns required for a unique key. Always <=COLCOUNT. < COLCOUNT only if there a include columns. -1 if index has no unique key (permits duplicates)
INDEXTYPE CHAR(4)
Type of index.

CLUS = Clustering
REG = Regular

ENTRYTYPE CHAR(1)

H = An index on a hierarchy table (H-table)
L = Logical index on a typed table
blank if an index on an untyped table

PCTFREE SMALLINT
Percentage of each index leaf page to be reserved during initial building of the index. This space is available for future inserts after the index is built.
IID SMALLINT
Internal index ID.
NLEAF INTEGER
Number of leaf pages; -1 if statistics are not gathered.
NLEVELS SMALLINT
Number of index levels; -1 if statistics are not gathered.
FIRSTKEYCARD BIGINT
Number of distinct first key values; -1 if statistics are not gathered.
FIRST2KEYCARD BIGINT
Number of distinct keys using the first two columns of the index (-1 if no statistics or inapplicable)
FIRST3KEYCARD BIGINT
Number of distinct keys using the first three columns of the index (-1 if no statistics or inapplicable)
FIRST4KEYCARD BIGINT
Number of distinct keys using the first four columns of the index (-1 if no statistics or inapplicable)
FULLKEYCARD BIGINT
Number of distinct full key values; -1 if statistics are not gathered.
CLUSTERRATIO SMALLINT
Degree of data clustering with the index; -1 if statistics are not gathered or if detailed index statistics are gathered (in which case, CLUSTERFACTOR will be used instead).
CLUSTERFACTOR DOUBLE
Finer measurement of degree of clustering, or -1 if detailed index statistics have not been gathered or if the index is defined on a nickname.
SEQUENTIAL_PAGES INTEGER
Number of leaf pages located on disk in index key order with few or no large gaps between them. (-1 if no statistics are available.)
DENSITY INTEGER
Ratio of SEQUENTIAL_PAGES to number of pages in the range of pages occupied by the index, expressed as a percent (integer between 0 and 100, -1 if no statistics are available.)
USER_DEFINED SMALLINT
1 if this index was defined by a user and has not been dropped; otherwise 0.
SYSTEM_REQUIRED SMALLINT

1 if this index is required for primary key or unique key constraint, OR if this is the index on the object identifier (OID) column of a typed table.
2 if this index is required for primary key or unique key constraint, AND this is the index on the object identifier (OID) column of a typed table.
0 otherwise.

CREATE_TIME TIMESTAMP
Time when the index was created.
STATS_TIME TIMESTAMP Yes Last time when any change was made to recorded statistics for this index. Null if no statistics available.
PAGE_FETCH_PAIRS VARCHAR(254)
A list of pairs of integers, represented in character form. Each pair represents the number of pages in a hypothetical buffer, and the number of page fetches required to scan the table with this index using that hypothetical buffer. (Zero-length string if no data available.)
MINPCTUSED SMALLINT
If not zero, then on-line index reorganization is enabled and the value is the threshold of minimum used space before merging pages.
REVERSE_SCANS CHAR(1)

Y = Index supports reverse scans
N = Index does not support reverse scans

INTERNAL_FORMAT SMALLINT
Encodes the internal representation of the index.
REMARKS VARCHAR(254) Yes User-supplied comment, or null.

Column Name	Data Type	Nullable	Description
INDSCHEMA	VARCHAR(128)		Name of the index.
INDNAME	VARCHAR(18)
DEFINER	VARCHAR(128)		User who created the index.
TABSCHEMA	VARCHAR(128)		Qualified name of the table or nickname on which the index is defined.
TABNAME	VARCHAR(128)
COLNAMES	VARCHAR(640)		List of column names, each preceded by + or - to indicate ascending or descending order respectively. Warning: This column will be removed in the future. Use SYSCAT.INDEXCOLUSE for this information.
UNIQUERULE	CHAR(1)		Unique rule: D = Duplicates allowed P = Primary index U = Unique entries only allowed
MADE_UNIQUE	CHAR(1)		Y = Index was originally non-unique but was converted to a unique index to support a unique or primary key constraint. If the constraint is dropped, the index will revert to non-unique. N = Index remains as it was created.
COLCOUNT	SMALLINT		Number of columns in the key plus the number of include columns if any.
UNIQUE_COLCOUNT	SMALLINT		The number of columns required for a unique key. Always <=COLCOUNT. < COLCOUNT only if there a include columns. -1 if index has no unique key (permits duplicates)
INDEXTYPE	CHAR(4)		Type of index. CLUS = Clustering REG = Regular
ENTRYTYPE	CHAR(1)		H = An index on a hierarchy table (H-table) L = Logical index on a typed table blank if an index on an untyped table
PCTFREE	SMALLINT		Percentage of each index leaf page to be reserved during initial building of the index. This space is available for future inserts after the index is built.
IID	SMALLINT		Internal index ID.
NLEAF	INTEGER		Number of leaf pages; -1 if statistics are not gathered.
NLEVELS	SMALLINT		Number of index levels; -1 if statistics are not gathered.
FIRSTKEYCARD	BIGINT		Number of distinct first key values; -1 if statistics are not gathered.
FIRST2KEYCARD	BIGINT		Number of distinct keys using the first two columns of the index (-1 if no statistics or inapplicable)
FIRST3KEYCARD	BIGINT		Number of distinct keys using the first three columns of the index (-1 if no statistics or inapplicable)
FIRST4KEYCARD	BIGINT		Number of distinct keys using the first four columns of the index (-1 if no statistics or inapplicable)
FULLKEYCARD	BIGINT		Number of distinct full key values; -1 if statistics are not gathered.
CLUSTERRATIO	SMALLINT		Degree of data clustering with the index; -1 if statistics are not gathered or if detailed index statistics are gathered (in which case, CLUSTERFACTOR will be used instead).
CLUSTERFACTOR	DOUBLE		Finer measurement of degree of clustering, or -1 if detailed index statistics have not been gathered or if the index is defined on a nickname.
SEQUENTIAL_PAGES	INTEGER		Number of leaf pages located on disk in index key order with few or no large gaps between them. (-1 if no statistics are available.)
DENSITY	INTEGER		Ratio of SEQUENTIAL_PAGES to number of pages in the range of pages occupied by the index, expressed as a percent (integer between 0 and 100, -1 if no statistics are available.)
USER_DEFINED	SMALLINT		1 if this index was defined by a user and has not been dropped; otherwise 0.
SYSTEM_REQUIRED	SMALLINT		1 if this index is required for primary key or unique key constraint, OR if this is the index on the object identifier (OID) column of a typed table. 2 if this index is required for primary key or unique key constraint, AND this is the index on the object identifier (OID) column of a typed table. 0 otherwise.
CREATE_TIME	TIMESTAMP		Time when the index was created.
STATS_TIME	TIMESTAMP	Yes	Last time when any change was made to recorded statistics for this index. Null if no statistics available.
PAGE_FETCH_PAIRS	VARCHAR(254)		A list of pairs of integers, represented in character form. Each pair represents the number of pages in a hypothetical buffer, and the number of page fetches required to scan the table with this index using that hypothetical buffer. (Zero-length string if no data available.)
MINPCTUSED	SMALLINT		If not zero, then on-line index reorganization is enabled and the value is the threshold of minimum used space before merging pages.
REVERSE_SCANS	CHAR(1)		Y = Index supports reverse scans N = Index does not support reverse scans
INTERNAL_FORMAT	SMALLINT		Encodes the internal representation of the index.
REMARKS	VARCHAR(254)	Yes	User-supplied comment, or null.

[ Top of Page | Previous Page | Next Page ]