DB2 Data-change table references and temporal FOR clauses

Two ideas that solve different problems

A data-change table reference wraps an INSERT, searched UPDATE, searched DELETE, or MERGE in parentheses and makes its directly affected rows look like an intermediate table. The outer SELECT can project those rows into the application. It is useful when the application needs values produced or replaced by the same statement that performs the change.

A temporal table is different. Its period columns describe when each version is valid. A period specification after the table name tells Db2 which versions to read. No data change is implied. Keeping these models separate prevents a common beginner mistake: FINAL TABLE means “the result of this change,” not “the latest temporal version.”

FINAL TABLE: return the post-change rows

FINAL TABLE returns the set of rows directly changed by its enclosed statement as those rows appear when that statement completes. It can wrap an INSERT, a searched UPDATE, or a MERGE. “Directly” matters: this is the transition result of the enclosed statement, not a second query of every row that triggers, cascading actions, or concurrent work might touch.

A frequent use is retrieving generated values without issuing a separate SELECT. In this example, Db2 inserts an order, generates ORDER_ID, and returns the generated key and creation timestamp through the outer SELECT:

1
2
3
4
5
6
7
SELECT ORDER_ID, CREATED_TS
INTO :HV-ORDER-ID, :HV-CREATED-TS
FROM FINAL TABLE
  (INSERT INTO ORDERS
     (CUSTOMER_ID, ORDER_STATUS, CREATED_TS)
   VALUES
     (:HV-CUSTOMER-ID, 'NEW', CURRENT TIMESTAMP));

The application avoids a race-prone “find the highest ID” query. It also avoids repeating the search predicate. Required privileges still include the privilege for the data change and SELECT privilege on the target table or view.

FINAL TABLE with a searched UPDATE

The outer SELECT can return the new values for every qualifying row. This works well for an application that must log, display, or pass on the exact set it changed:

1
2
3
4
5
SELECT EMPNO, SALARY
FROM FINAL TABLE
  (UPDATE EMP
      SET SALARY = SALARY * 1.03
    WHERE WORKDEPT = 'A00');

The UPDATE remains set based. If ten rows qualify, the intermediate result contains ten after-images. An application cursor over a SELECT containing a data-change table reference is read only; it is not an updatable cursor over the target.

FINAL TABLE restrictions worth knowing

• The data-change table reference must be the only table reference in the FROM clause of the outer fullselect. Join its result in a later query design rather than adding a second table beside it in that FROM clause.
• Db2 rejects FINAL TABLE when AFTER triggers perform further operations on the same target table. It is also restricted when the target is a view with an INSTEAD OF trigger for that operation.
• The exact INSERT, UPDATE, or MERGE form must satisfy the Db2 data-change table reference rules. Correlation, view, LOB, XML, ROWID, and MERGE restrictions can depend on the enclosed statement.
• Row permissions already constrain the intermediate rows, and column masks are applied to the outermost select list when row or column access control is active.

OLD TABLE: return the pre-change rows

OLD TABLE exposes the rows as they existed before a searched UPDATE or searched DELETE. It is ideal for an audit feed that needs the values being replaced or removed. OLD TABLE does not wrap INSERT because an inserted row has no old image.

1
2
3
4
5
SELECT EMPNO, SALARY AS PREVIOUS_SALARY
FROM OLD TABLE
  (UPDATE EMP
      SET SALARY = SALARY * 1.03
    WHERE WORKDEPT = 'A00');

The salaries returned are the values before multiplication. The underlying UPDATE still occurs. OLD TABLE is therefore not an undo operation, a simulation, or a temporal query. Transaction rules remain normal: the change can still be committed or rolled back with its unit of work.

For DELETE, OLD TABLE is especially clear because the deleted rows no longer exist in the base table after the statement:

1
2
3
4
5
SELECT ORDER_ID, CUSTOMER_ID, ORDER_STATUS
FROM OLD TABLE
  (DELETE FROM ORDERS
    WHERE ORDER_STATUS = 'CANCELLED'
      AND CREATED_TS < CURRENT TIMESTAMP - 2 YEARS);

Treat this as sensitive data. The result can contain every deleted value selected by the outer query, so authorization, masking, audit retention, and application logging deserve the same care as the original table.

Where NEW TABLE really belongs

It is tempting to write SELECT ... FROM NEW TABLE(UPDATE ...), especially after learning OLD TABLE. That is not Db2 for z/OS data-change table reference syntax. Use FINAL TABLE for the post-change result of application SQL.

NEW TABLE instead belongs to a trigger's REFERENCING clause. It gives a trigger a set-oriented transition table containing inserted or updated rows. OLD TABLE in a trigger is also a transition table; although the words are identical, its scope and grammar differ from an application SELECT that wraps a DML statement.

