Archive-enabled tables in DB2

Archive versus temporal history

Archiving answers “most queries only need recent rows; old rows should leave the clustering and buffer-pool working set, but we must still find them sometimes.” System period versioning answers “every UPDATE must leave a timestamped photo.” If a balance is updated daily for five years, a temporal history table grows with every update. An archive table grows only when you DELETE (for example once a row is older than 90 days). Choose archive for cold closed activity; choose SYSTEM_TIME for audit of changes; choose business time for overlapping-period integrity of validity windows.

Archive tables and archive-enabled tables

The original table is the archive-enabled table (current data). The archive table holds older rows that were deleted from the original. Create the archive table first with a compatible layout — CREATE TABLE archive LIKE base is the usual start — then enable the link. The archive table must be the only table in its table space. It must not have an incomplete definition, a period, an identity column, row-begin/end or transaction-start ID columns, generated expression columns, a security label column, or referential constraints. Column counts and corresponding attributes must match the base, including ROWID and row-change-timestamp columns when the base has them (archive ROWID must be GENERATED ALWAYS). SQLCODE -20554 is the catalog of layout mistakes.

1
2
3
4
CREATE TABLE BANK.ACCT_ACTIVITY_AR LIKE BANK.ACCT_ACTIVITY;
 
ALTER TABLE BANK.ACCT_ACTIVITY
  ENABLE ARCHIVE USE BANK.ACCT_ACTIVITY_AR;

DISABLE ARCHIVE later removes the relationship. Both tables still exist; new deletes no longer move automatically even if MOVE_TO_ARCHIVE is Y.

Transparent archiving

“Transparent” means application SQL keeps using the base table name. Moving data is a DELETE, not an INSERT you write to the archive. Reading old data is a SET of a global variable, not a UNION you paste into every program. Bind options decide which packages are even allowed to see the archive.

SYSIBMADM.MOVE_TO_ARCHIVE

This built-in global variable is session-scoped. “Global” does not mean ZPARM-wide; it means the name is available to every program. Db2 12 added MOVE_TO_ARCHIVE_DEFAULT (DSN6SPRM) so the initial value can be Y, E, or N (N is the IBM default).

1
2
3
4
5
6
SET SYSIBMADM.MOVE_TO_ARCHIVE = 'Y';
 
DELETE FROM BANK.ACCT_ACTIVITY
 WHERE TXN_DATE < CURRENT DATE - 90 DAYS;
 
SET SYSIBMADM.MOVE_TO_ARCHIVE = 'N';

A nightly mover often binds with ARCHIVESENSITIVE(NO), sets MOVE_TO_ARCHIVE to Y, and only deletes. Online updaters leave the variable at N (or E if a rare path must archive and still insert). Setting Y in a CICS transaction that also INSERTs will fail those inserts — that surprise is by design so a mover cannot accidentally add “current” rows while it is in archive-only mode.

SYSIBMADM archive information and GET_ARCHIVE

Retrieval uses SYSIBMADM.GET_ARCHIVE (Y or N) together with the ARCHIVESENSITIVE bind option. GET_ARCHIVE is the per-thread switch. ARCHIVESENSITIVE is the package gate.

1
2
3
4
5
6
7
SET SYSIBMADM.GET_ARCHIVE = 'N';
SELECT * FROM BANK.ACCT_ACTIVITY
 WHERE ACCTNO = :ACCT;   -- last 90 days only
 
SET SYSIBMADM.GET_ARCHIVE = 'Y';
SELECT * FROM BANK.ACCT_ACTIVITY
 WHERE ACCTNO = :ACCT;   -- current plus archive, same SQL

Bind packages that will never need history with ARCHIVESENSITIVE(NO) so a mistaken GET_ARCHIVE = Y cannot widen the result. Bind the “show me everything” path ARCHIVESENSITIVE(YES). IBM’s usual example is internet banking: 90% of customers see three months (GET_ARCHIVE N); the other 10% click “older activity” and the same program SETs Y and repeats the SELECT.

