SQL Reference

SYSCAT.INDEXES

Contains one row for each index that is defined for a table.

Table 50. SYSCAT.INDEXES Catalog View

Column Name Data Type Nullable Description
INDSCHEMA CHAR(8)
Name of the index.
INDNAME VARCHAR(18)
DEFINER CHAR(8)
User who created the index.
TABSCHEMA CHAR(8)
Qualified name of the table on which the index is defined.
TABNAME VARCHAR(18)
COLNAMES VARCHAR(320)
List of column names, each preceded by + or - to indicate ascending or descending order respectively.
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

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 INTEGER
Number of distinct first key values; -1 if statistics are not gathered.
FIRST2KEYCARD INTEGER
Number of distinct keys using the first two columns of the index (-1 if no statistics or inapplicable)
FIRST3KEYCARD INTEGER
Number of distinct keys using the first three columns of the index (-1 if no statistics or inapplicable)
FIRST4KEYCARD INTEGER
Number of distinct keys using the first four columns of the index (-1 if no statistics or inapplicable)
FULLKEYCARD INTEGER
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.
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.)
REMARKS VARCHAR(254) Yes User-supplied comment, or null.
TEXT CLOB(32K) Yes Reserved for future use.

Column Name	Data Type	Nullable	Description
INDSCHEMA	CHAR(8)		Name of the index.
INDNAME	VARCHAR(18)
DEFINER	CHAR(8)		User who created the index.
TABSCHEMA	CHAR(8)		Qualified name of the table on which the index is defined.
TABNAME	VARCHAR(18)
COLNAMES	VARCHAR(320)		List of column names, each preceded by + or - to indicate ascending or descending order respectively.
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
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	INTEGER		Number of distinct first key values; -1 if statistics are not gathered.
FIRST2KEYCARD	INTEGER		Number of distinct keys using the first two columns of the index (-1 if no statistics or inapplicable)
FIRST3KEYCARD	INTEGER		Number of distinct keys using the first three columns of the index (-1 if no statistics or inapplicable)
FIRST4KEYCARD	INTEGER		Number of distinct keys using the first four columns of the index (-1 if no statistics or inapplicable)
FULLKEYCARD	INTEGER		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.
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.)
REMARKS	VARCHAR(254)	Yes	User-supplied comment, or null.
TEXT	CLOB(32K)	Yes	Reserved for future use.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]