Bitemporal tables in DB2

System time + business time

Use business time alone when you only care about validity windows and you are willing to let later UPDATEs rewrite the story. Use system time alone when you only need an audit of stored images. Use both when regulators, clients, or auditors ask “what did you tell us last year that the June rate would be?” after someone has already fixed June.

The two periods are independent columns. A row has a business interval and a system interval. History rows are old system-time versions; they still carry the business dates that applied to that stored image.

Defining a bitemporal table

Combine the DDL from the previous two pages: DATE or TIMESTAMP pair for BUSINESS_TIME, TIMESTAMP(12) ROW BEGIN / ROW END / TRANSACTION START ID for SYSTEM_TIME, unique key with BUSINESS_TIME WITHOUT OVERLAPS, history table LIKE the current table, then versioning.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE HR.POLICY (
  ID              INT NOT NULL,
  VIN             VARCHAR(10),
  ANNUAL_MILEAGE  INT,
  RENTAL_CAR      CHAR(1),
  COVERAGE_AMT    INT,
  BUS_START       DATE NOT NULL,
  BUS_END         DATE NOT NULL,
  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),
  PERIOD BUSINESS_TIME (BUS_START, BUS_END),
  PRIMARY KEY (ID, BUSINESS_TIME WITHOUT OVERLAPS)
);
 
CREATE TABLE HR.POLICY_HISTORY LIKE HR.POLICY;
 
ALTER TABLE HR.POLICY
  ADD VERSIONING USE HISTORY TABLE HR.POLICY_HISTORY;

WITHOUT OVERLAPS still applies to business time on the current table. History can contain older business slices that no longer sit in the current table because a later FOR PORTION OF UPDATE replaced them.

Inserts, updates, and deletes

Insert

Provide business columns and BUS_START / BUS_END. Omit system-period columns. Db2 stamps SYS_START / SYS_END. Overlapping business keys still fail. Example: on 2011-11-15 you create a policy that will be in force from 2012-01-01. Business begin is in the future; system begin is today. That is normal and useful (scheduled terms).

1
2
3
4
INSERT INTO HR.POLICY
       (ID, VIN, ANNUAL_MILEAGE, RENTAL_CAR, COVERAGE_AMT, BUS_START, BUS_END)
VALUES (1111, 'A1111', 10000, 'Y', 500000,
        DATE('2012-01-01'), DATE('9999-12-31'));

Update

FOR PORTION OF BUSINESS_TIME splits validity the same way as a pure application-period table. Additionally, each current row that changes is versioned into history with SYSTEM_TIME. After an UPDATE on 2012-03-01 that lowers coverage from 2012-06-01 onward, the current table might hold two business slices (Jan–Jun still 500000, Jun onward 250000) both with SYS_START of 2012-03-01, while history holds the original single slice (Jan–9999 at 500000) with SYS_END of 2012-03-01.

1
2
3
4
5
6
UPDATE HR.POLICY
FOR PORTION OF BUSINESS_TIME
    FROM DATE('2012-06-01') TO DATE('9999-12-31')
   SET COVERAGE_AMT = 250000,
       RENTAL_CAR = 'N'
 WHERE ID = 1111;

Delete

DELETE FOR PORTION OF BUSINESS_TIME removes a validity slice and versions the old current images to history. A full DELETE of a current row moves it to history for system time and leaves no current business versions for that key.

Bitemporal queries

Accident on 2012-06-20, after the March update: FOR BUSINESS_TIME AS OF '2012-06-20' returns rental_car N and coverage 250000 — the terms in force on the accident date according to current knowledge.

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

Client calls on 2012-07-10 asking for a full account of recorded changes. Use system time so history participates:

1
2
3
4
5
6
SELECT ID, VIN, RENTAL_CAR, COVERAGE_AMT,
       BUS_START, BUS_END, SYS_START, SYS_END
  FROM HR.POLICY
  FOR SYSTEM_TIME FROM TIMESTAMP('2010-07-10')
                  TO TIMESTAMP('2012-07-11')
 WHERE ID = 1111;

That result includes the current two slices and the history row that still shows the original Jan–9999 coverage. You can explain both when terms were in effect and when the file changed.

Combining both periods reconstructs a past belief about a business instant:

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

On 2012-02-01 the March split had not happened, so this query should still see 500000 and rental Y for a June 20 business date. After the correction, the same business date with no SYSTEM_TIME clause sees 250000. That difference is the point of bitemporal design.

CURRENT TEMPORAL SYSTEM_TIME and CURRENT TEMPORAL BUSINESS_TIME can apply implicit AS OF predicates when the matching bind options are YES. Setting both registers is how some shops time-travel a whole batch job without editing every statement. Do not set them in OLTP sessions that must see current data.

Bitemporal corrections

A correction is not “delete the truth.” It is a new system-time version whose business periods describe what should have been. Typical pattern:

• Discover that a rate was typed as 9.9% from January but should have been 6.9% from March.
• UPDATE FOR PORTION OF BUSINESS_TIME FROM March TO end SET rate = 6.9.
• Current table now shows the corrected slices. History still shows 9.9% across the old business window, with SYS_END = correction time.
• Customer statements that must follow the legal valid-time use FOR BUSINESS_TIME AS OF statement-date on current data. An auditor who must see the error uses FOR SYSTEM_TIME AS OF a date before the correction.

