SYSINDEXSPACESTATS catalog table

The SYSINDEXSPACESTATS table contains real time statistics for index spaces. The schema is SYSIBM.

Rows in this table can be inserted, updated, and deleted. However, the following columns cannot be updated: SYS_START, SYS_END, and TRANS_START.

In data sharing environments, the values in SYSIBM.SYSINDEXSPACESTATS can be negative for short periods of time for certain situations.

SYSIBM.SYSINDEXSPACESTATS has an associated history table, SYSIBM.SYSIXSPACESTATS_H, which provides temporal versioning of the catalog table. Both tables contain the same columns, with the same data types. The temporal relationship must be enabled before the history table can be used. Rows in the history table can be inserted, updated, and deleted with proper authorization. For information about enabling the temporal relationship, see Temporal versioning for Db2 catalog tables.

Column name	Data type	Description	Use
UPDATESTATSTIME	TIMESTAMP NOT NULL WITH DEFAULT	The timestamp that the row in the SYSINDEXSPACESTATS table is inserted or last updated.	G
NLEVELS	SMALLINT	The number of levels in the index tree. A null value indicates that the number of levels is unknown.	G
NPAGES	INTEGER	The number of pages in the index tree that contain only pseudo-deleted index entries. This is an updatable column.	G
NLEAF	INTEGER	The number of leaf pages in the index. This is an updatable column.	G
NACTIVE	INTEGER	The number of active pages in the index space or partition. This value is equivalent to the number of pre-formatted pages. A null value indicates that the number of active pages is unknown.	G
SPACE	BIGINT	The amount of space, in KB, that is allocated to the index space or partition. For multi-piece, linear page sets, this value is the amount of space in all data sets. A null value indicates the amount of space is unknown.	G
EXTENTS	INTEGER	The number of extents in the index space or partition. For multi-piece index spaces, this value is the number of extents for the last data sets. For a data set that is stripped across multiple volumes, the value is the number of logical extents. A null value indicates the number of extents is unknown.	G
LOADRLASTTIME	TIMESTAMP	The timestamp that the LOAD REPLACE utility was last run on the index space or partition. A null value indicates that the LOAD REPLACE utility has never been run on the index space or partition or that the timestamp is unknown.	G
REBUILDLASTTIME	TIMESTAMP	The timestamp that the REBUILD INDEX utility was last run on the index space or partition. A null value indicates that the timestamp that the REBUILD INDEX was last run is unknown.	G
REORGLASTTIME	TIMESTAMP	The timestamp when the REORG INDEX utility was last run on the index space or partition, or if the REORG INDEX utility has not been run, the time when the index space or partition was created. A null value indicates that the timestamp is unknown.	G
REORGINSERTS	BIGINT	The number of index entries that have been inserted into the index space or partition since the last time the REORG, REBUILD INDEX, or LOAD REPLACE utilities were run, or since the object was created. A null value indicates that the number of inserted index entries is unknown.	G
REORGDELETES	BIGINT	The number of index entries that have been deleted from the index space or partition since the last time the REORG, REBUILD INDEX, or LOAD REPLACE utilities were run, or since the object was created. A null value indicates that the number of deleted index entries is unknown.	G
REORGAPPENDINSERT	BIGINT	The number of index entries that have a key value that is greater than the maximum key value in the index or partition that have been inserted into the index space or partition since the last time the REORG, REBUILD INDEX, or LOAD REPLACE utilities were run, or since the object was created. A null value indicates that the number of inserted index entries is unknown.	G
REORGPSEUDODELETES	BIGINT	The number of pseudo-deleted index entries stored in the index space or partition. A pseudo-delete is a RID entry that has been marked as deleted. A null value indicates that the number of pseudo-deleted index entries is unknown.	G
REORGMASSDELETE	INTEGER	The number of mass deletes from a segmented or LOB table space, or the number of dropped tables from a segmented table space since the last time the REORG or LOAD REPLACE utilities were run, or since the object was created. A null value indicates that the number of mass deletes is unknown.	G
REORGLEAFNEAR	INTEGER	The net number of leaf pages located physically near (within 2–16 pages) previous pages for successive active leaf pages since the object was created. A null value means that the value is unknown. A negative value is possible in some cases. Two index leaf pages are considered near if the distance is within 2–16 pages. (The optimal distance is 1 page.) Conditions that can increase the value of this counter A leaf page is added during a page split and the distance between the predecessor page and new page, or between the new page and the successor page, is near. An index page is deleted and the distance between the new predecessor and successor pages is near Conditions that can decrease the value of this counter A leaf page is deleted the distance between the predecessor page and the deleted page, or between the successor page and the deleted page, was near. An index page is added during a page split and the distance between the original predecessor and successor pages was near.	G
REORGLEAFFAR	INTEGER	The net number of leaf pages located physically far away (more than 16 pages away) from previous leaf pages for successive active leaf pages since the object was created. A null value means that the value is unknown. Two index leaf pages are considered far apart if the distance is greater than 16 pages. Conditions that can increase the value of this counter A leaf page is added during a page split and the distance between the predecessor page and new page, or between the new page and successor page, is far. An index page is deleted the distance between the new predecessor and successor pages is far. Conditions that can decrease the value of this counter A leaf page is deleted and the distance between the predecessor page and the deleted page, or between the successor page and the deleted page, was far. An index page is added during a page split and the distance between the predecessor and successor pages was far.	G
REORGNUMLEVELS	INTEGER	The number of levels in the index tree that were added or removed since the last REORG, REBUILD INDEX, or LOAD REPLACE, or the object was created. A null value means that the number of added or deleted levels is unknown.	G
STATSLASTTIME	TIMESTAMP	The timestamp of the last time that the RUNSTATS utility is run on the index space or partition, or the time when the index space or partition was created.	G
STATSINSERTS	BIGINT	The number of index entries that have been inserted into the index space or partition since the last time that the RUNSTATS utility was run, or since the object was created. A null value indicates that the number of inserted index entries is unknown.	G
STATSDELETES	BIGINT	The number of index entries that have been deleted since the last RUNSTATS on the index space or partition, or since the object was created. A null value means that the number of deleted index entries is unknown.	G
STATSMASSDELETE	INTEGER	The number of times that the index or index space partition was mass deleted since the last RUNSTATS, or the object was created. A null value indicates that the number of mass deletes is unknown.	G
COPYLASTTIME	TIMESTAMP	The timestamp of the last full image copy on the index space or partition. A null value means that COPY has never been run on the index space or partition, or that the timestamp of the last full image copy is unknown.	G
COPYUPDATEDPAGES	INTEGER	If the COPY utility was run with a SHRLEVEL value other than CHANGE, this value is the number of distinct pages that have been updated since the last time that the COPY utility was run. If the COPY utility was run with SHRLEVEL CHANGE, this value is the total number of distinct pages that were updated during the time that the last COPY utility was run, and since the last time that the COPY utility was run. A null value indicates that the number of updated pages is unknown.	G
COPYCHANGES	BIGINT	If the COPY utility was run with a SHRLEVEL value other than CHANGE, this value is the number of insert, update, and delete operations since the last time that the COPY utility was run. If the COPY utility was run with SHRLEVEL CHANGE, this value is the total number of insert, update, and delete operations, or the number of rows loaded, during the time that the last COPY utility was run, and since the last time that the COPY utility was run. A null value indicates that the number of insert, update, and delete operations is unknown.	G
COPYUPDATELRSN	CHAR(10) FOR BIT DATA	The LRSN or RBA of the first update that occurs after the last time the COPY utility was run. A null value indicates that the LRSN or RBA is unknown.	G
COPYUPDATETIME	TIMESTAMP	If the COPY utility was run with a SHRLEVEL value other than CHANGE, this value is the timestamp of the first update that occurred after the last time that the COPY utility was run. If the COPY utility was run with SHRLEVEL CHANGE, this value is the timestamp of the first update that occurred during the time that the last COPY utility was run, or since the last time that the COPY utility was run. A null value indicates that the timestamp is unknown.	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.	G
DBID	SMALLINT NOT NULL	The internal identifier of the database.	G
ISOBID	SMALLINT NOT NULL	The internal identifier of the index space page set descriptor.	I
PSID	SMALLINT NOT NULL	The internal identifier of the table space page set descriptor for the table space that is associated with the index.	G
PARTITION	SMALLINT NOT NULL	The data set number within the index space. For partitioned index spaces, this value corresponds to the partition number for a single partition. For non-partitioned index spaces, this value is 0.	G
INSTANCE	SMALLINT NOT NULL WITH DEFAULT 1	Indicates if the object is associated with data set 1 or 2. This is an updatable column.	G
TOTALENTRIES	BIGINT	The number of entries, including duplicate entries, in the index space or partition. A null value indicates that the number of entries is unknown.	G
DBNAME	VARCHAR(24) NOT NULL	The name of the database.	G
NAME	VARCHAR(128) NOT NULL	The name of the index.	G
CREATOR	VARCHAR(128) NOT NULL	The schema of the index.	G
INDEXSPACE	VARCHAR(24) NOT NULL	The name of the index space.	G
LASTUSED	DATE	The date when the index was last used in an access path for a SELECT, FETCH, searched UPDATE, or searched DELETE statement, or was used to enforce referential integrity constraints. If this field value indicates that an index has not been used for an extended period of time, consider dropping the index. For a data-partitioned secondary index, this column is only updated for one partition, even though more than one partition is accessed. The default value is NULL.	G
REORGINDEXACCESS	BIGINT	The number of times since the object was created, or since the last REORG, REBUILD INDEX, or LOAD REPLACE, that the index was used in one of the following situations: In an access path for a SELECT, FETCH, searched UPDATE, or searched DELETE statement For enforcement of referential integrity constraints For hash overflow indexes, this value is the number of times that Db2 used the hash overflow index. Use this value with other recommendations to determine when to run REORG INDEX. For example, when the ratio of SYSTABLESPACESTATS.TOTALROWS to SYSINDEXSPACESTATS.TOTALENTRIES indicates that REORG INDEX needs to be run, but this value is very low, REORG INDEX might not yet be necessary. A null value indicates that the number of times the index was used is unknown.	G
DRIVETYPE	CHAR(3) NOT NULL WITH DEFAULT	The drive type on which the index or index partition data set is defined. HDD Hard Disk Drive SSD Solid State Drive For multi-volume data sets, the drive type is set to SSD if any volume is SSD. For multi-piece linear page sets, the drive type of the first data set is used.	G
—	BIGINT	Reserved for future IBM® use.	R
GETPAGES	BIGINT	The number of getpage requests for the index space since the object was created, or since the last REORG, LOAD RELACE, or REBUILD INDEX was run. The value wraps if it exceeds the largest possible BIGINT value, which is 9223372036854775807.	G
SYS_START	TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN	The row-begin column of the SYSTEM_TIME period, for system-period data versioning.	G
SYS_END	TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END	The row-end column of the SYSTEM_TIME period, for system-period data versioning.	G
TRANS_START	TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID	The transaction-start-ID column, for system-period data versioning.	G
REORGTOTALSPLITS	INTEGER	The number of index splits since last reorganization or rebuild.	G
REORGSPLITTIME	BIGINT	Aggregated elapsed time for all index splits since last reorganization or rebuild.	G
REORGEXCSPLITS	INTEGER	The number of abnormal index splits (such as elapsed times greater than 1 second) since last reorganization or rebuild.	G