Skip to content

IBM i Data Replication

Overview

InfoConnect Hub 3.0.0 and later replicates IBM i data directly to JDBC-compliant databases, such as MySQL, PostgreSQL and SQL Server. No messaging or event middleware is required. The Hub works directly with InfoCDC, the lightweight Change Data Capture (CDC) agent for IBM i.

Use this approach to replicate IBM i tables to a database when you have no complex integration requirements and no messaging or event infrastructure. If a message broker is part of your architecture, see Example: DB2 Replication with InfoCDC and the AWS Stack.

In the web interface the replication job is a Data Flow. In the Admin API and in JSON it is a DQ poller (/admin/dqpollers) with pollerType set to Source. This page uses the term Data Flow.

How it works

The following diagram shows the replication flow:

Replication flow: IBM i database, InfoCDC and its data queue, the Hub poller and JDBC connection, and the target databases

Direct replication combines InfoCDC, the Hub data queue (DQ) poller and a JDBC sink:

  1. Data capture. InfoCDC captures changes to IBM i DB2 tables and sends them to replication data queues.
  2. Data queue polling. The Hub listens to the data queues for new messages. When a change event arrives, the Hub converts it from the internal fixed-position message format into JSON and determines the key values.
  3. Direct database integration. The Hub writes the change events to the target database:
    • The Hub checks whether the target table exists and, if needed, creates it according to the InfoCDC table and key configuration.
    • The Hub writes the change events (insert, update, delete) directly to the target database through JDBC. An update that matches no row in the target table is written as an insert.
    • No messages are routed through additional middleware, such as brokers or event platforms.

InfoCDC as a source

An InfoCDC Data Flow is an outbound Data Flow (pollerType = Source) that reads an InfoCDC replication data queue, for example CDCDQ1581 in library INFOCDCDTA. It is enabled with the infoCDCConfigured property. In the web interface it is the InfoCDC Source checkbox in the main configuration of the Data Flow (default: selected). Through the API the default is false, so set "infoCDCConfigured": true explicitly.

When the option is enabled, the Hub does the following for each message read from the data queue:

  1. Reads the InfoCDC message header: the source table name (SYSTBLNAM), the table library (SYSTBLSCM), the format file name (SYSFTBLNAM) and the format file library (SYSFTBLSCM).
  2. If the Data Flow's dqFormatFileName and dqFormatFileLibrary are auto, uses the format file named in the message to convert the fixed-position record into JSON.
  3. Retrieves the key columns configured in InfoCDC for the table (once per table, the result is cached). These columns are used to identify the row for updates and deletes, and to keep only the latest event per row.

Without this option the key is taken from the data queue key string.

Each JSON message carries EVENTTYP (INSERT, UPDATE, DELETE or SNAPSHOT), SYSTBLNAM, SYSTBLSCM, SYSFTBLNAM, SYSFTBLSCM, JRNSEQNO and the table columns. For columns that can be null, an additional <COLUMN>_NULLIND indicator is included (1 = null).

Format file default: auto

dqFormatFileName and dqFormatFileLibrary default to auto. If the fields are omitted or null, they default to auto; the web interface sends null for blank format file fields. An empty string in the Admin API means no format file. With auto, the format file name and library are read from each InfoCDC message, so one Data Flow can read changes of many tables, each with its own record layout.

auto is resolved only when infoCDCConfigured is true. To read an InfoCDC queue, leave the format file fields blank (or auto) and select InfoCDC Source. To use one fixed format file for all messages, enter its name and library explicitly.

Note

The InfoCDC replication workflow on IBM i (journal listener and data queue builder jobs) runs independently of the Hub Data Flow. Starting a Data Flow in the Hub does not start it.

Configuration example

To create the Data Flow in the web interface:

  1. In the navigation bar, select Data Flows.
  2. Select Create Data Flow.
  3. Choose the direction Outbound (IBM i to cloud services).

    Create New Data Flow page with the Outbound and Inbound options

  4. Fill in the form fields, or paste the JSON into the JSON tab.

    Create Data Flow form

To create the Data Flow through the Admin API, send the request to POST /admin/dqpollers.