Archive retrieval details

Db2 does not require you to mention the archive table in the SELECT list. The optimizer treats the access as a combination of current and archive, similar to UNION ALL, which is why Db2 12 optimizer work on UNION ALL also helped archive queries. Predicates still apply to both sides. If you need archive-only rows, query the archive table directly (with the right privileges) or filter on a date column you maintain. There is no FOR ARCHIVE clause analogous to FOR SYSTEM_TIME.

CURRENT GET_ACCEL_ARCHIVE is a different special register: it controls whether a query against a table archived on an accelerator uses accelerator-archived data. Do not confuse it with SYSIBMADM.GET_ARCHIVE, which is for Db2 transparent archive tables.

Temporal constraints

Archive-enabled tables do not enforce time periods. If your data has validity windows, you still need temporal DDL on a (non-archive) temporal table. Temporal constraints you already met on the business-time page are worth repeating here because they sit next to archiving in the tutorial map and people mix the features.

The implicit BUSINESS_TIME check is a constraint: begin must be less than end. It fires on INSERT and on any UPDATE that touches the period columns, including the automatic inserts FOR PORTION OF generates. WITHOUT OVERLAPS is a uniqueness constraint in time, not a check constraint: two rows may share an ID if their periods are disjoint. System period tables add a different constraint flavor: you cannot set ROW END <= ROW BEGIN, and you do not assign those columns yourself when they are GENERATED ALWAYS.

Archive enablement adds structural constraints (matching columns, no periods on the archive table) rather than calendar constraints. You can archive a table that has a DATE column you treat as “activity day” in the application, but Db2 will not stop two overlapping activity ranges unless you also defined a period and WITHOUT OVERLAPS — which then typically disqualifies that table as an archive table.

Overlapping periods

Overlap is the core business-time integrity question. For inclusive-exclusive periods, two rows overlap when each begin is less than the other end: begin1 < end2 AND begin2 < end1. Touching at a single instant (end1 = begin2) is not an overlap. That is why 2010-01-01–2011-01-01 and 2011-01-01–9999-12-31 can both exist for one policy ID.

FOR PORTION OF UPDATE/DELETE is Db2’s tool for changing a slice without creating overlaps: it splits rows so the unique constraint still holds. A raw INSERT of a colliding window fails. Archive DELETE does not split periods; it removes a whole current row (or the rows your WHERE selects) and optionally copies those full images to the archive. If you need both “no overlapping prices” and “cold rows leave the base table,” you usually keep business-time integrity on the current temporal table and use a different archive design (range-partition rotate, UNLOAD, or a non-period archive table populated by your own SQL), not ENABLE ARCHIVE on the period table.

1
2
3
4
5
6
7
-- Overlap (rejected with WITHOUT OVERLAPS):
--   existing: 2010-01-01 .. 2011-01-01
--   insert:   2010-06-01 .. 2011-09-01
 
-- Not an overlap (allowed):
--   existing: 2010-01-01 .. 2011-01-01
--   insert:   2011-01-01 .. 9999-12-31

Catalog and session information

After ENABLE ARCHIVE, catalog tables record the pairing. SYSIBM.SYSTABLES (and related archive columns in later catalogs) identify which table is archive-enabled and which table is its archive. Query the catalog when you inherit a subsystem and do not have the original DDL. Built-in global variables live in the SYSIBMADM schema; you SET them like host variables for the thread. They are not special registers, so VALUES CURRENT ... will not show them. SELECT SYSIBMADM.GET_ARCHIVE FROM SYSIBM.SYSDUMMY1 (or a VALUES clause that references the variable) is the way to confirm the session switch.

1
2
3
4
5
VALUES (SYSIBMADM.GET_ARCHIVE, SYSIBMADM.MOVE_TO_ARCHIVE);
 
SELECT NAME, TYPE, ARCHIVING_SCHEMA, ARCHIVING_TABLE
  FROM SYSIBM.SYSTABLES
 WHERE NAME = 'ACCT_ACTIVITY';

