SYSCAT.INDEXES catalog view

Each row represents an index. Indexes on typed tables are represented by two rows: one for the "logical index" on the typed table, and one for the "H-index" on the hierarchy table.

Table 1. SYSCAT.INDEXES Catalog View
Column Name Data Type Nullable Description
INDSCHEMA VARCHAR (128)   Schema name of the index.
INDNAME VARCHAR (128)   Unqualified name of the index.
OWNER VARCHAR (128)   Authorization ID of the owner of the index.
OWNERTYPE CHAR (1)  
  • S = The owner is the system
  • U = The owner is an individual user
TABSCHEMA VARCHAR (128)   Schema name of the table or nickname on which the index is defined.
TABNAME VARCHAR (128)   Unqualified name of the table or nickname on which the index is defined.
COLNAMES VARCHAR (640)   This column is no longer used and will be removed in the next release. Use SYSCAT.INDEXCOLUSE for this information.
UNIQUERULE CHAR (1)   Unique rule.
  • D = Permits duplicates
  • U = Unique
  • P = Implements primary key
MADE_UNIQUE CHAR (1)  
  • N = Index remains as it was created.
  • 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 reverts to being non-unique.
COLCOUNT SMALLINT   Number of columns in the key, plus the number of include columns, if any.
UNIQUE_COLCOUNT SMALLINT   Number of columns required for a unique key. It is always <= COLCOUNT, and < COLCOUNT only if there are include columns; -1 if the index has no unique key (that is, it permits duplicates).
INDEXTYPE5 CHAR (4)   Type of index.
  • BLOK = Block index
  • CLUS = Clustering index (controls the physical placement of newly inserted rows)
  • CPMA = Page map index for a column-organized table
  • DIM = Dimension block index
  • MDST = Modification state index
  • RCT = Key sequence index for a range-clustered table
  • REG = Regular index
  • TEXT = Text index
  • XPTH = XML path index
  • XRGN = XML region index
  • XVIL = Index over XML column (logical)
  • XVIP = Index over XML column (physical)
ENTRYTYPE CHAR (1)  
  • H = This row represents an index on a hierarchy table
  • L = This row represents a logical index on a typed table
  • Blank = This row represents an index on an untyped table
PCTFREE SMALLINT   Percentage of each index page to be reserved during the initial building of the index. This space is available for data insertions after the index has been built.
IID SMALLINT   Identifier for the index.
NLEAF BIGINT   Number of leaf pages; -1 if statistics are not collected.
NLEVELS SMALLINT   Number of index levels; -1 if statistics are not collected.
FIRSTKEYCARD BIGINT   Number of distinct first-key values; -1 if statistics are not collected.
FIRST2KEYCARD BIGINT   Number of distinct keys using the first two columns of the index; -1 if statistics are not collected, or if not applicable.
FIRST3KEYCARD BIGINT   Number of distinct keys using the first three columns of the index; -1 if statistics are not collected, or if not applicable.
FIRST4KEYCARD BIGINT   Number of distinct keys using the first four columns of the index; -1 if statistics are not collected, or if not applicable.
FULLKEYCARD BIGINT   Number of distinct full-key values; -1 if statistics are not collected.
CLUSTERRATIO3 SMALLINT   Degree of data clustering with the index; -1 if statistics are not collected or if detailed index statistics are collected (in which case, CLUSTERFACTOR will be used instead).
CLUSTERFACTOR3 DOUBLE   Finer measurement of the degree of clustering; -1 if statistics are not collected or if the index is defined on a nickname.
SEQUENTIAL_PAGES BIGINT   Number of leaf pages located on disk in index key order with few or no large gaps between them; -1 if statistics are not collected.
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 statistics are not collected.
USER_DEFINED SMALLINT   1 if this index was defined by a user and has not been dropped; 0 otherwise.
SYSTEM_REQUIRED SMALLINT  
  • 1 if one or the other of the following conditions is met:
    • This index is required for a primary or unique key constraint, or this index is a dimension block index or composite block index for a multidimensional clustering (MDC) table or an insert time clustering (ITC) table.
    • This is the index on the object identifier (OID) column of a typed table.
  • 2 if both of the following conditions are met:
    • This index is required for a primary or unique key constraint, or this index is a dimension block index or composite block index for an MDC table or an ITC table.
    • This is the index on the OID column of a typed table.
  • 0 otherwise.
