SYSIBM.SYSINDEXES table

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

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:
Start of changeCEnd of change
Start of change Yes, and it is used to enforce the uniqueness of a UNIQUE constraint or hash key columns.End of change
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.		 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
 Not used N
 FULLKEYCARD
INTEGER
NOT NULL
 Not used 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 Version 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
 
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 came from the basic machine-readable material (MRM) tape. 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. S
CREATEDBY
VARCHAR(128)
NOT NULL WITH
DEFAULT
 Primary authorization ID of the user who created the index. G
 
SMALLINT
NOT NULL
 Internal use only I
 
SMALLINT
NOT NULL
 Not used N
STATSTIME
TIMESTAMP
NOT NULL WITH
DEFAULT

Start of changeIf RUNSTATS updated the statistics, the date and time when the last invocation of RUNSTATS updated the statistics. The default value is '0001-01-01-00.00.00.000000'. The default value indicates that statistics were not collected. This is an updatable column.End of change

 G
INDEXTYPE
CHAR(1)
NOT NULL WITH
DEFAULT
 The index type:
2
Start of changeType 2 index or a hash overflow index on non-partitioned tables.End of change
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. 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. 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 kilobytes for non-partitioned indexes.

A value of zero (0) indicates that the index is a partitioned index or that the index was created in a DB2 release prior to 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(6)
NOT NULL WITH
DEFAULT
X'000000000000'
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'000000000000' 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. 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. The value is blank for indexes that have been 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. G
 
CHAR(1)
NOT NULL
 Not used N
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

Start of changeThe 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.End of change

 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, empty string for indexes created in a DB2 release prior to Version 9. G
OWNERTYPE
CHAR(1)
NOT NULL WITH
DEFAULT
 Indicates the type of owner:
blank
Authorization ID
L
Role
 G
DATAREPEAT-
FACTORF
FLOAT
NOT NULL WITH
DEFAULT -1
 Start of changeThe 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.End of change This statistic is only collected when the STATCLUS subsystem parameter is set to ENHANCED. G
ENVID
INTEGER
NOT NULL WITH
DEFAULT
 Internal environment identifier. G
Start of changeHASHEnd of change Start of change
CHAR(1)
NOT NULL
WITH DEFAULT N
End of change		 Start of changeWhether the index is the hash overflow index for a hash table.
N
No. N is the default.
Y
Yes
End of change		 Start of changeGEnd of change
Start of changeSPARSEEnd of change Start of change
CHAR(1)
NOT NULL
WITH DEFAULT N
End of change		 Start of changeWhether 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.
End of change		 Start of changeGEnd of change
Start of changeROWIDEnd of change Start of change
ROWID
NOT NULL
GENERATED
ALWAYS
End of change		 Start of changeROWID column, created for the lob columns in this table.End of change Start of changeGEnd of change
Start of change End of change Start of change
BLOB(1G)
NOT NULL
WITH DEFAULT
End of change		 Start of changeInternal use onlyEnd of change Start of changeIEnd of change
Start of change End of change Start of change
BLOB(1G)
NOT NULL
WITH DEFAULT
End of change		 Start of changeInternal use onlyEnd of change Start of changeIEnd of change