> ## Documentation Index
> Fetch the complete documentation index at: https://docs.popsink.com/llms.txt
> Use this file to discover all available pages before exploring further.

# IBMi (AS/400) Source

The IBMi (AS/400) Source Connector integrates with IBM i, formerly known as IBM AS/400 and iSeries, a powerful and reliable midrange computer system widely used in enterprise environments. This connector enables the extraction and streaming of data from IBMi systems, which are known for hosting critical business applications and databases in industries such as manufacturing, finance, and healthcare. It allows organizations to tap into their legacy IBMi data, including information from DB2 for i databases and various file systems. By connecting IBMi to modern data pipelines, this connector facilitates data modernization efforts, enabling real-time analytics, cross-platform data integration, and the ability to leverage legacy data in cloud-based or hybrid environments.

## Table of Contents

1. [Compatible versions](#compatible-versions)
2. [Key Features](#key-features)
3. [How It Works](#how-it-works)
4. [Core Concepts](#core-concepts)
5. [Prerequisites](#prerequisites)
6. [Configuration](#configuration)
7. [Enabling Journaling](#enabling-journaling)
8. [Granting User Permissions](#granting-user-permissions)
9. [Estimating Change Volume](#estimating-change-volume)
10. [Housekeeping](#housekeeping)

## Compatible versions

|                    |                                                                                        |
| ------------------ | -------------------------------------------------------------------------------------- |
| **IBM i (OS/400)** | 7.3, 7.4, 7.5, 7.6                                                                     |
| **Database**       | Db2 for i, as shipped with the OS release                                              |
| **Connectivity**   | JTOpen (JT400) 21.0.4 over the IBM i host servers — nothing installed on the partition |
| **Journals**       | Journaled physical files with `*BOTH` images                                           |
| **CDC engine**     | Debezium 3.5, Popsink build of the Db2 for i connector                                 |

<Note>IBM i 7.2 and earlier are out of IBM support and are not covered. Date and time columns are decoded from `QSYS2.SYSCOLUMNS2`, whose DDS format columns were added in IBM i 7.3 TR11 / 7.4 TR5; below those Technology Refresh levels the decoder assumes the `*ISO` format, so files using `*MDY`, `*EUR` or another DDS format need a TR at or above those levels.</Note>

## 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

The connector combines two mechanisms:

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](#enabling-journaling)
* You will need a user with the necessary permissions. [See Granting User Permissions](#granting-user-permissions)
* Network access from Popsink to the IBM i host on the ports listed below. [See Network Access](#network-access)

### Network Access

The connector reaches the IBM i system over JT400, which uses the DB2 for i (DRDA/DDM) listener and the IBM i host servers. Allow outbound TCP traffic from Popsink to the source host on the following ports:

| Port   | Service                             | Purpose                                                   |
| ------ | ----------------------------------- | --------------------------------------------------------- |
| `446`  | DRDA / DDM                          | DB2 for i over TCP/IP — used for JDBC snapshots           |
| `449`  | Server mapper (`as-svrmap`)         | Resolves the host server ports the client must connect to |
| `8471` | Database server (`as-database`)     | SQL and catalog access                                    |
| `8473` | File server (`as-file`)             | Access to journal receiver objects                        |
| `8475` | Remote command server (`as-rmtcmd`) | Runs the journal retrieval program calls                  |
| `8476` | Sign-on server (`as-signon`)        | User profile authentication                               |

<Note>
  If the system is configured for TLS, use `448` for the secure DDM/DRDA listener and the `947x` range (`9471`, `9473`, `9475`, `9476`) instead of `847x` for the host servers.
</Note>

If the IBM i system is only reachable through a bastion host, see [Connecting through an SSH Tunnel](#connecting-through-an-ssh-tunnel).

## Configuration

| Field                | Required | Description                                                                         |
| -------------------- | -------- | ----------------------------------------------------------------------------------- |
| **Host**             | Yes      | Hostname or IP address of the IBM i system                                          |
| **Port**             | —        | The connector uses port `446` for IBM i access (fixed, not exposed as a form field) |
| **User / Password**  | Yes      | IBM i user profile and password                                                     |
| **Schema (Library)** | Yes      | IBM i library containing the tables to replicate (e.g. `MYLIB`)                     |

### Advanced Options

| Field                    | Default | Description                                                                                                                                                                                    |
| ------------------------ | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Initial Load**         | `true`  | Snapshot existing rows before streaming journal changes                                                                                                                                        |
| **Poll Interval (ms)**   | `2000`  | How often the connector polls for new journal activity                                                                                                                                         |
| **Unicode Escape**       | `false` | Apply Unicode escape handling for special characters in field names                                                                                                                            |
| **Transaction Buffered** | `false` | Buffer transactions and emit changes only after commit, avoiding partial or uncommitted rows downstream                                                                                        |
| **Incremental Load**     | `false` | Legacy option, kept while the switch is still present in the form. Leave it disabled: snapshots run in blocking mode and are orchestrated by Popsink, with nothing required on the IBM i side. |

## Connecting through an SSH Tunnel

If the database is only reachable through a bastion host, enable **SSH Tunnel** and provide:

| Field               | Required | Description                                     |
| ------------------- | -------- | ----------------------------------------------- |
| **SSH Host**        | Yes      | Hostname or IP of the bastion host              |
| **SSH Port**        | Yes      | SSH port (default `22`)                         |
| **SSH User**        | Yes      | User to authenticate as on the bastion          |
| **SSH Private Key** | Yes      | **Base64-encoded** private key for the SSH user |

The **Host** and **Port** above stay as the database is addressed *from the
bastion*. For key generation, bastion hardening and the other connectivity
options (IPsec VPN, Tailscale, ngrok), see
[Tunnels and private connectivity](/deployment/tunnels).

### Table Selection

After a successful credential check, pick the tables to replicate from the discovered list. Only **journaled** tables can be selected — the list indicates the journaling status of each table. The selection is stored as a comma-separated whitelist.

## Enabling Journaling

To capture changes, the tables you want to track must be journaled. If your tables are already journaled, you can skip to [Granting User Permissions](#granting-user-permissions).

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

The connector's user profile needs the following authorities. No SQL-level SELECT authority is needed on system UDTFs.

```
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)
```

In summary, the user requires:

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

No write authority is required anywhere: the connector reads journal receivers and runs SQL snapshots, and creates no object on the source system.

## Estimating Change Volume

You can estimate your daily change volume by counting journal entries over the last 24 hours:

```sql theme={null}
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

Journal receivers take up disk space, so it is best practice to delete old ones regularly. There is a tradeoff between retention and the operational resilience of your replication pipeline: longer retention gives you more time to recover from outages before having to resync, while shorter retention takes up less space. We recommend a maximum retention of 7 days, and a minimum of 1 day depending on your requirements.

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>)
   ```