DB2 System Versioning on IBM i – Automatic History Tracking Without Triggers

By -

In this post I'll show you how to implement DB2 System Versioning on IBM i — a built-in mechanism that automatically keeps a full history of every INSERT, UPDATE, and DELETE on a table, without writing a single line of trigger code.



This is a practical, working example I built and tested step by step. I'll show you the real errors I ran into and exactly how I fixed them.

What You'll Learn

  • How to create a Journal and Journal Receiver with a CL program
  • How to design a main table with temporal columns
  • Why CREATE TABLE ... LIKE doesn't work for history tables (and what to do instead)
  • The correct sequence: create → journal → activate versioning
  • How to verify the history with SELECT queries

The Setup: Two Source Members, One CL Program

Everything runs from a single CL program (HISTORY010.CLP) that orchestrates two SQL script members stored in SQLTBLSRC.

The execution sequence is critical and must follow this exact order:

  1. Create journal receiver and journal (if not already there)
  2. HISTORY020.SQL — cleanup + create both tables
  3. STRJRNPF in CL — journal both tables (before versioning!)
  4. HISTORY060.SQL — activate versioning + test data

HISTORY010.CLP

PGM
DCL VAR(&LIB) TYPE(*CHAR) LEN(10) VALUE('VDOTEST1')
DCL VAR(&JRN) TYPE(*CHAR) LEN(10) VALUE('JRNHIST')
DCL VAR(&RCV) TYPE(*CHAR) LEN(10) VALUE('JRNRCVHIST')
/* ================================================= */
/* MONITOR GLOBAL PER ERRORI INATTESI                */
/* ================================================= */
MONMSG MSGID(CPF0000)
/* ================================================= */
/* CREATE JOURNAL RECEIVER IF NOT EXISTS             */
/* ================================================= */
CHKOBJ OBJ(&LIB/&RCV) OBJTYPE(*JRNRCV)
MONMSG MSGID(CPF9801) EXEC(DO)
   CRTJRNRCV JRNRCV(&LIB/&RCV) +
             THRESHOLD(100000) +
             TEXT('Journal Receiver per History')
ENDDO
/* ================================================= */
/* CREATE JOURNAL IF NOT EXISTS                      */
/* ================================================= */

CHKOBJ OBJ(&LIB/&JRN) OBJTYPE(*JRN)
MONMSG MSGID(CPF9801) EXEC(DO)
   CRTJRN JRN(&LIB/&JRN) +
          JRNRCV(&LIB/&RCV) +
          MNGRCV(*SYSTEM) +
          DLTRCV(*NO) +
          RCVSIZOPT(*MAXOPT2) +
          TEXT('Journal per History')
ENDDO
/* ================================================= */
/* RUN SQL SCRIPT                                    */
/* ================================================= */
RUNSQLSTM SRCFILE(&LIB/SQLTBLSRC) SRCMBR(HISTORY020) +
          COMMIT(*NONE) DFTRDBCOL(&LIB)
/* ================================================= */
/* START JOURNALING (tabelle già esistenti)          */
/* ================================================= */
STRJRNPF FILE(&LIB/LGCUS00F) JRN(&LIB/&JRN) IMAGES(*BOTH)
MONMSG MSGID(CPF0000)
STRJRNPF FILE(&LIB/LGCUS00H) JRN(&LIB/&JRN) IMAGES(*BOTH)
MONMSG MSGID(CPF0000)
/* ================================================= */
/* RUN SQL SCRIPT                                    */
/* ================================================= */
RUNSQLSTM SRCFILE(&LIB/SQLTBLSRC) SRCMBR(HISTORY060) +
          COMMIT(*NONE) DFTRDBCOL(&LIB)
ENDPGM

Notice that STRJRNPF is called in the CL, between the two RUNSQLSTM calls. This is intentional and essential — see the "Lessons Learned" section below for why.

HISTORY020.SQL — Cleanup + Create Tables

