SYSIBM.SYSINDEXSPACESTATS table
The SYSIBM.SYSINDEXSPACESTATS table contains real time statistics for index spaces.
Rows in this table can be inserted, updated, and deleted.
In data sharing
environments, the values in SYSIBM.SYSINDEXSPACESTATS can be negative for short periods of time for
certain situations.
| 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 | INTEGER
|
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 | SMALLINT
|
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 | INTEGER
|
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 | INTEGER
|
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 |
REORGAPPEND-
INSERT |
INTEGER
|
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 |
REORGPSEUDO-
DELETES |
INTEGER
|
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 previous pages for successive active leaf pages that occurred
since the last REORG, REBUILD INDEX, or LOAD REPLACE, or since the
object was created. The distance between leaf pages is optimal if the difference is 1 and considered near if the distance is 2-16. An index page is added during a page split and the distance between the predecessor and successor pages can lower this count if the distance between the two was near. The distance between the predecessor and new page increase the count if they are near. The distance between the new page and successor increment the count if they are near. If a leaf page is deleted the distance between the new predecessor and successor pages can increment this count if the distance between the two is near. The distance between the predecessor and the deleted page decrement the count if it was near. The distance between the successor and the deleted page decrement the count if it was near. A null value means that the value is unknown. A negative value is possible in some cases. |
G |
| REORGLEAFFAR | INTEGER
|
The net number of leaf pages located physically
far away from previous leaf pages for successive active leaf pages
that occurred since the last REORG, REBUILD INDEX, or LOAD REPLACE,
or since the object was created. The distance between leaf pages is optimal if the difference is 1 and considered far if the distance is greater than 16. An index page is added during a page split and the distance between the predecessor and successor pages can decrement this count if the distance between the two was far. The distance between the predecessor and new page increment the count if they are far. The distance between the new page and successor increment the count if they are far. If a leaf page is deleted the distance between the new predecessor and successor pages can increment this count if the distance between the two is far. The distance between the predecessor and the deleted page decrement the count if it was far. The distance between the successor and the deleted page decrement the count if it was far. A null value means that the value is unknown. |
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.
A null value means that RUNSTATS has never been run on the index space or partition, or that the timestamp of the last RUNSTATS is unknown. |
G |
| STATSINSERTS | INTEGER
|
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. A null value indicates that the number of inserted index entries is unknown. |
G |
| STATSDELETES | INTEGER
|
The number of index entries
that have been deleted since the last RUNSTATS on the index space or partition. 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 |
| COPYUPDATED PAGES | INTEGER
|
The number of distinct pages that have been updated since the last time that the
COPY utility was run. A null value indicates that the number of updated pages is unknown. |
G |
| COPYCHANGES | INTEGER
|
The number of insert, update, and delete operations 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(6)
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
|
The timestamp of the first update that occurs after
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 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. |
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.
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:
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.
|
G |
| |
BIGINT
|
Reserved for future IBM® use. |
R |