SYSTABLESPACESTATS catalog table

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

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

Important: Use care when issuing SQL statements or using tools to update statistics values in catalog tables. If such updates introduce invalid data, unpredictable results can occur, including abends for RUNSTATS and other utilities. If such problems occur, you can run the RUNSTATS utility and collect statistics at the table space level to resolve the problems, in most cases.

In data sharing environments, the values in SYSIBM.SYSTABLESPACESTATS 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 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
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	INTEGER	If the COPY utility was run with a SHRLEVEL value other than CHANGE, this value is the number of insert, update, and delete operations, or the number of rows loaded, 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. 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	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. 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 timestamp when one of the following events occurred: The last utility operation affected the data in a table space or partition, and Db2 did not log the changes. Some utility operations that update this column are REORG with the DISCARD option, REORG with the REBALANCE option, or a REORG operation that moves data in a partition-by-growth table space across partitions. A REORG operation that reorders data within the same partition does not result in an update of this column. An SQL statement affected the data in a table space or partition. SQL statements that update this column are INSERT, UPDATE, DELETE, MERGE, and TRUNCATE. 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. 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 object was created, or since the last REORG or LOAD REPLACE was run. 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