-- =====================================================
-- CLEANUP (gestisce riesecuzione pulita)
-- =====================================================
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
    ALTER TABLE LGCUS00F DROP VERSIONING;
END;

BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
    CALL QSYS2.QCMDEXC('DLTF FILE(VDOTEST1/LGCUS00H)');
END;

-- =====================================================
-- TABELLA PRINCIPALE
-- =====================================================
CREATE OR REPLACE TABLE LGCUS00F (
    LGID00   INTEGER       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    LGDTTM   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LGNAME   CHAR(50),
    LGSURN   CHAR(50),
    START_TS TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
    END_TS   TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
    TS_ID    TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
    PERIOD SYSTEM_TIME (START_TS, END_TS)
) RCDFMT LGCUS;

-- =====================================================
-- HISTORY TABLE (esplicita, NO LIKE)
-- =====================================================
CREATE TABLE LGCUS00H (
    LGID00   INTEGER       NOT NULL,
    LGDTTM   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LGNAME   CHAR(50),
    LGSURN   CHAR(50),
    START_TS TIMESTAMP(12) NOT NULL,
    END_TS   TIMESTAMP(12) NOT NULL,
    TS_ID    TIMESTAMP(12) NOT NULL
) RCDFMT LGCUSH;

The history table has the same columns as the main table, but:

  • LGID00 is plain NOT NULL — no IDENTITY, no GENERATED
  • START_TS / END_TS / TS_ID are plain TIMESTAMP — DB2 writes them directly
  • No PERIOD clause, no PRIMARY KEY — the history must store multiple versions of the same row
  • A different RCDFMT name (LGCUSH) to avoid conflicts

HISTORY060.SQL — Activate Versioning + Test Data

-- =====================================================
-- ATTIVA SYSTEM VERSIONING
-- =====================================================
ALTER TABLE LGCUS00F
    ADD VERSIONING USE HISTORY TABLE LGCUS00H;

-- =====================================================
-- INDICE SULLA HISTORY
-- =====================================================
-- CREATE INDEX LGCUS00H_01
--    ON LGCUS00H (LGID00, START_TS);

INSERT INTO LGCUS00F (LGNAME, LGSURN) VALUES
    ('Mario',    'Rossi'),
    ('Giulia',   'Bianchi'),
    ('Luca',     'Verdi'),
    ('Federica', 'Esposito');

UPDATE LGCUS00F
    SET LGNAME = 'Marco'
WHERE LGID00 = 1;

UPDATE LGCUS00F
    SET LGNAME = 'Giovannino'
WHERE LGID00 = 3;

UPDATE LGCUS00F
    SET LGNAME = 'Francesco'
WHERE LGID00 = 2;

DELETE FROM LGCUS00F
WHERE LGID00 = 2;




Verify the Results

After running the CL, check both tables:

-- Current state of the main table
SELECT LGID00, LGNAME, LGSURN, START_TS
FROM LGCUS00F
ORDER BY LGID00;

-- Full history — every version ever written
SELECT LGID00, LGNAME, LGSURN, START_TS, END_TS
FROM LGCUS00H
ORDER BY LGID00, START_TS;

Expected results:

  • LGCUS00F: 3 rows — Marco Rossi, Giovannino Verdi, Federica Esposito (row 2 deleted)
  • LGCUS00H: 4 rows — Mario Rossi (before update), Luca Verdi (before update), Giulia Bianchi (before update), Francesco Bianchi (before delete)








DB2 tracked every change automatically, with no triggers involved.

Lessons Learned (the hard way)

1. CREATE TABLE ... LIKE doesn't work for history tables
LIKE copies GENERATED ALWAYS columns from the main table. IBM i doesn't allow dropping those expressions (DROP EXPRESSION is not supported), and DB2 refuses to write to GENERATED ALWAYS columns during versioning. The only clean solution is to define the history table explicitly.

