Temporal versioning for Db2 statistics-related catalog tables
You can use the historical information in Db2 12 statistics-related catalog tables to analyze, predict, and help prevent specific conditions in a subsystem.
The historical information for real-time statistics catalog tables can be used to analyze the rate of change in size, organization, and activity for table space and index space partitions. This information can then be used to develop an automated response to conditions such as table spaces and index spaces running out of space or becoming too disorganized.
The following table lists the statistics-related catalog tables that have an associated history table for system-period temporal versioning.
Catalog table | History table |
---|---|
SYSINDEXSPACESTATS | SYSIBM.SYSIXSPACESTATS_H |
SYSTABLESPACESTATS | SYSIBM.SYSTABSPACESTATS_H |
Enabling the temporal relationship
The temporal relationship between a history table and its associated catalog table must be enabled before the history table can be used to record historical information. Each member Db2 externalizes rows whenever the interval specified by the STATSINT subsystem parameter is reached.
ALTER TABLE SYSIBM.SYSINDEXSPACESTATS
ADD VERSIONING
USE HISTORY TABLE SYSIBM.SYSIXSPACESTATS_H;
ALTER TABLE SYSIBM.SYSTABLESPACESTATS
ADD VERSIONING
USE HISTORY TABLE SYSIBM.SYSTABSPACESTATS_H;
If the temporal relationship between a catalog table and its associated history table later needs to be removed, you can issue the ALTER TABLE statement with the DROP VERSIONING clause on the catalog table.
Multiple inserts into a temporal history table for one insert into a real-time statistics table
In rare cases, statistics-related catalog history tables might contain multiple rows that correspond to a single insert into a real-time statistics table. This can happen when real-time statistics are externalized to one of the real-time statistics tables, and the real-time statistics table is unavailable.
When temporal versioning is enabled, and Db2 inserts a row into a real-time statistics table, Db2 also inserts a row into the corresponding temporal history table. If the insert operation for the real-time statistics table fails because the table is unavailable, Db2 does not delete the row from the temporal history table. Db2 makes additional attempts to insert the row into the real-time statistics table, and with the each attempt, Db2 inserts another row into the temporal history table. When the real-time statistics table becomes available, and the insert into the real-time statistics table is successful, Db2 writes another row into the temporal history table.
The following example shows the contents of the SYS_START and SYS_END columns in system-period temporal table SYSIBM.SYSTABSPACESTATS_H when two unsuccessful attempts and one successful attempt are made to insert rows into a real-time statistics table. All column values other than SYS_END are the same. The first row corresponds to the successful attempt to insert into SYSIBM.SYSTABLESPACESTATS, and the second and third rows correspond to the unsuccessful attempts.
SYS_START SYS_END
-------------------------- --------------------------
2020-05-13-13.03.42.279115 2020-05-13-13.39.04.139589
2020-05-13-13.03.42.279115 2020-05-13-13.21.57.603570
2020-05-13-13.03.42.279115 2020-05-13-13.13.14.991616