SQL Reference

OBJCAT.INDEXES

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

Table 86. OBJCAT.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)
ORIGIN_TABSCHEMA CHAR(8)
Qualified name of the table in the table hierarchy where the index was introduced.
ORIGIN_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; -2 for an inherited index on a subtable.
NLEVELS SMALLINT
Number of index levels. -1 if statistics are not gathered; -2 for an inherited index on a subtable.
FIRSTKEYCARD INTEGER
Number of distinct first key values. -1 if statistics are not gathered; -2 for an inherited index on a subtable.
FIRST2KEYCARD INTEGER
Number of distinct keys using the first two columns of the index. -1 if no statistics or inapplicable; -2 for an inherited index on a subtable.
FIRST3KEYCARD INTEGER
Number of distinct keys using the first three columns of the index. -1 if no statistics or inapplicable; -2 for an inherited index on a subtable.
FIRST4KEYCARD INTEGER
Number of distinct keys using the first four columns of the index. -1 if no statistics or inapplicable; -2 for an inherited index on a subtable.
FULLKEYCARD INTEGER
Number of distinct full key values. -1 if statistics are not gathered; -2 for an inherited index on a subtable.
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); -2 for an inherited index on a subtable.
CLUSTERFACTOR DOUBLE
Finer measurement of degree of clustering. -1 if detailed index statistics have not been gathered; -2 for an inherited index on a subtable.
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; -2 for an inherited index on a subtable.
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; -2 for an inherited index on a subtable.
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, or for an inherited index on a subtable.
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, or for an inherited index on a subtable.)
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)
ORIGIN_TABSCHEMA	CHAR(8)		Qualified name of the table in the table hierarchy where the index was introduced.
ORIGIN_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; -2 for an inherited index on a subtable.
NLEVELS	SMALLINT		Number of index levels. -1 if statistics are not gathered; -2 for an inherited index on a subtable.
FIRSTKEYCARD	INTEGER		Number of distinct first key values. -1 if statistics are not gathered; -2 for an inherited index on a subtable.
FIRST2KEYCARD	INTEGER		Number of distinct keys using the first two columns of the index. -1 if no statistics or inapplicable; -2 for an inherited index on a subtable.
FIRST3KEYCARD	INTEGER		Number of distinct keys using the first three columns of the index. -1 if no statistics or inapplicable; -2 for an inherited index on a subtable.
FIRST4KEYCARD	INTEGER		Number of distinct keys using the first four columns of the index. -1 if no statistics or inapplicable; -2 for an inherited index on a subtable.
FULLKEYCARD	INTEGER		Number of distinct full key values. -1 if statistics are not gathered; -2 for an inherited index on a subtable.
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); -2 for an inherited index on a subtable.
CLUSTERFACTOR	DOUBLE		Finer measurement of degree of clustering. -1 if detailed index statistics have not been gathered; -2 for an inherited index on a subtable.
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; -2 for an inherited index on a subtable.
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; -2 for an inherited index on a subtable.
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, or for an inherited index on a subtable.
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, or for an inherited index on a subtable.)
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 ]