db2ReadLog API over an ordinary client connection. Unlike the Db2 LUW Source, it does not need IBM’s ASN Capture (SQL Replication), its control tables or the asncdc UDF. Nothing is installed on the Db2 server.
Its events and topics have the same shape as the ASN connector’s: <prefix>.<SCHEMA>.<TABLE>, with one data model per table.
For Db2 for i (AS/400, IBM i), use the IBM i Source instead.
Compared with the ASN Capture connector
Compatible versions
How it works
- Startup checks. Before reading anything, the connector checks every prerequisite below and reports all the problems at once, each with the statement that fixes it.
- Initial load. The selected tables are snapshotted over JDBC. The log position is recorded before the snapshot, once the transactions in flight at that point have ended, so no change is lost between the snapshot and the stream.
- Streaming. The connector reads the log, rebuilds each transaction and emits it only once it commits, in commit order. Rolled-back transactions, savepoint rollbacks and failed statements are never emitted.
- Resume. The connector records where to resume and what it has already emitted. After a restart, a crash or a lost connection, it resumes from there with no loss and no duplicates.
Prerequisites
All prerequisites are SQL or database configuration. The connector’s startup checks verify each one.- Archive logging on the database. With circular logging, the log is overwritten and changes are lost:
DATA CAPTURE CHANGESon each captured table. Without it, Db2 logs too little for the row to be rebuilt, and the log reader skips the table’s changes:
- A connector user with DBADM (or SYSADM).
db2ReadLogrequires it:
- Archived log files kept and retrievable for at least the connector’s longest expected downtime. After an outage, the connector reads back from the archived logs.
- A primary key on each captured table is recommended. Without one, a target has no key to apply updates and deletes by.
Recommended: larger TCP buffers
Db2 uses its own 64 KB TCP buffers by default, which limits throughput and latency whenever the connector is not next to the database. Raise them on the Db2 server, then restart the instance:AUTHENTICATION=SERVER_ENCRYPT
If the instance uses SERVER_ENCRYPT, set ALTERNATE_AUTH_ENC to AES_CMP (or AES_ONLY) on the server. Otherwise the server negotiates DES with a 256-bit Diffie-Hellman key, which current Java clients refuse. This applies to every Java Db2 client, not only Popsink.
Supported data types
GRAPHIC and VARGRAPHIC are supported in a Unicode database (code page 1208), the default since Db2 9.5. In a non-Unicode database, a table with such a column is refused.
DECFLOAT special values (NaN, sNaN, Infinity, -Infinity) are kept only when decimals are emitted as strings or doubles. When decimals are emitted as exact decimals, a special value arrives as null with a warning, and stops the connector in a NOT NULL column. The startup checks warn once per table with DECFLOAT columns in that case.
A table with an unsupported column is refused at startup, with the reason. Leave the table out, or exclude the column. Row-compressed tables (static and adaptive) and range-partitioned tables are supported; value compression is not:
Configuration
Advanced Options
Connecting through an SSH Tunnel
If the database is only reachable through a bastion host, enable SSH Tunnel and provide:
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), see
Tunnels and private connectivity.
Table operations and schema changes
A table whose rows are rewritten outside the log can only be caught up by re-snapshotting it.
Limitations
- LOB and XML columns (and
LONG VARGRAPHIC) are not decoded yet, norGRAPHICin a non-Unicode database. See Supported data types. - Recreating a table under the same name before the connector has caught up can make its older changes decode with the new definition. Let the connector catch up before dropping and recreating a captured table, or changing a column’s
CODEUNITS32length unit. - Online schema changes stop the connector instead of updating the data model.
- One log stream only. DPF and pureScale are refused at startup. Under HADR, connect to the primary: a takeover is retried and the connector resumes on the new primary through client reroute.
- At-least-once at the snapshot boundary. A row changed while the initial load runs can arrive both in the snapshot and in the stream. Targets that apply by primary key end up with the right row.
- Catching up from archived logs costs about 3 seconds per archived log file. Keep the database
ACTIVATEd: a database deactivated when its last connection closes archives its active log each time, which multiplies these files. - Large transactions are held in memory until they commit. Use Max transaction size to turn an out-of-memory into a clear error.
Performance
Measured against Db2 12.1.5 on AIX (0.25 core LPAR) from a client 28 ms away, with the TCP buffers above:
Throughput grows as round-trip time shrinks: for large backlogs, run the connector close to the database.