Business-time temporal tables in DB2

Business-time versus system time

System time is the database clock. Business time is the contract calendar. Insurance coverage, product prices, interest rates, and assignment of an employee to a department are classic business-time problems. You need multiple rows per key, each with a validity window, and you need Db2 to stop two windows from covering the same day when that would be nonsense.

Periods are inclusive-exclusive. Begin is in; end is out. A row with BUS_END = 2011-01-01 is not valid on 2011-01-01. The next row, if any, starts that morning. Open ended facts often use 9999-12-31 as the end date.

PERIOD BUSINESS_TIME

Add two NOT NULL DATE or TIMESTAMP columns and name them in PERIOD BUSINESS_TIME (begin, end). Db2 creates an implicit constraint that begin is less than end. You can ALTER an existing table to add the columns and the period. There is no TRANSACTION START ID and no GENERATED ALWAYS requirement; these are ordinary columns you supply on INSERT and that Db2 rewrites when FOR PORTION OF splits a row.

1
2
3
4
5
6
7
8
9
10
11
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,
  PERIOD BUSINESS_TIME (BUS_START, BUS_END),
  PRIMARY KEY (ID, BUSINESS_TIME WITHOUT OVERLAPS)
);

BUSINESS_TIME WITHOUT OVERLAPS on a unique or primary key means: for any instant of business time, at most one row exists for that ID. Touching endpoints are allowed because the end is exclusive. Overlapping interiors are rejected. Without WITHOUT OVERLAPS you can still have a period, but Db2 will not stop two “current” prices for the same product on the same day.

Business-time inserts

INSERT must include the begin and end values (unless they have defaults, which is unusual for a period). Inserting a second row for the same key with an overlapping window fails the WITHOUT OVERLAPS unique constraint. To change coverage for a middle slice, do not INSERT a colliding row; use UPDATE FOR PORTION OF instead.

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO HR.POLICY
VALUES (1111, 'A1111', 10000, 'Y', 500000,
        DATE('2010-01-01'), DATE('2011-01-01'));
 
INSERT INTO HR.POLICY
VALUES (1111, 'A1111', 10000, 'Y', 750000,
        DATE('2011-01-01'), DATE('9999-12-31'));
 
-- Rejected: overlaps Policy 1111 in 2010
INSERT INTO HR.POLICY
VALUES (1111, 'A1111', 10000, 'Y', 900000,
        DATE('2010-06-01'), DATE('2011-09-01'));

Adjacent rows for 1111 (ending 2011-01-01 and starting 2011-01-01) do not overlap. That is the usual way to model a change of terms on a calendar day.

Business-time updates

A plain UPDATE still works: it changes columns of the rows you qualify, including BUS_START and BUS_END if you SET them. That is dangerous if you are not thinking in periods, because you can create overlaps or holes.

FOR PORTION OF BUSINESS_TIME is the temporal UPDATE. Write it immediately after the table name. Db2 updates only the overlap with your window and inserts leftover fragments so the rest of the original period keeps the old values. Generated columns in leftover rows get new generated values; if a generated column is part of a unique key, an automatic insert can fail a constraint.

1
2
3
4
5
UPDATE HR.POLICY
FOR PORTION OF BUSINESS_TIME
    FROM DATE('2010-06-01') TO DATE('2011-09-01')
   SET COVERAGE_AMT = 900000
 WHERE ID = 1111;

If 1111 had 2010-01-01–2011-01-01 at 500000 and 2011-01-01–9999-12-31 at 750000, this UPDATE splits both rows. You can end with four rows: old coverage until June 2010, 900000 until January 2011, 900000 until September 2011, and 750000 after that. FROM ... TO uses inclusive-exclusive window semantics. BETWEEN ... AND is available when the period is defined as inclusive-inclusive in some examples; on z/OS BUSINESS_TIME is the inclusive-exclusive period, and IBM documents both FROM/TO and BETWEEN forms of FOR PORTION OF. Prefer FROM/TO unless your shop standardizes on BETWEEN.

You must not specify FOR PORTION OF if CURRENT TEMPORAL BUSINESS_TIME is not null and BUSTIMESENSITIVE is YES. In that session Db2 already adds implicit predicates on begin/end.

Business-time deletes

DELETE FOR PORTION OF BUSINESS_TIME suspends or cancels coverage for a window without wiping the entire key. If a row only partly overlaps the window, Db2 deletes the overlap and inserts leftovers for the uncovered parts — the same split logic as UPDATE, except the overlap is removed rather than given new column values.

1
2
3
4
DELETE FROM HR.POLICY
FOR PORTION OF BUSINESS_TIME
    FROM DATE('2010-06-01') TO DATE('2011-01-01')
 WHERE ID = 1414;

A policy that ran 2010-03-01 to 2011-01-01 becomes 2010-03-01 to 2010-06-01: coverage stops at June 1. A full DELETE without FOR PORTION OF removes whole rows that match WHERE, which is correct when the entire remaining term should vanish.

Querying business time

A SELECT with no period specification returns every business version. COUNT(*) for a policy ID can be 2 or 20. That surprises people who just converted a table: “current” is not implied.

• FOR BUSINESS_TIME AS OF value — the version in force at that business instant (begin <= value < end).
• FOR BUSINESS_TIME FROM value1 TO value2 — versions that overlap that window.
• FOR BUSINESS_TIME BETWEEN value1 AND value2 — overlap with the closed query range as documented for period specifications.