On Db2 12 (and 11 with archive support), SYSTABLES.ARCHIVING_SCHEMA and ARCHIVING_TABLE on the archive-enabled row name the archive table. Nulls mean the table is not archive-enabled. VERSIONING_SCHEMA and VERSIONING_TABLE are the analogous columns for system-period history — a useful way to tell archive and temporal apart in the catalog.

Restrictions you will hit

Archive tables cannot participate in referential integrity. They cannot be the only table in a multi-table segmented space (they must be alone in the table space). They cannot be clone tables, and clone-related operations on the base need extra care. Certain utilities and LOAD against the archive table are DBA territory: you can accidentally insert rows that never came from a DELETE, which transparent GET_ARCHIVE will then return as if they had been archived. Prefer letting DELETE with MOVE_TO_ARCHIVE populate the archive.

Triggers on the archive-enabled table still fire for the DELETE you issue; design them so they do not assume the row is gone from the subsystem entirely. An AFTER DELETE trigger that writes to a third audit table may duplicate what the archive already stores. INSTEAD OF triggers on views over archive-enabled tables are a specialist topic: test GET_ARCHIVE behavior on the view, because the view text may not be what you think is executed when Db2 expands archive access.

When to use archive, temporal, or both (on different tables)

Account activity that is insert-heavy and deleted in monthly chunks is the poster child for transparent archive. Policy terms that change while remaining “the same policy” are the poster child for SYSTEM_TIME or bitemporal. A warehouse of prices with WITHOUT OVERLAPS is business time, not archive. Some shops archive a non-temporal activity table and keep a separate bitemporal product table. That split is healthier than forcing one table to be both period-constrained and archive-enabled, which the product rules largely forbid.

Operations and design habits

Keep archive table spaces on cheaper disks if your shop still tiers that way, and REORG them on a colder schedule. RUNSTATS on both tables matter when GET_ARCHIVE is Y; a stale archive histogram makes the UNION-ALL-style access misestimate. Privileges: users who SELECT the base do not automatically hold SELECT on the archive; transparent retrieval uses the archive-enabled table’s privileges and Db2’s internal access. Still lock down direct SQL against the archive table so people cannot UPDATE archived rows into a fiction. Do not treat archive as a substitute for image copies or recovery: if you DROP the archive table you lose cold data even if the base is fine.

Thread reuse (CICS, stored procedures) is the operational foot-gun. Always SET GET_ARCHIVE and MOVE_TO_ARCHIVE back to N (or your shop default) before returning to the pool, or the next transaction inherits Y and either archives live deletes or scans the closet on every SELECT.

Explain It Like I'm Five

The classroom desk (archive-enabled table) only holds this week’s worksheets. Friday’s job (DELETE with MOVE_TO_ARCHIVE) puts old worksheets in a closet (archive table). Most kids only look at the desk (GET_ARCHIVE = N). When someone asks for last month, the teacher opens the closet too without rewriting the homework question (GET_ARCHIVE = Y). A photo album of every eraser mark (system-period history) is a different cupboard. Stickers that say “this price is for summer only” (business time, no overlapping stickers) are yet another rule — the closet does not check stickers.

Exercises

1. Create a base table and LIKE archive table, ENABLE ARCHIVE, SET MOVE_TO_ARCHIVE = 'Y', DELETE a row, and SELECT both tables. Then try INSERT with Y still set and record the error.
2. Repeat with MOVE_TO_ARCHIVE = 'E' and show that INSERT succeeds while DELETE still copies to the archive.
3. Bind a package ARCHIVESENSITIVE(YES). Toggle GET_ARCHIVE N and Y for the same SELECT and compare row counts.
4. Attempt ENABLE ARCHIVE on a table that has PERIOD BUSINESS_TIME and explain the failure using the archive-table rules.
5. Draw two DATE periods that touch at an endpoint and two that overlap. State which pair WITHOUT OVERLAPS allows.