IBMi (AS/400) Source

​

Table of Contents

1. Key Features
2. How It Works
3. Core Concepts
4. Prerequisites
5. Configuration
6. Enabling Journaling
7. Granting User Permissions
8. Enabling Incremental Load
9. Estimating Change Volume
10. Housekeeping

​

Key Features

• Real-time Change Data Capture (CDC): the connector reads native IBM i journal binaries directly, capturing and streaming changes (inserts, updates, and deletes) as they occur, with end-to-end latency typically between 50–200 ms from commit to publication.
• Agentless: no software needs to be installed on the IBM i system. The connector accesses journals remotely via JT400, keeping SQL engine involvement minimal.
• Low System Overhead: lightweight binary parsing keeps CPU impact low on the source partition — typically under 2% at low volumes (<10k changes/min), ~5% at moderate volumes (~50k changes/min), and 10–15% at high volumes (100k+ changes/min) — leaving headroom for business-critical workloads.
• High Throughput: continuous streaming with exact receiver offset tracking sustains more than 50k changes/sec on mid-range Power9 systems and remains stable under burst load.
• Initial Load: the connector automatically performs an initial full table load using JDBC + SQL snapshots.
• Fault-tolerant: the connector tracks exact journal receiver offsets and resumes replication from the last known position in case of failures, ensuring data consistency and minimal downtime.

​

How It Works

1. Snapshots: initial full loads are performed over JDBC using SQL.
2. Change capture: ongoing changes are read directly from the native IBM i journal binaries attached to your physical files, via a remote procedure call (RPC) approach over JT400. Rather than polling journals through SQL, the connector streams raw journal entries continuously and tracks exact receiver offsets, minimizing load on the SQL engine and transferring changes in a compact binary format.

​

Core Concepts

• Journal Receiver: the physical storage object containing change records with before-and-after data images, metadata, timestamps, and transaction information.
• Journal: the database tracking mechanism that records changes to specific database objects, maintains metadata, and points to Journal Receivers.
• Journal Library: the repository storing journal definitions and organizing journal objects.
• Journal Receiver Library: a separate storage location for Journal Receivers, enabling better organization and maintenance.

​

Prerequisites

• The tables you want to ingest must be journaled. See Enabling Journaling
• You will need a user with the necessary permissions. See Granting User Permissions
• For incremental load support, a signal table must be created and journaled. See Enabling Incremental Load

​

Configuration

​

Advanced Options

​

Connecting through an SSH Tunnel

​

Table Selection

​

Enabling Journaling

1. Create a Journal Receiver:
   CRTJRNRCV <RECEIVER_LIB>/<RECEIVER_NAME>
   
2. Create a Journal attached to the Receiver:
   CRTJRN <JOURNAL_LIB>/<JOURNAL_NAME> <RECEIVER_LIB>/<RECEIVER_NAME>
   
3. Start journaling the tables you want to track:
   STRJRNPF <FILE_LIB>/<FILE_NAME> <JOURNAL_LIB>/<JOURNAL_NAME>
   
4. Enable before/after image capture (recommended):
   CHGJRNOBJ OBJ((<FILE_LIB>/<FILE_NAME> *FILE)) ATR(*IMAGES) IMAGES(*BOTH)
   
   Capturing both before and after images is required to correctly handle updates to Primary Key columns.

​

Granting User Permissions

GRTOBJAUT OBJ(<JOURNAL_LIB>) OBJTYPE(*LIB) USER(popsink) AUT(*EXECUTE)
GRTOBJAUT OBJ(<JOURNAL_LIB>/*ALL) OBJTYPE(*JRNRCV) USER(popsink) AUT(*USE)
GRTOBJAUT OBJ(<JOURNAL_LIB>/<JOURNAL_NAME>) OBJTYPE(*JRN) USER(popsink) AUT(*USE *OBJEXIST)
GRTOBJAUT OBJ(<RECEIVER_LIB>) OBJTYPE(*LIB) USER(popsink) AUT(*EXECUTE)
GRTOBJAUT OBJ(<RECEIVER_LIB>/*ALL) OBJTYPE(*FILE) USER(popsink) AUT(*USE)

• *EXECUTE authority on the journal library and the receiver library
• *USE and *OBJEXIST authority on the journal object
• *USE authority on the journal receivers

​

Enabling Incremental Load

1. Create the signal table:
   CREATE TABLE SCHEMA.POP_SIGNAL (
     ID VARCHAR(128) NOT NULL,
     TYPE VARCHAR(32) NOT NULL,
     DATA VARCHAR(2048)
   )
   
2. Journal this table in the same journal as the tracked tables:
   STRJRNPF <FILE_LIB>/POP_SIGNAL <JOURNAL_LIB>/<JOURNAL_NAME>
   
3. Grant the user write permissions on this table (this table only — no write access is needed anywhere else).

​

Estimating Change Volume

SELECT J.OBJECT,
  COUNT(CASE WHEN J.JOURNAL_ENTRY_TYPE = 'PT' THEN 1 END) AS INSERT_COUNT,
  COUNT(CASE WHEN J.JOURNAL_ENTRY_TYPE = 'UP' THEN 1 END) AS UPDATE_COUNT,
  COUNT(CASE WHEN J.JOURNAL_ENTRY_TYPE = 'DL' THEN 1 END) AS DELETE_COUNT
FROM TABLE(QSYS2.DISPLAY_JOURNAL('<JOURNAL_LIB>', '<JOURNAL_NAME>')) AS J
WHERE J.ENTRY_TIMESTAMP >= CURRENT_TIMESTAMP - 24 HOURS
GROUP BY J.OBJECT;

​

Housekeeping

​

Journal Receiver Retention

1. Delete journal receivers older than 7 days:
   DLTJRNRCV JRNRCV(<RECEIVER_LIB>/<RECEIVER_NAME>) DLTOPT(*IGNINQMSG) SELECT(*OLD) RETENTION(7)
   
   Wildcard patterns are supported (e.g. RECEIVER_NAME*). You can schedule this command periodically using WRKJOBSCDE.
2. Monitor active receivers:
   WRKJRNRCV <JOURNAL_LIB>/<JOURNAL_NAME>
   
3. Monitor receiver disk usage:
   DSPJRNRCVA
   

​

Stopping and Restarting CDC

1. Stop CDC connectivity:
   ENDTCPIFC INTERFACE(<CDC_SERVER_IP>)
   
2. Restart CDC connectivity:
   STRTCPIFC INTERFACE(<CDC_SERVER_IP>)
   

​

Disabling Journaling

1. Stop journaling a table:
   ENDJRNPF FILE(<FILE_LIB>/<FILE_NAME>) JRN(<JOURNAL_LIB>/<JOURNAL_NAME>)