CREATE_TIME TIMESTAMP   Time when the index was created.
STATS_TIME TIMESTAMP Y Last time that any change was made to the recorded statistics for this index. The null value if no statistics are available.
PAGE_FETCH_PAIRS3 VARCHAR (520)   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 is available.
MINPCTUSED SMALLINT   A non-zero integer value indicates that the index is enabled for online defragmentation, and represents the minimum percentage of used space on a page before a page merge can be attempted. A zero value indicates that no page merge is attempted.
REVERSE_SCANS CHAR (1)  
  • N = Index does not support reverse scans
  • Y = Index supports reverse scans
INTERNAL_FORMAT SMALLINT   Possible values are:
  • 1 = Index does not have backward pointers
  • 2 or greater = Index has backward pointers
  • 6 = Index is a composite block index
COMPRESSION CHAR (1)   Specifies whether index compression is activated
  • N = Not activated
  • Y = Activated
IESCHEMA VARCHAR (128) Y Schema name of the index extension. The null value for ordinary indexes.
IENAME VARCHAR (128) Y Unqualified name of the index extension. The null value for ordinary indexes.
IEARGUMENTS CLOB (64K) Y External information of the parameter specified when the index is created. The null value for ordinary indexes.
INDEX_OBJECTID INTEGER   Identifier for the index object.
NUMRIDS BIGINT   Total number of row identifiers (RIDs) or block identifiers (BIDs) in the index; -1 if not known.
NUMRIDS_DELETED BIGINT   Total number of row identifiers (or block identifiers) in the index that are marked deleted, excluding those identifiers on leaf pages on which all the identifiers are marked deleted.
NUM_EMPTY_LEAFS BIGINT   Total number of index leaf pages that have all of their row identifiers (or block identifiers) marked deleted.
AVERAGE_RANDOM_FETCH_ PAGES1,2 DOUBLE   Average number of random table pages between sequential page accesses when fetching using the index; -1 if not known.
AVERAGE_RANDOM_PAGES2 DOUBLE   Average number of random table pages between sequential page accesses; -1 if not known.
AVERAGE_SEQUENCE_GAP2 DOUBLE   Gap between index page sequences. Detected through a scan of index leaf pages, each gap represents the average number of index pages that must be randomly fetched between sequences of index pages; -1 if not known.
AVERAGE_SEQUENCE_FETCH_ GAP1,2 DOUBLE   Gap between table page sequences when fetching using the index. Detected through a scan of index leaf pages, each gap represents the average number of table pages that must be randomly fetched between sequences of table pages; -1 if not known.
AVERAGE_SEQUENCE_PAGES2 DOUBLE   Average number of index pages that are accessible in sequence (that is, the number of index pages that the prefetchers would detect as being in sequence); -1 if not known.
AVERAGE_SEQUENCE_FETCH_ PAGES1,2 DOUBLE   Average number of table pages that are accessible in sequence (that is, the number of table pages that the prefetchers would detect as being in sequence) when fetching using the index; -1 if not known.
TBSPACEID INTEGER   Identifier for the index table space.
LEVEL2PCTFREE SMALLINT   Percentage of each index level 2 page to be reserved during initial building of the index. This space is available for future inserts after the index has been built.
PAGESPLIT CHAR (1)   Index page split behavior.
  • H = High
  • L = Low
  • S = Symmetric
  • Blank = Not applicable
