A tabela SYSTABLESPACESTATS contém estatísticas em tempo real para espaços de tabela.
O esquema é SYSIBM.
As linhas nesta tabela podem ser inseridas, atualizadas e excluídas.
Importante:
Tenha cuidado ao emitir instruções SQL ou usar ferramentas para atualizar os valores das estatísticas nas tabelas do catálogo.
Se essas atualizações introduzirem dados inválidos, resultados imprevisíveis podem ocorrer, incluindo abends para RUNSTATS e outros utilitários.
Se esses problemas ocorrerem, você pode executar o utilitário RUNSTATS e coletar estatísticas no nível do espaço de tabela para resolver os problemas, na maioria
dos casos.
Em ambientes de compartilhamento de dados, os valores em SYSIBM.SYSTABLESPACESTATS podem ser negativos por curtos períodos de tempo para certas situações.
Tabela de história:
SYSIBM.SYSTABSPACESTATS_H é uma tabela de histórico para versão temporal da tabela de catálogo SYSIBM.SYSTABLESPACESTATS.
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
| Column name |
Data type |
Description |
Use |
| UPDATESTATSTIME |
TIMESTAMP
NOT NULL
WITH DEFAULT |
The timestamp that the row in the SYSTABLESPACESTATS table is inserted or updated. |
G |
| NACTIVE |
INTEGER |
The number of active pages in the table space or partition. |
G |
| NPAGES |
INTEGER |
The number of distinct pages with active rows in the partition or table space.
This is an updatable column.This column can be used to calculate an estimate of the size of LOB data in a table space.
To produce an estimate, use the following formula:
value of NPAGES * page size = approximate size of LOB data |
G |
| EXTENTS |
SMALLINT |
The number of extents in the table space. For multi-piece table spaces, this value is the number of extents for the last data set.
For a data set that is striped 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 table space or partition.
A null value indicates that the LOAD REPLACE utility has never been run on the table space or partition or that the timestamp is unknown. |
G |
| REORGLASTTIME |
TIMESTAMP |
The timestamp the REORG utility was last run on the table space or partition, or when the REORG utility has not been run, the time when the table space or
partition was created.
A null value indicates that the timestamp is unknown. |
G |
| REORGINSERTS |
INTEGER |
The number of rows or LOBs that have been inserted into the table space or partition or loaded into the table space or partition using the LOAD utility specified
without the REPLACE option 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 inserted rows or LOBs is unknown.
If the value is 2147483647, the actual number of inserted rows or LOBs might be greater than this value. |
G |
| REORGDELETES |
INTEGER |
The number of rows or LOBs that have been deleted from the table space or partition 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 deleted rows or LOBs is unknown.
If the value is 2147483647, the actual number of deleted rows or LOBs might be greater than this value. |
G |
| REORGUPDATES |
INTEGER |
The number of rows that have been updated in the table space or partition 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 updated rows is unknown.
If the value is 2147483647, the actual number of updated rows or LOBs might be greater than this value. |
G |
| REORGUNCLUSTINS |
INTEGER |
The number of rows that were inserted that are not well-clustered with respect to the clustering index since the last REORG or LOAD REPLACE, or since the object
was created.
A record is well-clustered if the record is inserted into a page that is within 16 pages of the ideal candidate page.
The clustering index determines the ideal candidate page.
A null value indicates that the number of pages that are not well clustered is unknown.
For a table space that has the MEMBER CLUSTER attribute, the clustering index is not used to identify the ideal candidate page.
Therefore, this value is not updated. |
G |
| REORGDISORGLOB |
INTEGER |
The number of LOBs that were inserted that are not perfectly chunked since the last REORG or LOAD REPLACE, or since the object was created.
A LOB is perfectly chunked if the allocated pages are in the minimum number of chunks.
A null value indicates that the number of not perfectly chunked LOBs 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 |
| REORGNEARINDREF |
INTEGER |
The number of overflow rows that are created and relocated near the pointer record since the last time the REORG and LOAD REPLACE utilities were run, or since
the object was created.
For non-segmented table spaces, a page is near the present page if the two page numbers differ by 16 or less.
For segmented table spaces, a page is near the present page if the two page numbers differ by SEGSIZE*2 or less.
A null value indicates that the number of overflow rows that are near the pointer record is unknown. |
G |
| REORGFARINDREF |
INTEGER |
The number of overflow rows that are created and relocated far from the pointer record since the last time the REORG and LOAD REPLACE utilities were run, or
since the object was created.
For non-segmented table spaces, a page is far from the present page if the two page numbers differ by more than 16.
For segmented table spaces, a page is far from the present page if the two page numbers differ by at least (SEGSIZE*2)+1.
A null value indicates that the number of overflow rows that are near the pointer record is unknown. |
G |
| STATSLASTTIME |
TIMESTAMP |
The timestamp of the last time that the RUNSTATS utility is run on the table space or partition, or the time that table space or partition was created. |
G |
| STATSINSERTS |
INTEGER |
The number of rows or LOBs that have been inserted into the table space or partition or loaded into the table space or partition using the LOAD utility
specified without the REPLACE option 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 rows or LOBs is unknown.
If the value is 2147483647, the actual number of inserted rows or LOBs might be greater than this value. |
G |
| STATSDELETES |
INTEGER |
The number of rows or LOBs that have been deleted from the table 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 deleted rows or LOBs is unknown.
If the value is 2147483647, the actual number of deleted rows or LOBs might be greater than this value. |
G |
| STATSUPDATES |
INTEGER |
The number of rows that have been updated in the table 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 updated rows is unknown.
If the value is 2147483647, the actual number of updated rows or LOBs might be greater than this value. |
G |
| STATSMASSDELETE |
INTEGER |
The number of mass deletes from a segmented or LOB table space, or the number of tables that are dropped from a segmented table space, since the last time the
RUNSTATS utility was run, or since 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 or incremental image copy of the table space or partition.
A null value indicates that the COPY utility has never been run on the table space or partition.
A null value can also indicate that the timestamp of the last image copy is unknown. |
G |
| COPYUPGETDATEDPAGES |
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, or the number of rows loaded, since the last time that the COPY utility was run.
This value does not include operations that result in no change to the data, such as an update that sets the value of a column to its existing value.
A null value indicates that the number of insert, update, and delete operations or the number of rows loaded 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.
The value is 6 bytes of X'FF' if the RBA/LRSN exceeds the 6-byte limit. |
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.
This column is used to map a DBID to its statistics. |
G |
| PSID |
SMALLINT
NOT NULL |
The internal identifier of the table space page set descriptor.
This column is used to map a PSID to its statistics. |
G |
| PARTITION |
SMALLINT
NOT NULL |
The data set number within the table space.
This column is used to map a data set number in a table space to its statistics.
For partitioned table spaces, this value corresponds to the partition number for a single partition. For non-partitioned table spaces, this value is 0. |
G |
| INSTANCE |
SMALLINT
NOT NULL
WITH DEFAULT 1 |
Indicates if the object is associated with data set instance 1 or 2.
This is an updatable column. |
G |
| SPACE |
BIGINT |
The amount of space, in KB, that is allocated to the table 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 |
| TOTALROWS |
BIGINT |
The number of rows or LOBs that are in the table space or partition, calculated from the in-memory counters for inserts and deletes.
For XML, this column contains the number of physical rows in the table space or partition.
Each XML document might have more than one physical record in a table space or partition. |
G |
| DATASIZE |
BIGINT |
The total number of bytes that row data occupy.
For LOB table spaces this column is always 0.
This is an updatable column. |
G |
| UNCOMPRESSEDDATASIZE |
BIGINT |
This column is not used.
The value is always set to 0. |
G |
| DBNAME |
VARCHAR(24)
NOT NULL |
The name of the database.
This column is used to map a database to its statistics. |
G |
| NAME |
VARCHAR(24)
NOT NULL |
The name of the table space.
This column is used to map a table space to its statistics. |
G |
| REORGSCANACCESS |
BIGINT |
The number of times data is accessed for SELECT, FETCH, searched UPDATE, or searched DELETE since the last CREATE, LOAD REPLACE or REORG, or since the object
was created.
A null value indicates that the number of times data is accessed is unknown. |
G |
| REORGHASHACCESS |
BIGINT |
The number of times data is accessed using hash access for SELECT, FETCH, searched UPDATE, searched DELETE, or used to enforce referential integrity constraints
since the last CREATE, LOAD REPLACE or REORG, or since the object was created.
A null value indicates that the number of times data is accessed is unknown. |
G |
| HASHLASTUSED |
DATE |
The date when hash access was last used for SELECT, FETCH, searched UPDATE, searched DELETE, or used to enforce referential integrity constraints. |
G |
| REORGCLUSTERSENS |
BIGINT |
The number of times that data has been read by SQL statements that are sensitive to the clustering sequence of the data since the last REORG or LOAD REPLACE, or
since the object was created.
For a table space that has the MEMBER CLUSTER attribute, when records are inserted, the clustering index is not used.
Therefore, this value is not updated. |
G |
| DRIVETYPE |
CHAR(3)
NOT NULL
WITH DEFAULT |
The drive type on which the table space or table space 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. This is an updatable column. |
G |
| LPFACILITY |
CHAR(1) |
Whether the disk control unit has the high performance list prefetch facility.
- N
- No
- Y
- Yes
A NULL value indicates that it is unknown whether the disk control unit has the high performance list prefetch facility. This is an updatable column. |
G |
| |
BIGINT |
Reserved for future IBM® use. |
R |
| UPDATESIZE |
BIGINT |
The net number of bytes that were added or removed by UPDATE operations since the object was created, or since the last REORG or LOAD REPLACE operation.
Valid values can be positive or negative. |
G |
| LASTDATACHANGE |
TIMESTAMP |
The last time that this row was updated because data was modified in the table space or partition.
The timestamp reflects the time at which the real-time statistics table was updated, and not the time at which the data in the table space or partition was
modified.
Physical data changes such as reorganization of data are not reflected in this column.
This value can also be updated when an -ACCESS DATABASE command is issued with the MODE(STATS) keyword, but an object in UTRO or UTRW state prevents the
externalization of real-time statistics.
In such cases, Db2 preserves the in-memory statistics until next possible externalization cycle. |
G |
| GETPAGES |
BIGINT |
The number of getpage requests for the table space since the last REORG was run, or since the object was created. The value wraps if it exceeds the largest
possible BIGINT value, 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 |
