DB2 Write temporal SQL

Prerequisites

You should be comfortable with CREATE TABLE, ALTER TABLE, SELECT, INSERT, UPDATE, DELETE, constraints, and indexes. You also need authority to create or alter the sample objects in a development subsystem. Temporal DDL details and available enhancements depend on Db2 function level and application compatibility, so verify the IBM documentation for the release where the SQL will run.

• Use a test schema; the examples use HR and SALES for clarity.
• Decide whether the requirement concerns database history, business validity, or both.
• Choose compatible DATE or TIMESTAMP types and document endpoint semantics before loading data.
• Plan authorization, retention, utility, and recovery operations for the base and history objects.

Understand the two temporal clocks

A system-period temporal table answers what Db2 recorded at an earlier system time. It contains generated row-begin, row-end, and transaction-start-ID columns. The SYSTEM_TIME period is formed from the row-begin and row-end columns. When system-period data versioning is enabled, an associated history table receives previous versions created by updates and deletes.

An application-period temporal table answers when a fact is valid in the domain. For example, a rate might be entered today but valid from next January through March. Its BUSINESS_TIME begin and end values are supplied by the application. Db2 gives those columns formal period semantics, but it cannot decide the correct business dates for you.

A table can define both periods and become bitemporal. Then it can answer a precise question such as, “According to what the database knew on March 10, which premium was considered valid on July 1?” Do not substitute one clock for the other: a load timestamp is not automatically an effective date, and an effective date does not prove when Db2 learned the fact.

Steps: create and query a system-period table

1. Define the generated system columns and PERIOD SYSTEM_TIME.
2. Create a compatible history table.
3. Enable versioning with ALTER TABLE ADD VERSIONING.
4. Change ordinary business columns and query earlier versions through the base table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE HR.EMPLOYEE_TEMPORAL (
  EMP_ID       INTEGER NOT NULL PRIMARY KEY,
  DEPT_CODE    CHAR(3) NOT NULL,
  JOB_TITLE    VARCHAR(60) 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_START  TIMESTAMP(12)
               GENERATED ALWAYS AS TRANSACTION START ID,
  PERIOD SYSTEM_TIME (SYS_START, SYS_END)
);
 
CREATE TABLE HR.EMPLOYEE_TEMPORAL_HISTORY
  LIKE HR.EMPLOYEE_TEMPORAL;
 
ALTER TABLE HR.EMPLOYEE_TEMPORAL
  ADD VERSIONING
  USE HISTORY TABLE HR.EMPLOYEE_TEMPORAL_HISTORY;

The application normally omits SYS_START, SYS_END, and TRANS_START from INSERT and UPDATE statements because Db2 generates them. The base and history tables must satisfy IBM's compatibility requirements before versioning can be enabled. A production design also needs table spaces, indexes, privileges, utility plans, and retention rules; the compact DDL here focuses on temporal behavior.

Insert, update, and delete behavior

1
2
3
4
5
6
7
8
9
10
INSERT INTO HR.EMPLOYEE_TEMPORAL
       (EMP_ID, DEPT_CODE, JOB_TITLE)
VALUES (42, 'A00', 'ANALYST');
 
UPDATE HR.EMPLOYEE_TEMPORAL
   SET JOB_TITLE = 'SENIOR ANALYST'
 WHERE EMP_ID = 42;
 
DELETE FROM HR.EMPLOYEE_TEMPORAL
 WHERE EMP_ID = 42;

INSERT creates the current version in the base table. UPDATE closes the old version, preserves it in the history table, and leaves the new current version in the base table. DELETE removes the current base row while preserving its final version in history. These effects participate in the same unit of work as the original change; a rollback does not leave a committed temporal history of a change that never committed.

Write FOR SYSTEM_TIME queries

AS OF one timestamp