AVGPARTITION_ CLUSTERRATIO3 SMALLINT   Degree of data clustering within a single data partition. -1 if the table is not partitioned, if statistics are not collected, or if detailed statistics are collected (in which case AVGPARTITION_ CLUSTERFACTOR will be used instead).
AVGPARTITION_ CLUSTERFACTOR3 DOUBLE   Finer measurement of the degree of clustering within a single data partition. -1 if the table is not partitioned, if statistics are not collected, or if the index is defined on a nickname.
AVGPARTITION_PAGE_FETCH_ PAIRS3 VARCHAR (520)   A list of paired integers in character form. Each pair represents a potential buffer pool size and the corresponding page fetches required to access a single data partition from the table. Zero-length string if no data is available, or if the table is not partitioned.
PCTPAGESSAVED SMALLINT   Approximate percentage of pages saved in the index as a result of index compression. -1 if statistics are not collected.
DATAPARTITION_CLUSTERFACTOR DOUBLE   A statistic measuring the "clustering" of the index keys with regard to data partitions. It is a number between 0 and 1, with 1 representing perfect clustering and 0 representing no clustering.
INDCARD BIGINT   Cardinality of the index. This might be different from the cardinality of the table for indexes that do not have a one-to-one relationship between the table rows and the index entries.
AVGLEAFKEYSIZE INTEGER   Average index key size for keys on leaf pages in the index.
AVGNLEAFKEYSIZE INTEGER   Average index key size for keys on non-leaf pages in the index.
OS_PTR_SIZE INTEGER   Platform word size with which the index was created.
  • 32 = 32-bit
  • 64 = 64-bit
COLLECTSTATISTCS CHAR (1)   Specifies how statistics were collected at index creation time.
  • D = Collect detailed index statistics
  • S = Collect sampled detailed index statistics
  • Y = Collect basic index statistics
  • Blank = Do not collect index statistics
DEFINER4 VARCHAR (128)   Authorization ID of the owner of the index.
LASTUSED DATE   Date when the index was last used by any DML statement to perform a scan, or used to enforce referential integrity constraints. This column is not updated when the index is used on an HADR standby database, nor is it updated when rows are inserted into the table on which the index is defined. The default value is '0001-01-01'. This value is updated asynchronously not more than once within a 24 hour period and might not reflect usage within the last 15 minutes.
PERIODNAME VARCHAR(128) Y Name of the period used to define this index.
PERIODPOLICY CHAR (1)   If a period name was specified, the index uses this period policy.
  • N = Not applicable
  • O = Period overlaps not allowed
MADE_WITHOUTOVERLAPS CHAR (1)  
  • N = Index remains as it was created.
  • Y = Index was converted to enforce WITHOUT OVERLAPS on the application period to support a primary or unique constraint. If the constraint is dropped, the index reverts to the original state.
NULLKEYS CHAR (1)  
  • N = Keys that contain all null values are not indexed (not considering columns or expressions from the INCLUDE clause)
  • Y = Keys that contain all null values are indexed (not considering columns or expressions from the INCLUDE clause)
FUNC_PATH CLOB (2K) Y SQL path in effect when the index was defined with an expression in the key. The null value if the key does not include any expressions.
VIEWSCHEMA VARCHAR(128) Y Schema name of the statistical view associated with the index key, if the key includes at least one expression. The null value if there are no expressions in the key.
VIEWNAME VARCHAR(128) Y Unqualified name of the statistical view associated with the index key, if the key includes at least one expression. The null value if there are no expressions in the key.
ENVSTRINGUNITS VARCHAR (11)   Default string units when the object was created.
REMARKS VARCHAR (254) Y User-provided comments, or the null value.
Note:
  1. When using DMS table spaces, this statistic cannot be computed.
  2. Prefetch statistics are not gathered during a LOAD...STATISTICS USE PROFILE, or a CREATE INDEX...COLLECT STATISTICS operation, or when the database configuration parameter seqdetect is turned off.
  3. AVGPARTITION_CLUSTERRATIO, AVGPARTITION_CLUSTERFACTOR, and AVGPARTITION_PAGE_FETCH_PAIRS measure the degree of clustering within a single data partition (local clustering). CLUSTERRATIO, CLUSTERFACTOR, and PAGE_FETCH_PAIRS measure the degree of clustering in the entire table (global clustering). Global clustering and local clustering values can diverge significantly if the table partitioning key is not a prefix of the index key, or when the table partitioning key and the index key are logically independent of each other.
  4. The DEFINER column is included for backwards compatibility. See OWNER.
  5. The XPTH, XRGN, and XVIP indexes are not recognized by any application programming interface that returns index metadata.