Static IBM i Series CDC with Gluesync: Journal Setup Guide

Source data from Db2 for IBM i

Prerequisites

To have Gluesync working on your Db2 for IBM i (former AS/400) instance you will need to have:

  • Valid user credentials with permissions to read to the source tables and respective schema;

  • Tables of whom changes need to be tracked must-have journal enabled;

  • Given user role level should be QPGMR (at least) to let Gluesync read the specified journal.

Only tables that are subject to journaling will be able to provide changes feed. Both Libraries & Journals are configurable via the respective settings at entityconfiguration level. You can find more details about its configuration below on this page.
If you require deploying additional agents on the same source database, see Deploying multiple Gluesync agents on the same source database chapter below.

Setup via Web UI

  • Hostname / IP Address: DNS record or IP Address of your server;

  • Database name: Name of your target database;

  • Username: Username with read & write access to the target tables;

  • Password: Password belonging to the given username;

  • Max connections count: Maximum number of connections the pool can instantiate.

Custom host credentials

  • Date Format: Format for date values (default: iso). Allowed values: julian, mdy, dmy, ymd, usa, iso, eur, jis;

  • Time Format: Format for time values (default: iso). Allowed values: hms, usa, iso, eur, jis;

  • Block Size (kilobytes): Size of data blocks in kilobytes (default: 32). Allowed values: 0, 8, 16, 32, 64, 128, 256, 512;

  • Use connection pool: Whether to use connection pooling (default: true);

  • Gluesync library: Name of the Gluesync library (default: GLUESYNC);

  • Gluesync journal check point: Name of the journal checkpoint table (default: JOURNALSCP).

Specific configuration

The following example shows how to apply the agent-specific configurations via Rest API.

  • Date format: (optional, defaults to NULL), the date format to be used when negotiating the JDBC driver connection. It can be any of the following:

    • julian,

    • mdy,

    • dmy,

    • ymd,

    • usa,

    • iso,

    • eur,

    • jis;

  • Time format: (optional, defaults to NULL), the time format to be used when negotiating the JDBC driver connection. It can be any of the following:

    • hms,

    • usa,

    • iso,

    • eur,

    • jis;

Setup via Rest APIs

Here following an example of calling the CoreHub’s Rest API via curl to setup the connection for this Agent.

Connect the agent

curl --location --request PUT 'http://core-hub-ip-address:1717/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
        "hostCredentials": {
        "connectionName": "myAgentNickName",
        "host": "host-address",
        "databaseName": "db_name",
        "maxConnectionsCount": 100,
        "username": "",
        "password": ""
}'

Setup specific configuration

The following example shows how to apply the agent-specific configurations via Rest API.

curl --location --request PUT 'http://core-hub-ip-address:1717/pipelines/{pipelineId}/agents/{agentId}/config/specific' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
      "configuration": {
            "dateFormat": "iso",
            "timeFormat": "iso"
      }
}'

Troubleshooting & Best Practices

Monitoring backlog and enqueued changes

To monitor how many changes are enqueued/buffered at source and waiting to be replicated, you can run the following query on the source database:

SELECT
    I.LAST_SEQUENCE_NUMBER - R.SEQUENCE_NUMBER AS ROWS_TO_READ,
    (I.LAST_SEQUENCE_NUMBER - R.SEQUENCE_NUMBER)/10000*5/60 AS MIN_LEFT
FROM GLUESYNC.JOURNALSCP R -- where GLUESYNC is the name of the given Gluesync schema/user
CROSS JOIN (
    SELECT
        MAX(LAST_SEQUENCE_NUMBER) AS LAST_SEQUENCE_NUMBER
    FROM QSYS2.JOURNAL_RECEIVER_INFO
    WHERE JOURNAL_LIBRARY = 'YOUR_JOURNAL_NAME'
        AND JOURNAL = 'YOUR_JOURNAL_LIBRARY_NAME'
) AS I;

Deploying multiple Gluesync agents on the same source database

In order to have multiple Gluesync agents working on the same source database you will need to configure them within different libraries. In order to do that, you will need to specify the library name in the agent configuration under the custom host credentials section, named Gluesync library. Gluesync automatically creates a library with this name if it doesn’t exist.

The default name for the library is GLUESYNC but you can specify any other name.

Working with Before & After images

Before & after images are a feature that allows Gluesync to track the changes that have occurred in your database and comparing them with their previous values. This enables Gluesync to apply only the changes that have occurred, saving bandwidth and processing time.

To enable that feature in your DB2 for IBM i database please contact your database administrator.