1
2
3
4
SELECT EMP_ID, DEPT_CODE, JOB_TITLE
  FROM HR.EMPLOYEE_TEMPORAL
  FOR SYSTEM_TIME AS OF :AS_OF_TS
 WHERE EMP_ID = :EMP_ID;

AS OF asks for versions whose system period contains one timestamp. Under the usual inclusive-start, exclusive-end interpretation, a version qualifies when SYS_START is less than or equal to the supplied value and SYS_END is greater than it. Query the temporal base-table name rather than manually unioning the base and history tables; Db2 then applies the temporal rules consistently.

FROM TO for a half-open interval

1
2
3
4
5
6
7
SELECT EMP_ID, JOB_TITLE, SYS_START, SYS_END
  FROM HR.EMPLOYEE_TEMPORAL
  FOR SYSTEM_TIME
      FROM TIMESTAMP('2026-01-01-00.00.00.000000000000')
        TO TIMESTAMP('2026-04-01-00.00.00.000000000000')
 WHERE EMP_ID = 42
 ORDER BY SYS_START;

FROM/TO returns versions that overlap the requested interval. The first boundary is included and the requested upper boundary is excluded. A version can begin before January and still qualify if it remained current during part of the requested window. This is an overlap test, not a requirement that the complete version fit inside it.

BETWEEN for an inclusive requested endpoint

1
2
3
4
5
SELECT EMP_ID, JOB_TITLE, SYS_START, SYS_END
  FROM HR.EMPLOYEE_TEMPORAL
  FOR SYSTEM_TIME BETWEEN :START_TS AND :END_TS
 WHERE EMP_ID = 42
 ORDER BY SYS_START;

Temporal BETWEEN also finds overlapping versions, but the upper requested endpoint participates in the overlap test. It is therefore not interchangeable punctuation for FROM/TO. With equal values, BETWEEN behaves like a point-in-time request, while FROM/TO with equal bounds describes an empty interval. Test rows ending or beginning exactly on each boundary.

Steps: create and query a BUSINESS_TIME table

Product prices are business-valid facts. DATE is suitable when the business rule changes at day boundaries. The following key uses BUSINESS_TIME WITHOUT OVERLAPS so one product cannot have conflicting price periods. Adjacent periods are allowed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE SALES.PRODUCT_PRICE (
  PRODUCT_ID  INTEGER NOT NULL,
  PRICE       DECIMAL(11,2) NOT NULL,
  VALID_FROM  DATE NOT NULL,
  VALID_TO    DATE NOT NULL,
  PERIOD BUSINESS_TIME (VALID_FROM, VALID_TO),
  PRIMARY KEY (PRODUCT_ID, BUSINESS_TIME WITHOUT OVERLAPS)
);
 
INSERT INTO SALES.PRODUCT_PRICE
       (PRODUCT_ID, PRICE, VALID_FROM, VALID_TO)
VALUES (100, 19.95, DATE('2026-01-01'), DATE('2026-04-01'));
 
INSERT INTO SALES.PRODUCT_PRICE
       (PRODUCT_ID, PRICE, VALID_FROM, VALID_TO)
VALUES (100, 21.50, DATE('2026-04-01'), DATE('2026-07-01'));

The periods meet at April 1 without overlapping. A third row for product 100 covering March 15 through May 1 would conflict with the temporal key. Database enforcement is safer than a “check, then insert” query because two concurrent units of work could both pass an application-only overlap check.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Price valid on one business date
SELECT PRODUCT_ID, PRICE
  FROM SALES.PRODUCT_PRICE
  FOR BUSINESS_TIME AS OF DATE('2026-05-15')
 WHERE PRODUCT_ID = 100;
 
-- Price periods overlapping the first half of 2026
SELECT PRODUCT_ID, PRICE, VALID_FROM, VALID_TO
  FROM SALES.PRODUCT_PRICE
  FOR BUSINESS_TIME
      FROM DATE('2026-01-01')
        TO DATE('2026-07-01')
 WHERE PRODUCT_ID = 100
 ORDER BY VALID_FROM;