Keep the following in mind:

  • connectionName is required. It is the name of the IBM i connection the Data Flow uses.
  • The Data Flow name (pollerName) may contain only letters, digits and the - character. Underscores and other special characters are not allowed.
  • If status is not specified, the Data Flow is created as INACTIVE (this is also the case for Data Flows created in the web interface). Start it with the Start button, with POST /admin/dqpollers/{pollerName}/start, or pass "status": "ACTIVE" when creating it.
  • jdbcProperties.username and jdbcProperties.password must be encrypted values. Encrypt them with POST /admin/encryption, passing for example {"password": "plain-text-password"}; the response contains the encrypted value under the same key. The web interface encrypts these fields automatically, see Encrypting secrets.
  • batchSize is the number of messages the Data Flow reads and writes in one batch. Use a value of 1 or more. A value of 0 or less reads one message per cycle, which is slow for a busy queue.

For FAILED and INVALID Data Flows and how to recover them, see Data Flow States and Errors.

Default mode: automatic table creation

Sample JSON configuration for a Data Flow
{
    "pollerName": "db-poller",
    "connectionName": "ibmi-prod",
    "pollerType": "Source",
    "dqName": "CDCDQ1581",
    "libraryName": "INFOCDCDEM",
    "infoCDCConfigured": true,
    "dqFormatFileName": "auto",
    "dqFormatFileLibrary": "auto",
    "dqFormatExceptionAction": "place back into queue",
    "batchSize": 100,
    "threadCount": 20,
    "status": "ACTIVE",
    "sinkType": "JDBC",
    "sinkTypeProperties": {
        "jdbcProperties": {
            "driverClassName": "com.mysql.cj.jdbc.Driver",
            "maxPoolSize": 20,
            "minPoolSize": 3,
            "jdbcUrl": "jdbc:mysql://localhost:3306/testDB",
            "username": "GtflCbDpwstp9hDlUl2g3av81i38WCYnVtG452GN6gDjJTaWmmhgexwT/A==",
            "password": "fkzxAm+GBqLYDZTAMsSy1HG+Vi0I9CgDOBo18ADbuYbrJWJ3itFJsZzzqPtS/p0="
        }
    }
}

The JDBC sink properties are set in sinkTypeProperties.jdbcProperties:

Property Description
driverClassName JDBC driver class name, required. See Supported databases and drivers.
jdbcUrl JDBC connection URL of the target database, required.
username, password Encrypted credentials, required.
maxPoolSize, minPoolSize Connection pool size, required. If they are omitted, the Data Flow becomes INVALID. A value of 0 or less is replaced by the default (10 for maxPoolSize, 2 for minPoolSize).
dynamicDataPublishing Optional, default false. See Replicating many tables with one Data Flow.
insertSql, updateSql, deleteSql, selectSql Optional custom SQL. See Custom SQL templates.

If the driver is not found, the credentials are rejected, or the JDBC URL is not valid, the Data Flow becomes INVALID.

How the JDBC sink writes data

This section describes the default mode, that is, when no custom SQL is configured.

Batching

The Data Flow reads up to batchSize messages per cycle and writes the whole batch through one database connection in a single transaction. The batch is committed or rolled back as a unit.

Inside one batch only the latest event for each row key is applied (the event with the highest JRNSEQNO). The row key is the table together with the values of its key columns.

Supported events are INSERT, SNAPSHOT (written as an insert), UPDATE and DELETE. An UPDATE that matches no row in the target table is written as an insert. Events of any other type are skipped.

Automatic table creation

When the first message for a table arrives, the Hub reads the InfoCDC format file and creates the target table if it does not exist (CREATE TABLE IF NOT EXISTS; on SQL Server a guarded IF NOT EXISTS ... CREATE TABLE). The target table is named after the IBM i table system name (SYSTBLNAM), without the library, and is created in the default schema of the JDBC URL.

IBM i column types are mapped as follows:

