System-period temporal tables in DB2

System-period temporal: the idea

System time (also called transaction time in academic papers) records when the database knew a fact. It is the audit clock, not the business effective date. If a clerk corrects a coverage amount today, system time records that the correction happened today, even if the policy was meant to apply last January. Business time (the next page) records that January effective window. System-period support is what you use for “show me the row as stored at 2011-02-28-09.10.12.”

Periods in Db2 are inclusive-exclusive: the begin timestamp is inside the period; the end timestamp is not. A history row with SYS_END equal to your AS OF value is not current at that instant; the next version, whose SYS_START equals that timestamp, is.

Defining a system-period temporal table

You need three generated timestamp columns and a SYSTEM_TIME period, then a history table with the same structure, then versioning. TIMESTAMP(12) (optionally WITH TIME ZONE) is required for the row-begin and row-end columns. IMPLICITLY HIDDEN keeps SELECT * from surprising old programs with extra columns.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE HR.POLICY (
  ID              INT NOT NULL PRIMARY KEY,
  VIN             VARCHAR(10),
  ANNUAL_MILEAGE  INT,
  RENTAL_CAR      CHAR(1),
  COVERAGE_AMT    INT,
  SYS_START       TIMESTAMP(12) NOT NULL
                    GENERATED ALWAYS AS ROW BEGIN,
  SYS_END         TIMESTAMP(12) NOT NULL
                    GENERATED ALWAYS AS ROW END,
  TRANS_ID        TIMESTAMP(12)
                    GENERATED ALWAYS AS TRANSACTION START ID
                    IMPLICITLY HIDDEN,
  PERIOD SYSTEM_TIME (SYS_START, SYS_END)
);
 
CREATE TABLE HR.POLICY_HISTORY LIKE HR.POLICY;
 
ALTER TABLE HR.POLICY
  ADD VERSIONING USE HISTORY TABLE HR.POLICY_HISTORY;

You can convert an existing table with ALTER TABLE ADD COLUMN for the three timestamps, ADD PERIOD SYSTEM_TIME, CREATE TABLE ... LIKE for history, then ADD VERSIONING. After versioning, existing applications that INSERT, UPDATE, and DELETE the business columns keep working; they simply start generating history.

SYSTEM_TIME

PERIOD SYSTEM_TIME (begin-column, end-column) names the pair Db2 uses as the system period. You do not UPDATE those columns yourself when they are GENERATED ALWAYS. Db2 rejects attempts to put an end timestamp that is not greater than begin. The period name in queries is SYSTEM_TIME, not the column names.

Historical rows

The current table holds versions that are still in effect (row-end at the timestamp maximum). The history table holds versions that have been replaced or deleted. On UPDATE, Db2 copies the old current row to history and sets that copy’s row-end to the transaction time; the surviving current row gets a new row-begin. On DELETE, the current row moves to history with row-end set to the delete time; it is no longer in the current table. INSERT of a brand-new key does not write history; there is no old version yet.

Do not load the history table with user SQL as if it were a scratch pad. Utilities and carefully controlled repairs exist, but the supported path is: change the current table and let versioning populate history.

Temporal inserts

INSERT looks like a normal insert. Omit ROW BEGIN, ROW END, and TRANSACTION START ID. Db2 stamps SYS_START to the transaction start time and SYS_END to the “until changed” maximum. History stays empty for that key until a later UPDATE or DELETE.

1
2
INSERT INTO HR.POLICY (ID, VIN, ANNUAL_MILEAGE, RENTAL_CAR, COVERAGE_AMT)
VALUES (1111, 'A1111', 10000, 'Y', 500000);

Temporal updates

A searched UPDATE of a current row versions the old image automatically. You do not mention the history table.

1
2
3
UPDATE HR.POLICY
   SET COVERAGE_AMT = 750000
 WHERE ID = 1111;

After this statement, the current table has coverage 750000 with a new SYS_START. The history table has coverage 500000 with SYS_END equal to the update’s timestamp. If CURRENT TEMPORAL SYSTEM_TIME is not null and the package is SYSTIMESENSITIVE(YES), Db2 adds implicit period predicates so the statement only sees rows as of that register — and you cannot mix an explicit FOR SYSTEM_TIME on the same statement. Keep that register null unless you intend time-travel DML.

Temporal deletes

DELETE of a current row does not throw the data away. Db2 inserts the old current row into history with SYS_END set to the delete time. A later FOR SYSTEM_TIME AS OF a timestamp before the delete still finds the row. A query without a period specification sees only current rows, so the deleted key is gone from “today’s” result.

1
2
DELETE FROM HR.POLICY
 WHERE ID = 1414;

Temporal queries

Without a period specification, FROM HR.POLICY means current rows only. History is invisible. That is why converting a table to system-period temporal is often transparent to existing SELECT * programs.

Place the period specification immediately after the table or view name, before the correlation name. The same period name must not appear twice for one table. AS OF TIMESTAMP is treated as FOR SYSTEM_TIME AS OF.

FOR SYSTEM_TIME AS OF

Use AS OF for a point-in-time reconstruction: a lawsuit, an audit, “what did the underwriter see?” Db2 reads current and history and returns the version whose period contains the timestamp.