If you only had business time, the UPDATE would destroy the evidence of the 9.9% typing. If you only had system time, you could not model “this rate applies next summer” as first-class periods with WITHOUT OVERLAPS. Bitemporal tables give you both.

Be careful with generated columns, unique indexes, and referential constraints: FOR PORTION OF may insert extra current rows in one statement. Test the split against your keys. Also remember inclusive-exclusive ends so you do not leave a one-day hole or overlap when you “correct” a boundary.

Special registers and bind options together

SYSTIMESENSITIVE and BUSTIMESENSITIVE are independent bind options. You can time-travel only system time, only business time, or both. CURRENT TEMPORAL SYSTEM_TIME implicit AS OF applies to system-period tables (and bitemporal tables’ system axis). CURRENT TEMPORAL BUSINESS_TIME applies to the business axis. If either register is non-null and the matching SENSITIVE option is YES, an explicit period specification for that same period on the statement is rejected. Null both registers after a report so the next transaction in a reused thread does not inherit last month’s as-of date.

Logical transactions: SYSIBM.TEMPORAL_LOGICAL_TRANSACTION_TIME can supply the timestamp used for ROW BEGIN and TRANSACTION START ID when you need several SQL statements to share one system-time instant (a single “correction event”). If that global variable is null, Db2 uses a clock reading from the first data-change statement in the unit of work that needs a row-begin value. Set it deliberately in batch corrections; leave it null in OLTP.

Indexes usually include the business key plus BUS_START, and often the system-period begin column on the history table so time-travel queries do not scan every version. History table spaces can be partitioned on SYS_END or SYS_START for archive-like pruning of very old system-time versions if compliance allows, which is a different conversation from ENABLE ARCHIVE.

What a correction looks like end to end

Suppose Policy 1111 was entered on 2011-11-15 with coverage 500000 from 2012-01-01 onward. On 2012-03-01 someone intended to lower coverage only from June, but typed the wrong amount. On 2012-07-15 you correct June onward to 400000:

1
2
3
4
5
UPDATE HR.POLICY
FOR PORTION OF BUSINESS_TIME
    FROM DATE('2012-06-01') TO DATE('9999-12-31')
   SET COVERAGE_AMT = 400000
 WHERE ID = 1111;

Current table: January–June still 500000 (or 250000 if the March update already applied), June onward 400000, all with SYS_START of 2012-07-15 for the rows that changed. History: previous current images with SYS_END of 2012-07-15, still showing whatever coverage those images had, including the mistaken 250000. A claim for an accident on 2012-06-20 paid in July using current business time sees 400000. An internal review that must reconstruct the file as it stood on 2012-06-21 uses FOR SYSTEM_TIME AS OF that day and still sees 250000 for June. That is the correction pattern: business time tells the customer the right story going forward; system time tells the auditor what the file contained when the claim was first filed.

Insert-only corrections (adding a missing future slice) still version nothing into history until something existing is updated or deleted. If you INSERT a disjoint business window for a new year, WITHOUT OVERLAPS must still hold against current rows only; history may already contain overlapping business windows from older system-time versions, which is expected.

Migration notes

If you already have hand-built valid-from/valid-to columns, ADD PERIOD BUSINESS_TIME on those columns, then add ROW BEGIN/END/TRANSACTION START ID, a LIKE history table, and versioning. Drop homemade triggers that copied rows to a history table or you will double-write. If the old history table layout matches, you may be able to reuse it as the SYSTEM_TIME history after you declare the period — verify TIMESTAMP(12), NOT NULL, and column order. Existing applications keep running: they see current system-time rows and every business version unless they add FOR BUSINESS_TIME AS OF CURRENT DATE (or the register). Plan that predicate with the application team or “current policy” reports suddenly return one row per historical price.

Explain It Like I'm Five

Imagine a school calendar (business time) and a security camera (system time). The calendar says field day is Friday. On Wednesday someone tapes a new note that field day is Thursday. The calendar now shows Thursday. The camera still has Wednesday’s tape of the Friday note. Bitemporal Db2 is the calendar plus the camera. FOR BUSINESS_TIME asks the calendar. FOR SYSTEM_TIME asks the camera. Both together ask “on Wednesday, what did the calendar say about Friday?”

Exercises

1. Create a bitemporal POLICY, history, and versioning. INSERT a future-dated policy, then UPDATE FOR PORTION OF a later slice. Query history and current tables separately.
2. Write three SELECTs: business AS OF accident date; system FROM/TO around a complaint date; both periods together for “what we knew on 1 Feb about 20 Jun.”
3. Simulate a rate correction and show an auditor query that still returns the wrong rate using SYSTEM_TIME AS OF before the fix.
4. Explain why SELECT without period clauses can return multiple business rows per key even though history is hidden.
5. List two reasons ENABLE ARCHIVE is the wrong tool if you already need bitemporal periods on the same table.