1
2
3
4
5
6
7
8
9
10
11
CREATE TRIGGER AUDIT_ORDER_CHANGES
  AFTER UPDATE OF ORDER_STATUS ON ORDERS
  REFERENCING OLD TABLE AS O
              NEW TABLE AS N
  FOR EACH STATEMENT
  INSERT INTO ORDER_STATUS_AUDIT
    (ORDER_ID, OLD_STATUS, NEW_STATUS, CHANGED_AT)
  SELECT N.ORDER_ID, O.ORDER_STATUS, N.ORDER_STATUS,
         CURRENT TIMESTAMP
    FROM N
    JOIN O ON O.ORDER_ID = N.ORDER_ID;

The example is conceptual trigger SQL: the transition names exist only in the trigger body, are read only, and represent the complete affected set. OLD transition tables apply to UPDATE and DELETE events; NEW transition tables apply to INSERT and UPDATE events. Always verify the allowed trigger timing, granularity, transition references, and target design for the Db2 release you deploy.

Temporal tables: system time and business time

Db2 supports two period meanings. A system-period temporal table uses a SYSTEM_TIME period whose begin and end values are maintained by Db2. With system period data versioning enabled, a related history table stores prior versions. This answers “what did the database record look like at that time?”

An application-period temporal table uses a BUSINESS_TIME period whose values are maintained by the application. It answers “when was this fact valid for the business?” A future price can be business-valid next month even though it was entered into the database today. A bitemporal table has both periods and can answer both dimensions together.

FOR SYSTEM_TIME examples

AS OF: one point in system history

Place the period specification immediately after the temporal table reference. Db2 evaluates the SYSTEM_TIME begin and end columns and returns versions that existed at the supplied timestamp:

1
2
3
4
5
SELECT CUSTOMER_ID, CUSTOMER_NAME, CREDIT_LIMIT
FROM CUSTOMER
  FOR SYSTEM_TIME AS OF
    TIMESTAMP('2026-03-31-23.59.59.000000')
WHERE CUSTOMER_ID = 10042;

For the usual inclusive-exclusive system period, a row qualifies when its begin value is less than or equal to the AS OF value and its end value is greater than that value. A null AS OF value produces an empty table. The expression must be comparable to the period columns, and its timestamp precision cannot exceed their precision.

FROM/TO and BETWEEN

1
2
3
4
5
6
7
8
9
SELECT CUSTOMER_ID, CUSTOMER_NAME,
       SYS_START, SYS_END
FROM CUSTOMER
  FOR SYSTEM_TIME FROM
    TIMESTAMP('2026-01-01-00.00.00')
  TO
    TIMESTAMP('2026-04-01-00.00.00')
WHERE CUSTOMER_ID = 10042
ORDER BY SYS_START;

FROM/TO finds row versions that overlap the requested interval from the first value up to, but not including, the second. If the start is greater than or equal to the end, the result is empty. BETWEEN/AND also tests overlap, but its requested upper endpoint is included. With equal endpoints, BETWEEN behaves like AS OF, while FROM/TO with equal endpoints is empty.

1
2
3
4
5
6
7
SELECT CUSTOMER_ID, CREDIT_LIMIT, SYS_START, SYS_END
FROM CUSTOMER
  FOR SYSTEM_TIME BETWEEN
    TIMESTAMP('2026-03-01-00.00.00')
  AND
    TIMESTAMP('2026-03-31-23.59.59')
WHERE CUSTOMER_ID = 10042;

These are overlap queries, not filters requiring an entire row period to fit inside the requested range. That distinction explains why a version that began before the first date can still qualify: it remained valid during some part of the requested interval.

FOR BUSINESS_TIME examples

BUSINESS_TIME uses the same AS OF, FROM/TO, and BETWEEN/AND shapes, but applies them to an application-maintained period. If the period columns are DATE, use values comparable to DATE; if they are TIMESTAMP, use compatible timestamp expressions.

1
2
3
4
SELECT POLICY_ID, COVERAGE_CODE, PREMIUM
FROM POLICY_RATE
  FOR BUSINESS_TIME AS OF DATE('2026-07-01')
WHERE POLICY_ID = 77501;

1
2
3
4
5
6
7
SELECT POLICY_ID, COVERAGE_CODE, PREMIUM,
       BUSINESS_START, BUSINESS_END
FROM POLICY_RATE
  FOR BUSINESS_TIME FROM DATE('2026-01-01')
  TO DATE('2027-01-01')
WHERE POLICY_ID = 77501
ORDER BY BUSINESS_START;

The first query asks which policy rate is business-valid on July 1. It does not ask when that row was physically inserted. The second asks for every version whose business-valid period overlaps calendar year 2026 under FROM/TO endpoint rules.

