Whenever a row is updated or deleted, the “before image” of the affected row is inserted into the associated history table. This is accomplished via versioning. Timestamps are automatically generated to produce an exact history of all INSERT, UPDATE and DELETE operations that occurred against the base table. Queries with temporal references to the base table are automatically and transparently run against the history table where needed. System-period temporal tables are supported in all editions of DB2 10, including Database Partitioning Feature, PureScale and High Availability Disaster Recovery (HADR). The base or history table can be range-partitioned or Multidimensional Clustering (MDC) tables.
You can hide system-period columns so they don’t appear in SELECT * queries. Base and history tables must have the same columns. When versioning is enabled, ADD column operations are propagated to the history table. You can use the new “PERIODOVERRIDE” option of the LOAD utility to load existing timestamps into period columns. You can now use the new SYSCAT.PERIODS catalog view to query the characteristics of any temporal table defined in the database.
A new column, TEMPORALTYPE, was added to the view SYSCAT.TABLES to identify temporal tables. Table types are indicated by these codes:
• A=Application-period temporal table
• B=Bitemporal table
• S=System-period temporal table
• N=Not a temporal table.
Creating a System-Period Temporal Table
You produce a system-period table by creating a regular table with system start and system end time columns and then create a history table using the same Data Definition Language (DDL) structure or the LIKE option of the CREATE TABLE statement. Figure 1 contains the DDL used to create the ITEM system-period temporal table. After creating the base table, you create the history table (see Figure 2).
You can use the ALTER TABLE statement to enable the ITEM table as a system-period temporal table with the relationship of the ITEM_HISTORY table established.
The system_start and system_end columns are the default names DB2 uses for the start and end points. However, you can name these columns any valid column name and then use them in the PERIOD SYSTEM_TIME clause to identify the table as a temporal table. These columns use a TIMESTAMP(12) data type to provide the precision necessary to prevent duplicate rows. Time periods apply to system time and business time. A period indicates the starting and ending points of a time interval.
DB2 uses the TRANS_ID column to capture the time when execution started for a transaction that impacts the row. If multiple rows are inserted or updated within a single transaction, in one or multiple tables, then the values for the TRANS_ID column are the same for all these rows and are unique from values generated for this column by any other transaction. Period columns and transaction start-ID columns can be IMPLICITLY HIDDEN from applications.