SYSINDEXES catalog table

The SYSINDEXES table contains one row for every index. The schema is SYSIBM.

Table 1. SYSIBM.SYSINDEXES table column descriptions
Column name	Data type	Description	Use
NAME	VARCHAR(128) NOT NULL	Name of the index.	G
CREATOR	VARCHAR(128) NOT NULL	The schema of the index.	G
TBNAME	VARCHAR(128) NOT NULL	Name of the table on which the index is defined.	G
TBCREATOR	VARCHAR(128) NOT NULL	The schema of the table.	G
UNIQUERULE	CHAR(1) NOT NULL	Whether the index is unique: C Yes, and it is used to enforce the uniqueness of a UNIQUE constraint or hash key columns. D No (duplicates are allowed) U Yes P Yes, and it is a primary index (As in prior releases of Db2, a value of P is used for primary keys that are used to enforce a referential constraint.) N Yes, and it is defined with UNIQUE WHERE NOT NULL R Yes, and it is an index used to enforce the uniqueness of a non-primary parent key G Yes, and it is an index used to enforce the uniqueness of values in a column defined as ROWID GENERATED BY DEFAULT X Yes, and it is an index used to enforce the uniqueness of values in a column that is used to identify or find XML values associated with a specific row.	G
COLCOUNT	SMALLINT NOT NULL	The number of columns in the key.	G
CLUSTERING	CHAR(1) NOT NULL	Whether CLUSTER was specified for the index: N No Y Yes	G
CLUSTERED	CHAR(1) NOT NULL	Whether the table is actually clustered by the index: N A significant number of rows are not in clustering order, or statistics have not been gathered. Y Most of the rows are in clustering order. blank Not applicable. This is an updatable column that can also be changed by the RUNSTATS utility. For a sparse index, the statistic is based on the actual contents of the index.	G
DBID	SMALLINT NOT NULL	Internal identifier of the database.	S
OBID	SMALLINT NOT NULL	Internal identifier of the index fan set descriptor.	S
ISOBID	SMALLINT NOT NULL	Internal identifier of the index page set descriptor.	S
DBNAME	VARCHAR(24) NOT NULL	Name of the database that contains the index.	G
INDEXSPACE	VARCHAR(24) NOT NULL	Name of the index space.	G
FIRSTKEYCARD	INTEGER NOT NULL	Db2 utilities might populate this column value, but its use is not recommended in DB2® version 5 and later. For best results use its replacement, the FIRSTKEYCARDF column.	N
FULLKEYCARD	INTEGER NOT NULL	Db2 utilities might populate this column value, but its use is not recommended in DB2 version 5 and later. For best results use its replacement, the FULLKEYCARDF column.	N
NLEAF	INTEGER NOT NULL	Number of active leaf pages in the index. The value is -1 if statistics have not been gathered. This is an updatable column.	S
NLEVELS	SMALLINT NOT NULL	Number of levels in the index tree. If the index is partitioned, it is the maximum of the number of levels in the index tree for all the partitions. The value is -1 if statistics have not been gathered. This is an updatable column.	S
BPOOL	CHAR(8) NOT NULL	Name of the buffer pool used for the index.	G
PGSIZE	SMALLINT NOT NULL	Contains the value 4, 8, 16, or 32 which indicates the size, in KB, of the leaf pages in the index. If the index was created prior to DB2 9, the value will be 4096 for a 4 KB page size.	G
ERASERULE	CHAR(1) NOT NULL	Whether the data sets are erased when dropped. The value is meaningless if the index is partitioned: N No Y Yes	G
DSETPASS	VARCHAR(24) NOT NULL	Not used	N
CLOSERULE	CHAR(1) NOT NULL	Whether the data sets are candidates for closure when the limit on the number of open data sets is reached: N No Y Yes	G
SPACE	INTEGER NOT NULL	Number of kilobytes of DASD storage allocated to the index, as determined by the last execution of the STOSPACE utility. The value is 0 if the index is not related to a storage group, or if STOSPACE has not been run. If the index space is partitioned, the value is the total kilobytes of DASD storage allocated to all partitions that are defined in a storage group.	G
IBMREQD	CHAR(1) NOT NULL	A value of Y indicates that the row was provided with the Db2 product code. For all other values, see Release dependency indicators. The value in this field is not a reliable indicator of release dependencies. RELCREATED should be used instead.	G
CLUSTERRATIO	SMALLINT NOT NULL WITH DEFAULT	Percentage of rows that are in clustering order. For a partitioning index, it is the weighted average of all index partitions in terms of the number of rows in the partition. The value is 0 if statistics have not been gathered. The value is -2 if the index is for an auxiliary table. This is an updatable column. For a sparse index, the statistic is based on the actual contents of the index.	S
CREATEDBY	VARCHAR(128) NOT NULL WITH DEFAULT	Primary authorization ID of the user who created the index.	G
IOFACTOR	SMALLINT NOT NULL	Internal use only.	I
PREFETCHFACTOR	SMALLINT NOT NULL	Not used	N
STATSTIME	TIMESTAMP NOT NULL WITH DEFAULT	If RUNSTATS or another utility with inline statistics updated the statistics, the date and time when the last utility invocation updated the statistics. The default value is '0001-01-01-00.00.00.000000'. The default value indicates that statistics were not collected. This column can be updated.	G
INDEXTYPE	CHAR(1) NOT NULL WITH DEFAULT	The index type: 2 Type 2 index or a hash overflow index on non-partitioned tables. blank Type 1 index D Data-partitioned secondary index P An index that is both partitioned and is a partitioning index (index that is on a table that uses table-controlled partitioning).	G
FIRSTKEYCARDF	FLOAT NOT NULL WITH DEFAULT -1	Number of distinct values of the first key column. This number is an estimate if updated while collecting statistics on a single partition. The value is -1 if statistics have not been gathered. This is an updatable column. For a sparse index, the statistic is based on the actual contents of the index.	S
FULLKEYCARDF	FLOAT NOT NULL WITH DEFAULT -1	Number of distinct values of the key. The value is -1 if statistics have not been gathered. This is an updatable column. For a sparse index, the statistic is based on the actual contents of the index.	S
CREATEDTS	TIMESTAMP NOT NULL WITH DEFAULT	Time when the CREATE statement was executed for the index. If the index was created in a Db2 release prior to Version 5, the value is '0001-01-01.00.00.00.000000'.	G
ALTEREDTS	TIMESTAMP NOT NULL WITH DEFAULT	Time when the most recent ALTER INDEX statement was executed for the index. If no ALTER INDEX statement has been applied, ALTEREDTS has the value of CREATEDTS. If the index was created in a Db2 release prior to Version 5, the value is '0001-01-01.00.00.00.000000'.	G
PIECESIZE	INTEGER NOT NULL WITH DEFAULT	Maximum size of a data set in KB for non-partitioned indexes. A value of 0 indicates a partitioned index or an index was created in an earlier release than DB2 version 5.	G
COPY	CHAR(1) NOT NULL WITH DEFAULT 'N'	Whether COPY YES was specified for the index, which indicates if the index can be copied and if SYSIBM.SYSLGRNX recording is enabled for the index. N No Y Yes	G
COPYLRSN	CHAR(10) NOT NULL WITH DEFAULT X'000000000000 00000000' FOR BIT DATA	The value can be either an RBA or LRSN. (LRSN is only for data sharing.) If the index is currently defined as COPY YES, the value is the RBA or LRSN when the index was created with COPY YES or altered to COPY YES, not the current RBA or LRSN. If the index is currently defined as COPY NO, the value is set to X'00000000000000000000' if the index was created with COPY NO; otherwise, if the index was altered to COPY NO, the value in COPYLRSN is not changed when the index is altered to COPY NO.	G
CLUSTERRATIOF	FLOAT NOT NULL WITH DEFAULT	When multiplied by 100, the value of the column is the percentage of rows that are in clustering order. For example, a value of '.9125' indicates 91.25%. For a partitioning index, it is the weighted average of all index partitions in terms of the number of rows in the partition. The value is 0 if statistics have not been gathered. The value is -2 if the index is for an auxiliary table, a node ID index or an XML index. This is an updatable column. For a sparse index, the statistic is based on the actual contents of the index.	G
SPACEF	FLOAT(8) NOT NULL WITH DEFAULT -1	Kilobytes of DASD storage. The value is -1 if statistics have not been gathered. This is an updatable column.	G
REMARKS	VARCHAR(762) NOT NULL WITH DEFAULT	A character field string provided by the user with the COMMENT statement.	G
PADDED	CHAR(1) NOT NULL WITH DEFAULT	Indicates whether keys within the index are padded for varying-length column data: Y The index contains varying-length character or graphic data and is PADDED (the varying-length columns are padded to their maximum length). N The index contains varying-length character or graphic data and is NOT PADDED (the varying-length columns are not padded to their maximum length). Index-only access to all column data is possible. blank The index does not contain varying-length character or graphic data, or the index was created or altered prior to DB2 version 8.	G
VERSION	SMALLINT NOT NULL WITH DEFAULT	The version of the data row format for this index. A value of zero indicates that a version-creating alter has never occurred against this index.	G
OLDEST_VERSION	SMALLINT NOT NULL WITH DEFAULT	The version number describing the oldest format of data in the index space and any image copies of the index.	G
CURRENT_VERSION	SMALLINT NOT NULL WITH DEFAULT	The version number describing the newest format of data in the index space. A zero indicates that the index space has never had been versioned. After the version number reaches the maximum value, the number will wrap back to one.	G
RELCREATED	CHAR(1) NOT NULL WITH DEFAULT	Release of Db2 that was used to create the object, blank for indexes created before Version 8. For all other values, see Release dependency indicators.	G
AVGKEYLEN	INTEGER NOT NULL WITH DEFAULT -1	Average length of keys within the index. The value is -1 if statistics have not been gathered. For a sparse index, the statistic is based on the actual contents of the index.	G
KEYTARGET_COUNT	SMALLINT NOT NULL WITH DEFAULT	The number of key-targets for an extended index. The value is 0 for a simple index.	G
UNIQUE_COUNT	SMALLINT NOT NULL WITH DEFAULT	The number of columns or key-targets that make up the unique constraint of an index, when other non-constraint enforcing columns or key-targets exist. Otherwise the value is 0.	G
IX_EXTENSION_TYPE	CHAR(1) NOT NULL WITH DEFAULT	Identifies the type of extended index: N Node ID index S Index on a scalar expression T Spatial index V XML index blank Simple index	G
COMPRESS	CHAR(1) NOT NULL WITH DEFAULT ' N'	Indicates whether index compression is active: N Index compression is not active Y Index compression is active	G
OWNER	VARCHAR(128) NOT NULL WITH DEFAULT	Authorization ID of the owner of the index. The value is an empty string for indexes created in a Db2 release prior to DB2 9.	G
OWNERTYPE	CHAR(1) NOT NULL WITH DEFAULT	Indicates the type of owner: blank Authorization ID L Role	G
DATAREPEATFACTORF	FLOAT NOT NULL WITH DEFAULT -1	The anticipated number of data pages that will be touched following an index key order. This number is -1 if statistics have not been collected. This is an updatable column. For a sparse index, the statistic is based on the actual contents of the index.	G
ENVID	INTEGER NOT NULL WITH DEFAULT	Internal environment identifier.	G
ROWID	ROWID NOT NULL GENERATED ALWAYS	ROWID column, created for the lob columns in this table.	G
HASH	CHAR(1) NOT NULL WITH DEFAULT N	Whether the index is the hash overflow index for a hash table. N No. N is the default. Y Yes	G
SPARSE	CHAR(1) NOT NULL WITH DEFAULT N	Whether the index is sparse or not. N No. N is the default. Every data row has an index entry. Y Yes. This index might not have an entry for each data row in the table. X Excluded. This index will not have an index entry when every data row for a key column contains the NULL value.	G
PARSETREE	BLOB(1G) NOT NULL WITH DEFAULT	Internal use only.	I
RTSECTION	BLOB(1G) NOT NULL WITH DEFAULT	Internal use only.	I
DSSIZE	INTEGER WITH DEFAULT NULL	The maximum size in KB of a partitioned index data set for a table space with relative page numbers. 0 for nonpartitioned indexes or partitioned indexes for table spaces with absolute page numbering. NULL for indexes that were created before Db2 12.	G
PAGENUM	CHAR(1) NOT NULL WITH DEFAULT 'A'	Format of page numbers for the index, indicating absolute or relative page numbering. A Indicates absolute addressing. The page number is an absolute number from the first page of the index. If the index is partitioned, the partition number is embedded in the page number. R Indicates relative addressing for partitioned indexes. The page number is relative to the start of the partition. NULL for objects that were created before Db2 12	G
PARTKEYCOLNUM	SMALLINT NOT NULL WITH DEFAULT	Reserved for future IBM® use.	I
STATUS	CHAR(1) NOT NULL WITH DEFAULT	Reserved for future IBM use.	I
INDEXSTATUS	VARCHAR(30) NOT NULL WITH DEFAULT	Reserved for future IBM use.	I
PARTITIONS	SMALLINT	Reserved for future IBM use.	I
PQTY	INTEGER WITH DEFAULT NULL	For user-managed data sets, the value is the primary space allocation in units of 4 KB storage blocks or -1. PQTY is based on a value of PRIQTY in the appropriate CREATE or ALTER TABLESPACE statement. Unlike PQTY, however, PRIQTY accepts space in 1 KB units. A value of -1 indicates that one of the following cases is true: PRIQTY was not specified for a CREATE TABLESPACE statement or for any subsequent ALTER TABLESPACE statements. -1 was the most recently specified value for PRIQTY, either on the CREATE TABLESPACE statement or a subsequent ALTER TABLESPACE statement. This column contains the null value when the value is unknown for objects created prior to Db2 12.
STORTYPE	CHAR(1) WITH DEFAULT NULL	Type of storage allocation: E Explicit (storage group not used) I Implicit (storage group used) This column is not used for rows representing catalog indexes. Catalog data sets are managed by Db2 This column contains the null value when the value is unknown for objects created prior to Db2 12.
STORNAME	VARCHAR(128) WITH DEFAULT NULL	Name of storage group used for space allocation. Blank if storage group not used. This column is not used for rows representing catalog indexes. Catalog data sets are managed by Db2 This column contains the null value when the value is unknown for objects created prior to Db2 12.
VCATNAME	VARCHAR(24) WITH DEFAULT NULL	Name of integrated catalog facility catalog used for space allocation. This column is not used for rows representing catalog indexes. Catalog data sets are managed by Db2 This column contains the null value when the value is unknown for objects created prior to Db2 12.
FREEPAGE	SMALLINT WITH DEFAULT NULL	Number of pages loaded before a page is left as free space. This column contains the null value when the value is unknown for objects created prior to Db2 12.
PCTFREE	SMALLINT WITH DEFAULT NULL	Percentage of each page left as free space. This column contains the null value when the value is unknown for objects created prior to Db2 12.
GBPCACHE	CHAR(1) WITH DEFAULT NULL	Group buffer pool cache option specified for this index or index partition. Blank Only changed pages are cached in the group buffer pool. A Changed and unchanged pages are cached in the group buffer pool. N No data is cached in the group buffer pool. This column contains the null value when the value is unknown for objects created prior to Db2 12.
SECQTYI	INTEGER WITH DEFAULT NULL	Secondary space allocation in units of 4 KB storage. For user-managed data sets, the value is the secondary space allocation in units of 4 KB blocks. This column contains the null value when the value is unknown for objects created prior to Db2 12.
ENFORCED_CONS	CHAR(1) NOT NULL WITH DEFAULT	Whether the index is an enforcing non-unique constraint. Blank The index does not enforce a non-unique constraint. F The index enforces a foreign key for a temporal referential constraint.
IMPLICIT	CHAR(1) NOT NULL WITH DEFAULT	Whether the index was implicitly created. Possible values are: Blank n/a N The index was explicitly created. Y The index was implicitly created.
REGENERATETS	TIMESTAMP(12) NOT NULL	The time when the object was regenerated. The value is valid only for objects that can be regenerated. If no regeneration has occurred, this column contains the same value as the CREATEDTS column.	G
FL 502 KEYLABEL	VARCHAR(192) NOT NULL WITH DEFAULT	The key label that is specified at the table level. Otherwise, the value is an empty string.