Bitemporal query

A bitemporal table can use each period specification once on the same table reference. The result must satisfy both. The next query asks: “Based on what the database knew on March 15, what rate was considered business-valid for July 1?”

1
2
3
4
5
6
7
SELECT POLICY_ID, PREMIUM
FROM POLICY_RATE
  FOR SYSTEM_TIME AS OF
    TIMESTAMP('2026-03-15-12.00.00')
  FOR BUSINESS_TIME AS OF
    DATE('2026-07-01')
WHERE POLICY_ID = 77501;

Temporal caveats that prevent surprises

• FOR SYSTEM_TIME requires a system-period temporal table or an eligible view. FOR BUSINESS_TIME requires an application-period temporal table or an eligible view.
• SYSTEM_TIME and BUSINESS_TIME can each appear no more than once per table reference. A bitemporal reference can contain one of each.
• A period value that is null produces an empty table. Reversed FROM/TO bounds also produce an empty table; reversed BETWEEN bounds are empty when the first value is greater than the second.
• Period boundaries matter. SYSTEM_TIME commonly uses inclusive-exclusive periods. BUSINESS_TIME periods can have endpoint behavior defined by the table's period design. Test exact boundary dates rather than assuming ordinary BETWEEN semantics.
• When CURRENT TEMPORAL SYSTEM_TIME or CURRENT TEMPORAL BUSINESS_TIME is non-null and the corresponding SYSTIMESENSITIVE or BUSTIMESENSITIVE bind option is YES, Db2 can apply an implicit AS OF specification. Do not add an explicit specification for the same period in that situation.
• Reset a temporal special register to NULL when its session-wide effect is no longer wanted. COMMIT and ROLLBACK do not provide a substitute for deliberate register management.
• An unqualified query of a system-period temporal table normally returns current rows, not every historical row. Use the temporal clause when history is required.

FOR PORTION OF BUSINESS_TIME is a change clause

Do not confuse a query period specification with FOR PORTION OF BUSINESS_TIME. FOR BUSINESS_TIME after a table name reads versions. FOR PORTION OF BUSINESS_TIME appears in supported UPDATE or DELETE statements and limits the business-time portion to change. Db2 can split a row's business period so the unaffected portions retain their prior values.

1
2
3
4
5
6
UPDATE POLICY_RATE
  FOR PORTION OF BUSINESS_TIME
    FROM DATE('2026-07-01')
    TO DATE('2026-10-01')
SET PREMIUM = PREMIUM * 1.05
WHERE POLICY_ID = 77501;

This changes only the specified business-valid portion for qualifying rows. Period boundaries, uniqueness, constraints, and generated row splitting make application- period changes more than ordinary date predicates. Design and test them with sample periods that begin before, inside, and after the requested portion.

A practical decision guide

• Need generated keys or new values from the DML you are executing? Use FINAL TABLE.
• Need values that an UPDATE or DELETE is replacing? Use OLD TABLE.
• Writing a trigger that must process every inserted or updated row? Define a NEW TABLE transition table in REFERENCING.
• Need the database version that existed at a past timestamp? Use FOR SYSTEM_TIME.
• Need a fact valid on a business date? Use FOR BUSINESS_TIME.
• Need to change only part of a business-valid interval? Investigate FOR PORTION OF BUSINESS_TIME.

Explain It Like I'm Five

Imagine replacing a drawing on a classroom wall. OLD TABLE takes a photo just before you replace it. FINAL TABLE takes a photo of the new drawing after your change. A trigger's NEW TABLE is a tray where the teacher puts all the new drawings so a helper can inspect them.

Temporal tables are photo albums. SYSTEM_TIME asks, “What photo was in the album when the clock said Tuesday at noon?” BUSINESS_TIME asks, “Which drawing did our schedule say was valid for summer?” One follows the database clock; the other follows dates chosen by the business.

Exercises

• Write an INSERT inside FINAL TABLE that returns a generated invoice number and creation timestamp into host variables. Explain why selecting MAX(invoice_number) afterward would be unsafe.
• Update three employee salaries and use OLD TABLE to return their employee numbers and pre-update salaries. Predict the values before running the statement.
• Explain why NEW TABLE is valid in a trigger REFERENCING clause but invalid as a data-change table reference. Rewrite an invalid NEW TABLE(UPDATE...) expression using FINAL TABLE.
• Create AS OF, FROM/TO, and BETWEEN queries for one system-period customer row. Add a version that ends exactly at the requested boundary and compare the results.
• For a bitemporal policy table, write a question in plain English that requires both SYSTEM_TIME and BUSINESS_TIME, then translate it into SQL.
• Sketch three business-period rows expected after a FOR PORTION OF BUSINESS_TIME update changes the middle of one existing period.

Frequently asked questions