FOR BUSINESS_TIME uses the same AS OF, FROM/TO, and BETWEEN shapes as SYSTEM_TIME, but applies them to application-maintained business dates or timestamps. Keep parameter types compatible with the period columns. A character string that merely looks like a date adds conversion risk and can make access-path reasoning harder.

Change only part of a business period

An ordinary UPDATE changes every qualifying row as a whole. An eligible FOR PORTION OF BUSINESS_TIME UPDATE changes only the overlapping business-time portion. Db2 can split one source period into before, changed, and after rows so that values outside the requested portion remain intact.

1
2
3
4
5
6
UPDATE SALES.PRODUCT_PRICE
  FOR PORTION OF BUSINESS_TIME
      FROM DATE('2026-05-01')
        TO DATE('2026-06-01')
   SET PRICE = 22.25
 WHERE PRODUCT_ID = 100;

If the existing row covers April 1 through July 1, the logical result can be three adjacent periods: the old price for April, the new price for May, and the old price from June through June 30. The exact generated changes must still satisfy uniqueness, constraints, triggers, and authorization. FOR PORTION OF BUSINESS_TIME can also apply to supported DELETE forms, removing only the selected business-valid portion rather than necessarily deleting the complete source period.

1
2
3
4
5
DELETE FROM SALES.PRODUCT_PRICE
  FOR PORTION OF BUSINESS_TIME
      FROM DATE('2026-05-10')
        TO DATE('2026-05-20')
 WHERE PRODUCT_ID = 100;

Combine both clocks in bitemporal SQL

1
2
3
4
5
SELECT POLICY_ID, PREMIUM
  FROM INSURANCE.POLICY_RATE
  FOR SYSTEM_TIME AS OF :WHAT_DB2_KNEW_AT
  FOR BUSINESS_TIME AS OF :BUSINESS_VALID_DATE
 WHERE POLICY_ID = :POLICY_ID;

This query requires a table that defines both periods. The result must satisfy both specifications. Each period name can appear only once on the same table reference. Db2 also has CURRENT TEMPORAL SYSTEM_TIME and CURRENT TEMPORAL BUSINESS_TIME special registers that can apply an implicit AS OF specification when the relevant package bind option is sensitive. They are useful for consistent reporting, but pooled connections must reset them deliberately so one request does not affect the next.

Temporal referential integrity at a high level

Ordinary referential integrity asks whether a matching parent key exists. Temporal referential integrity adds a coverage question: is the child's BUSINESS_TIME period covered by matching parent rows? Coverage can come from one parent row or from the union of contiguous matching parent periods. This lets a child contract remain valid across adjacent versions of its parent.

1
2
3
4
5
-- Conceptual shape; verify release-specific DDL requirements
CONSTRAINT FK_CONTRACT_CUSTOMER
  FOREIGN KEY (CUSTOMER_ID, PERIOD BUSINESS_TIME)
  REFERENCES SALES.CUSTOMER_STATUS
             (CUSTOMER_ID, PERIOD BUSINESS_TIME)

IBM documents supporting index requirements, including a parent unique index with BUSINESS_TIME WITHOUT OVERLAPS and an appropriate child index that supports period coverage checks. Parent UPDATE and DELETE support also depends on the active function level in Db2 12 environments. Treat temporal RI as a schema feature to design and test, not as a phrase to add after ordinary foreign-key DDL.

Auditing is not the same as recovery

System-period history is valuable audit evidence because it can show old values and the system periods during which they were current. It can help answer who or what changed a row when combined with trusted actor, transaction, or audit columns. However, generated period columns alone do not identify the human reason for a change. If that matters, capture approved user identity, source transaction, and reason codes through a governed design.