1
2
3
4
5
6
7
8
9
SELECT COVERAGE_AMT
  FROM HR.POLICY
  FOR BUSINESS_TIME AS OF DATE('2010-12-01')
 WHERE ID = 1111;
 
SELECT *
  FROM HR.POLICY
  FOR BUSINESS_TIME FROM DATE('2009-01-01') TO DATE('2011-01-01')
 WHERE ID = 1414;

Internally Db2 rewrites these clauses into predicates on BUS_START and BUS_END. You could write the predicates yourself, but the period specification is clearer and stays correct if column names change. Views can carry a period specification; a query against such a view must not add another specification for the same period.

Inclusive-exclusive dates in practice

Beginners lose a day when they store “through 31 December” as BUS_END = 2011-12-31 and then query AS OF 2011-12-31. That row is not in force on the 31st; the period ended at the start of that date. If the business means “valid all of December 31,” store BUS_END = 2012-01-01. TIMESTAMP periods have the same rule at the nanosecond: end is the first instant that is not in the period.

Inclusive-inclusive BETWEEN on UPDATE FOR PORTION OF appears in IBM examples for periods that were defined that way. On Db2 for z/OS the BUSINESS_TIME period itself is inclusive-exclusive. Read the row that results after a BETWEEN update: IBM’s documented example turns an original end of 2020-12-31 into a leftover end of 2013-12-31 when BETWEEN '2014-01-01' AND '9999-12-31' is used on an inclusive-inclusive illustration. Prefer FROM/TO so the window you type matches the period model in the table.

CURRENT TEMPORAL BUSINESS_TIME

The special register is TIMESTAMP(12) and starts as null. When it is not null and the package is bound BUSTIMESENSITIVE(YES), references to application-period tables get an implicit period specification, as if you had written FOR BUSINESS_TIME AS OF the register. UPDATE and DELETE pick up extra predicates on begin/end so you only change rows that overlap that instant. You then must not code FOR PORTION OF BUSINESS_TIME (SQLSTATE 428HY). Triggers, and routines defined with INHERIT SPECIAL REGISTERS, inherit the caller’s value. DEFAULT SPECIAL REGISTERS restores null inside the routine.

Use the register for a report job that should see “as of month-end” without editing fifty SELECTs. Do not leave it set in a CICS region that also runs FOR PORTION OF maintenance.

1
2
3
SET CURRENT TEMPORAL BUSINESS_TIME = TIMESTAMP('2010-12-01-00.00.00.000000000000');
SELECT COVERAGE_AMT FROM HR.POLICY WHERE ID = 1111;
SET CURRENT TEMPORAL BUSINESS_TIME = NULL;

Altering existing tables

To add business time to a populated table: ADD the two datetime columns (NOT NULL with a default for the backfill, then drop the default if you want), populate historical windows if you already stored them in other columns, ADD PERIOD BUSINESS_TIME, then ALTER the unique key or create a new unique index with BUSINESS_TIME WITHOUT OVERLAPS. Existing duplicates in time will fail that unique index — clean them before you add it. There is no ALTER INDEX ADD BUSINESS_TIME WITHOUT OVERLAPS in older Db2; you typically DROP/CREATE or create a new unique index.

Design notes

Index the business key plus begin (and often end) so AS OF lookups stay cheap. A unique index that implements WITHOUT OVERLAPS is not optional if integrity matters; the period clause alone only guarantees begin < end, not that two rows are disjoint. Partition on business dates only after you understand how FOR PORTION OF inserts extra rows — splits increase row count and can move rows between partitions. Referential constraints on temporal parents are subtle: a child row’s business window should fit a parent window, which Db2 does not automatically enforce as a temporal foreign key in the way people sometimes hope. Application checks or triggers still show up in real designs.

Do not use business-time tables as a substitute for system-period audit: a clerk can UPDATE BUS_START and rewrite the validity story unless you also add SYSTEM_TIME (see bitemporal tables). Bind BUSTIMESENSITIVE(NO) for programs that must ignore the CURRENT TEMPORAL BUSINESS_TIME register, such as data-fix utilities.

Explain It Like I'm Five

Business time is a sticker on a toy that says “this price is for summer” or “this price is for winter.” The toy box (one table) holds both stickers. You are not allowed to put two summer stickers on the same toy (WITHOUT OVERLAPS). If you change the price for only July, Db2 cuts the summer sticker into June, July, and August pieces. System time would be a photo of when you glued the sticker. Business time is what the sticker itself claims.

Exercises

1. Create a PRICE table with PERIOD BUSINESS_TIME and PRIMARY KEY (SKU, BUSINESS_TIME WITHOUT OVERLAPS). Insert two adjacent seasons for one SKU, then try an overlapping INSERT and capture the SQLCODE.
2. UPDATE FOR PORTION OF a middle month and draw the resulting rows (begin, end, price).
3. DELETE FOR PORTION OF a gap in the middle of a period and confirm leftover rows on both sides.
4. Write AS OF, FROM/TO, and unfiltered SELECT queries for the same SKU and explain why the unfiltered result has more rows.
5. Explain what goes wrong if CURRENT TEMPORAL BUSINESS_TIME is set and you also code FOR PORTION OF.