SYSINDEXES_HIST catalog table
The SYSINDEXES_HIST catalog table contains rows from SYSINDEXES. The schema is SYSIBM.
Rows are added or changed in this table when RUNSTATS collects history statistics. Rows in this table can also be inserted, updated, and deleted.
| 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 |
| CLUSTERING | CHAR(1)
NOT NULL |
Whether CLUSTER was specified when
the index was created:
|
G |
| NLEAF | INTEGER
NOT NULL WITH DEFAULT -1 |
Number of active leaf pages in the index. The value is -1 if statistics have not been gathered. | S |
| NLEVELS | SMALLINT
NOT NULL WITH DEFAULT -1 |
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. | S |
| STATSTIME | TIMESTAMP
NOT NULL |
|
G |
| FIRSTKEYCARDF | FLOAT(8)
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. | S |
| FULLKEYCARDF | FLOAT(8)
NOT NULL WITH DEFAULT -1 |
Number of distinct values of the key. The value is -1 if statistics have not been gathered. | S |
| CLUSTERRATIOF | FLOAT(8)
NOT NULL |
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. For a sparse index, the statistic is based on the actual contents of the index. |
G |
| SPACEF | FLOAT(8)
NOT NULL WITH DEFAULT -1 |
Number of kilobytes of DASD storage allocated to the index space partition. The value is -1 if statistics have not been gathered. | G |
| IBMREQD | CHAR(1)
NOT NULL WITH DEFAULT 'N' |
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. |
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 |
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 |
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.