IBM i type Target type
Binary 1 byte TINYINT
Binary 2 bytes SMALLINT
Binary 4 bytes INTEGER
Binary 8 bytes BIGINT
Byte array VARBINARY(n)
Date DATE
Time TIME
Timestamp TIMESTAMP
Float (4 bytes) REAL
Float (8 bytes) DOUBLE
Packed decimal, zoned decimal DECIMAL(n, 0) (the scale is always 0)
Text, variable-length text VARCHAR(n)

Other IBM i data types are not supported for automatic table creation.

Right after creating a table, the Hub creates a non-unique index named primary_key on the table's key columns. If the table has no key columns, no index is created. No primary key or unique constraint is created.

An error caused by the table or index already existing is ignored. The Hub recognizes the messages Duplicate key name (MySQL) and already exists (PostgreSQL, H2, SQL Server).

To find out whether the table exists, the Hub compares table names case-insensitively, because some databases (for example PostgreSQL) fold unquoted names to lower case.

Table and column names are not quoted in the generated SQL.

Duplicate keys on insert

If a batched insert fails with the SQL Server message Cannot insert duplicate key row in object, the Hub repeats that group of rows one row at a time: each row is inserted, and a row that already exists is updated by its key. Only this SQL Server message is recognised. On other databases a duplicate-key error is handled like any other SQL error (see below). Tables created by the Hub have no unique constraint, so this applies mainly to tables you created yourself with a primary key or unique index.

This fallback is not used with a custom insertSql.

Errors and retries

Error type Behavior
Transient errors: connection pool timeout, deadlock rollback, recoverable SQL errors, socket or connection timeouts, unexpected end of stream The batch is retried until it succeeds, waiting 60000 ms between attempts by default.
Any other SQL error The batch is rolled back and the Data Flow changes to FAILED with the message Non-retryable error processing batch of N message(s): followed by the database error. The failed batch is saved to the failed records folder.

See Data Flow States and Errors for how to reset a FAILED Data Flow and for the failed records settings.

Replicating many tables with one Data Flow

One InfoCDC data queue can carry changes of many tables. To replicate all of them with a single Data Flow, use the InfoCDC source and leave the format file fields as auto (see InfoCDC as a source). The Hub then creates each target table from the format file named in the message, and writes each message to the table named by SYSTBLNAM.

The same per-message behaviour is also switched on explicitly by the dynamicDataPublishing property in jdbcProperties. When it is true, the format file used to create each target table is read from the message even if a fixed dqFormatFileName is configured. The default is false.

"jdbcProperties": {
    "driverClassName": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    "jdbcUrl": "jdbc:sqlserver://dbhost:1433;database=replica;encrypt=true;trustServerCertificate=true",
    "username": "<encrypted-username>",
    "password": "<encrypted-password>",
    "maxPoolSize": 10,
    "minPoolSize": 2,
    "dynamicDataPublishing": true
}

Note

Set dynamicDataPublishing in the JSON tab of the web interface or through the API. It has no effect when custom SQL is configured, because the Hub does not create tables in that mode.

For routing messages to a separate queue, topic or event hub per table on message brokers, see Dynamic data publishing.

Custom SQL templates

By default, the Hub generates the SQL statements itself. If the target table has a different structure than the IBM i table, or you need other statements, you can provide your own SQL for each operation in jdbcProperties:

Property Used for
insertSql Insert and snapshot events, and the insert half of an upsert.
updateSql Update events.
deleteSql Delete events.
selectSql An existence check run before an update. It takes effect only together with updateSql.

Each property is optional and independent. A property that is empty or not set uses the statement generated by the Hub; for example, you can set only insertSql.

Syntax

Use ${FIELD} to insert a value from the replicated message. FIELD is the column name as in the InfoCDC format file, for example ID or NAME. It is case-sensitive, and may contain letters, digits, _ and # (for example ${ORODR#}). The _NULLIND indicator fields cannot be used; a null column arrives as a null value.

Each placeholder is replaced by a JDBC bind parameter (?) in a prepared statement, so values are never concatenated into the SQL. Placeholders are bound in the order they appear and can be repeated.

