A tabela SYSINDEXSPACESTATS contém estatísticas em tempo real para espaços de índice.
O esquema é SYSIBM.
As linhas nesta tabela podem ser inseridas, atualizadas e excluídas.
Em ambientes de compartilhamento de dados, os valores em SYSIBM.SYSINDEXSPACESTATS podem ser negativos por curtos períodos de tempo para certas situações.
Tabela de histórico:
SYSIBM.SYSIXSPACESTATS_H é uma tabela de histórico para a tabela de catálogo SYSIBM.SYSINDEXSPACESTATS.
Ambas as tabelas contêm as mesmas colunas, com os mesmos tipos de dados.
O relacionamento temporal deve ser ativado antes que a tabela de histórico possa ser usada.
Linhas na tabela de histórico podem ser inseridas, atualizadas e excluídas com a devida autorização.
Para obter informações sobre como ativar o relacionamento temporal,
consulte Temporal versioning for Db2 catalog tables
The SYSINDEXSPACESTATS table contains real time statistics for index spaces.
| 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 |
| REORGAPPENDINSERT |
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 |
| REORGPSEUDODELETES |
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, or the time when the index space or partition was created. |
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, or since the object
was created.
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, 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 |
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(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 |
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.
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 last REORG was run, or since the object was created.
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 |