Temporal history is also not a substitute for recovery. An AS OF query can help locate an earlier row value, but recovering from media failure, object damage, accidental DDL, or broad corruption still requires Db2 logs, image copies, backups, catalog consistency, and tested RECOVER procedures. History retention and recovery retention serve different purposes and should have separate policies.

Performance and indexing

• Index for the actual leading predicates. For lookups by EMP_ID and system range, an index beginning with EMP_ID and including useful period columns is a candidate, not a universal rule.
• Remember that current and history data are separate physical objects. A fast current lookup does not prove that a ten-year AS OF or range query has an efficient history access path.
• Collect representative RUNSTATS for base tables, history tables, and indexes. Use EXPLAIN to inspect each important AS OF and range query.
• Keep temporal predicates typed and sargable. Avoid wrapping indexed period columns in CHAR, DATE, or arithmetic merely to match an input format.
• Expect history growth. Define retention, partitioning, archiving, reorganization, and utility windows before a heavily updated table accumulates years of versions.
• Select only needed columns. Historical scans can multiply the number of qualifying versions, making unnecessary wide rows and sorts expensive.

Common errors and pitfalls

• Confusing the clocks: SYSTEM_TIME says when Db2 held a version; BUSINESS_TIME says when the business fact is valid.
• Querying history directly: this can omit current rows or reproduce temporal boundary logic incorrectly. Prefer a temporal base-table reference.
• Assuming BETWEEN equals FROM/TO: their requested upper endpoints differ. Boundary rows deserve explicit tests.
• Expecting automatic business dates: Db2 maintains SYSTEM_TIME, but the application remains responsible for correct BUSINESS_TIME values.
• Allowing accidental overlap: use BUSINESS_TIME WITHOUT OVERLAPS when overlapping periods violate the key's business rule.
• Forgetting implicit temporal registers: a non-null special register can change an unqualified query when the package bind option enables sensitivity.
• Using mismatched precision: period expressions must be compatible with period-column types and timestamp precision. Cast deliberately.
• Treating history as immutable proof by itself: authorization and governance determine whether the evidence is trustworthy.

Verify results

1. Insert one system-period row, record its visible SYS_START, then commit. Update it in a later unit of work and commit again.
2. Run current, AS OF, FROM/TO, and BETWEEN queries using values before, exactly on, and after the observed boundaries.
3. Confirm that the current query returns the latest base version and the temporal query returns the expected earlier version.
4. Insert two adjacent BUSINESS_TIME rows and confirm both succeed. Attempt an overlap for the same logical key and confirm that the temporal uniqueness rule rejects it.
5. Run FOR PORTION OF BUSINESS_TIME in a test transaction, inspect the split periods, and roll back or commit according to the test plan.
6. EXPLAIN representative point and range queries, and verify statistics exist for both current and historical objects.

Explain It Like I'm Five

Imagine a school keeps two calendars. The first is a photo album. Every time a name card changes, the school saves the old photo and writes down when the new photo replaced it. That is SYSTEM_TIME. You can ask, “What did the card look like last Tuesday?”

The second calendar says when a rule is supposed to apply. A lunch price entered today might be marked “good from September until December.” That is BUSINESS_TIME. One calendar remembers when the school knew something; the other says when that thing is meant to be true.

Exercises

• Create a system-period ACCOUNT_STATUS table and history table. Insert, update, and delete one account in separate committed units of work, then reconstruct its timeline.
• Write AS OF, FROM/TO, and BETWEEN queries for the same employee. Add test values exactly at the period boundaries and explain every difference.
• Create a BUSINESS_TIME shipping-rate table with a non-overlapping logical key. Test adjacent, nested, and partially overlapping periods.
• Use FOR PORTION OF BUSINESS_TIME to change the middle month of a quarterly price. Predict the resulting rows before executing the statement.
• Write a bitemporal question in plain English, then identify the distinct system timestamp and business date needed to answer it.
• Draft a retention plan that separately addresses audit history, history-table growth, image copies, logs, and disaster recovery.

Frequently asked questions