INSERT INTO customers (customer_id, first_name) VALUES (${ID}, ${NAME})
UPDATE customers SET first_name = ${NAME} WHERE customer_id = ${ID}
DELETE FROM customers WHERE customer_id = ${ID}
SELECT 1 FROM customers WHERE customer_id = ${ID}

Quoting rules:

  • Write the placeholder without quotes, for example WHERE id = ${ID}.
  • If you write it in quotes ('${ID}', "${ID}", `${ID}` or N'${ID}'), the quotes are removed and the value is still bound as a parameter.
  • A placeholder inside a longer literal, such as 'abc${ID}', is not valid.

Values are bound according to their JSON type: integer, decimal, text, boolean or null. Array and object values are not supported. Text values are bound exactly as received, without trimming, so IBM i CHAR columns can carry trailing blanks. (The statements the Hub generates itself still trim text values.)

Validation

When the Data Flow is created or updated, a template is rejected with Invalid or unmatched ${FIELD} placeholder in SQL template if it contains a malformed placeholder such as ${123} or ${ID. In the web interface the message is Invalid or unmatched ${FIELD} placeholder.

Other problems are found when the first batch runs, and make the batch fail (the Data Flow changes to FAILED):

  • The number of placeholders does not match the number of bind parameters, typically because a placeholder is quoted inside a string literal. The message starts with SQL template produced N ${FIELD} value(s) but M JDBC bind parameter(s).
  • A placeholder names a field that is not in the message: Message field not found for placeholder: ${NAME}.

How the templates are applied

  1. If any of insertSql, updateSql or deleteSql is set, the Hub does not create the target table or its index. The table must already exist. Setting only selectSql does not switch to this mode.
  2. Each batch is processed in this order: updates, inserts, deletes. All of them run in one database transaction, which is committed at the end and rolled back on error.
  3. An update with selectSql set: the select runs for each row. If it returns a row, updateSql runs. If it returns no row, the event is turned into an insert when insertSql is set; if insertSql is not set, the row is not written.
  4. An update without selectSql: updateSql runs. If it affects no rows and insertSql is set, insertSql runs for that row.
  5. Inserts and snapshot events run insertSql as a JDBC batch. The duplicate-key fallback described above is not applied.
  6. Within one batch only the latest event for each row key is applied.
  7. selectSql is used only for update events.

Web interface

On the Create Data Flow and Update Data Flow pages, with Sink Type set to JDBC, open the Advanced SQL configuration accordion. It has four text areas: Insert SQL, Update SQL, Delete SQL and Select SQL. All are optional.

Example: custom SQL

Sample JSON configuration with custom SQL
{
    "pollerName": "customers-to-mysql",
    "connectionName": "ibmi-prod",
    "pollerType": "Source",
    "dqName": "CDCDQ1581",
    "libraryName": "INFOCDCDTA",
    "infoCDCConfigured": true,
    "batchSize": 100,
    "threadCount": 5,
    "sinkType": "JDBC",
    "sinkTypeProperties": {
        "jdbcProperties": {
            "driverClassName": "com.mysql.cj.jdbc.Driver",
            "jdbcUrl": "jdbc:mysql://dbhost:3306/testdb",
            "username": "<encrypted-username>",
            "password": "<encrypted-password>",
            "maxPoolSize": 10,
            "minPoolSize": 2,
            "insertSql": "INSERT INTO customers (customer_id, first_name) VALUES (${ID}, ${NAME})",
            "updateSql": "UPDATE customers SET first_name = ${NAME} WHERE customer_id = ${ID}",
            "deleteSql": "DELETE FROM customers WHERE customer_id = ${ID}",
            "selectSql": "SELECT 1 FROM customers WHERE customer_id = ${ID}"
        }
    }
}

The customers table must already exist in the target database. The Data Flow is created as INACTIVE; start it with POST /admin/dqpollers/customers-to-mysql/start.

Supported databases and drivers

InfoConnect Hub includes the following JDBC drivers:

Database driverClassName
MySQL com.mysql.cj.jdbc.Driver
PostgreSQL org.postgresql.Driver
Microsoft SQL Server com.microsoft.sqlserver.jdbc.SQLServerDriver
H2 Included with the Hub