Start of change

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.

Start of changeFL 500End of change

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.

Start of changeThe following table lists the statistics-related catalog tables that have an associated history table for system-period temporal versioning.End of change

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.

Tip: It is best to develop and test an aggregation and purge strategy before enabling the temporal relationship.
To enable the temporal relationship for SYSIBM.SYSINDEXSPACESTATS and its associated history table, SYSIBM.SYSIXSPACESTATS_H, issue the following statement:
ALTER TABLE SYSIBM.SYSINDEXSPACESTATS
  ADD VERSIONING
  USE HISTORY TABLE SYSIBM.SYSIXSPACESTATS_H;
To enable the temporal relationship for SYSIBM.SYSTABLESPACESTATS and its associated history table, SYSIBM.SYSTABSPACESTATS_H, issue the following statement:
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
End of change