2. QCMDEXC('STRJRNPF ... + JRN(...)') silently fails
The + continuation character is CL syntax. Inside a SQL string passed to QCMDEXC, it is treated literally and the command arrives malformed. IBM i swallows the error silently when wrapped in COMMIT(*NONE), so the tables end up unjournaled. Always use STRJRNPF directly in CL, never inside QCMDEXC.

3. Journaling must happen before ADD VERSIONING
This is an IBM i requirement, not optional. The sequence is: create tables → journal both → then activate versioning. Getting this order wrong produces SQL0443.

4. Cleanup order matters
To safely drop the tables and re-run the script: first DROP VERSIONING, then drop the history table, then drop the main table. IBM i will refuse to delete the history table while versioning is still active.

Final Thoughts

System Versioning is one of the most powerful and underused features of DB2 for IBM i. Once you get the setup right, the auditing is completely transparent — no triggers, no extra application logic, no performance hit during normal reads.

The full source is structured as re-runnable scripts: execute HISTORY010.CLP as many times as you want, it will always start clean.

Have you used System Versioning in production? Did you find a cleaner way to handle the LIKE issue, or a different approach to the journaling setup? Drop a comment below — I'd love to hear your experience, suggestions, or improvements.

Comments

Post a Comment

Popular posts from this blog

Using a Parameterized Cursor in SQLRPGLE

By -
Image
Using a Parameterized Cursor in SQLRPGLE – A Simple and Clean Example When working with embedded SQL on IBM i, using a cursor with parameters is a great way to selectively retrieve data from a file based on runtime input. In this post, I’ll walk you through a simple SQLRPGLE program that demonstrates this concept. This free-format RPG example shows how to: Accept an input parameter Open a cursor based on that parameter Fetch records from the CLANA00F file Display basic customer information Properly handle SQL errors What Is CLANA00F? The file CLANA00F contains customer master data. Here's a simplified layout of its most relevant fields: Field Type Description CLCCL0 S(7) Customer numeric ID CLNOM0 A(50) Customer name CLCIB0 A(3) International code Our goal is to retrieve only those customers whose CLCIB0 matches a parameter passed when the program is called. Program Highlights ctl-opt dftactgrp(*no) actgrp(*caller); dcl-s CLCCL0...
Read more

SQL Indicator Variables in Embedded SQL for RPG on IBM i

By -
Image
SQL Indicator Variables in Embedded SQL for RPG on IBM i When working with DB2 on IBM i, handling NULL values properly is essential, especially when you retrieve data using embedded SQL in RPG. In this post, I’ll show you a simple and practical way to check if a column is NULL using what’s called an SQL Indicator Variable . The Scenario Let’s say you have a customer file called CLANA00F with the following fields: Field Type Length Description CLANN0 CHAR(1) 1 Customer status CLCCL0 DEC(7,0) 7 Customer ID CLCIB0 CHAR(3) 3 Customer type CLNOM0 CHAR(50) 50 Customer name CLSTS0 CHAR(1) 1 Customer status Now, imagine that CLSTS0 is nullable (i.e., it can contain NULL ), and you want to find out whether it has a value or not. The RPG Example **free ctl-opt dftactgrp ( *no ) actgrp ( *caller ); // **************************************************************** // * Autore.......: ALDO SUCCI ...
Read more

Dynamic SQL in RPG

By -
Image
Simple Example of Using Dynamic SQL in RPG Dynamic SQL is a powerful technique on IBM i that allows you to build SQL statements at runtime instead of hardcoding them. This flexibility is essential when your SQL needs to adapt based on user input, configuration, or variable conditions. In this post, I’ll walk you through a simple RPG example that uses a parameterized dynamic SQL statement to retrieve data from a file. The Program **FREE // ************************************************************* // * Author.......: ALDO SUCCI                                 * // * Description..: Simple example of using dynamic SQL in RPG * // *************************************************************   // Declare variable to hold the customer type (3 characters) dcl-s wCLCIB0 CHAR(3);   // Declare variable to hold the customer name retrieved from the database dcl-s wCLNOM...
Read more