1
2
3
4
SELECT ID, COVERAGE_AMT, SYS_START, SYS_END
  FROM HR.POLICY
  FOR SYSTEM_TIME AS OF TIMESTAMP('2011-02-28-09.10.12.649592000000')
 WHERE ID = 1111;

FOR SYSTEM_TIME FROM

FROM value1 TO value2 returns every version that overlaps that window. It is the right shape for “show all coverage amounts that existed at any time during 2011.” If value1 is greater than or equal to value2, no rows return. Inclusive start, exclusive end of the specified range.

1
2
3
4
5
SELECT ID, COVERAGE_AMT, SYS_START, SYS_END
  FROM HR.POLICY
  FOR SYSTEM_TIME FROM TIMESTAMP('2010-11-15')
                  TO TIMESTAMP('2012-02-01')
 WHERE ID = 1111;

FOR SYSTEM_TIME BETWEEN

BETWEEN value1 AND value2 also returns overlapping versions, with both query endpoints included in the overlap test (begin <= value2 and end > value1). People mix up FROM and BETWEEN; the difference is how the endpoints of the query window participate. When you need a closed range on both sides of the question, BETWEEN is the clause to reach for. When you want a half-open window that tiles cleanly next to another window, prefer FROM ... TO.

1
2
3
4
5
SELECT ID, COVERAGE_AMT
  FROM HR.POLICY
  FOR SYSTEM_TIME BETWEEN '2011-02-28-09.10.12.649592000000'
                  AND '9999-12-30-00.00.00.000000000000'
 WHERE ID = 1111;

FOR SYSTEM_TIME ALL

Some platforms (and the SQL:2011 discussion of system-versioned tables) allow FOR SYSTEM_TIME ALL to return every current and historical version with no time filter. Db2 for z/OS documents AS OF, FROM ... TO, and BETWEEN ... AND only. There is no ALL period-specification. To list every version, either:

• Use FROM a minimum timestamp TO a maximum timestamp so every stored period overlaps the window
• UNION ALL the current table and the history table (watch for duplicate column lists and authorization on both tables)

1
2
3
4
5
6
7
8
9
SELECT ID, COVERAGE_AMT, SYS_START, SYS_END
  FROM HR.POLICY
  FOR SYSTEM_TIME FROM TIMESTAMP('0001-01-01-00.00.00.000000000000')
                  TO TIMESTAMP('9999-12-30-00.00.00.000000000000')
 WHERE ID = 1111;
 
SELECT ID, COVERAGE_AMT, SYS_START, SYS_END FROM HR.POLICY
 UNION ALL
SELECT ID, COVERAGE_AMT, SYS_START, SYS_END FROM HR.POLICY_HISTORY;

Bitemporal queries

A bitemporal table has both SYSTEM_TIME and BUSINESS_TIME. You may specify both period names once each on the same table reference. System time asks “as the database knew it”; business time asks “as the policy was in force.” A claim on 2012-06-20 that must ignore later corrections uses FOR BUSINESS_TIME AS OF the accident date. A complaint on 2012-07-10 that must list every recorded change uses FOR SYSTEM_TIME FROM ... TO covering the complaint window. Combining both is how you answer “on 1 March, what did we think the June coverage would be?” The dedicated bitemporal page walks through corrections in detail.

1
2
3
4
5
SELECT VIN, RENTAL_CAR, COVERAGE_AMT
  FROM HR.POLICY
  FOR SYSTEM_TIME AS OF TIMESTAMP('2012-03-01')
  FOR BUSINESS_TIME AS OF DATE('2012-06-20')
 WHERE ID = 1111;

Special registers and bind options

CURRENT TEMPORAL SYSTEM_TIME, when not null, acts like an implicit FOR SYSTEM_TIME AS OF for system-period tables if SYSTIMESENSITIVE is YES. That lets old SQL pick up time-travel without text changes. It also blocks an explicit FOR SYSTEM_TIME on the same statement. Leave the register null for ordinary OLTP.

Explain It Like I'm Five

Imagine a classroom whiteboard and a box of old photos. The whiteboard is today’s table. Every time someone erases a word and writes a new one, a camera (Db2) takes a photo of the old whiteboard and drops it in the box (history) with a sticky note of when the erase happened. If a teacher asks “what was on the board at recess?”, you do not search the box by hand — you say FOR SYSTEM_TIME AS OF recess and Db2 picks the right photo. The photos are not the lesson plan (that is business time). They are proof of what was actually written.

Exercises

1. Create a small table with ROW BEGIN, ROW END, TRANSACTION START ID, a history table, and ADD VERSIONING. INSERT two keys, UPDATE one, DELETE the other. Query with no period clause, then AS OF a time between the insert and the update.
2. Explain why a history row whose SYS_END equals your AS OF timestamp is not returned.
3. Write equivalent FROM ... TO and BETWEEN ... AND queries for the same calendar day and compare the result sets.
4. Show a coworker why FOR SYSTEM_TIME ALL is not valid on z/OS and demonstrate a full-range FROM ... TO instead.
5. On a bitemporal sample (or on paper), write one query that uses only SYSTEM_TIME, one that uses only BUSINESS_TIME, and one that uses both.