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;

    • Create GLUESYNC schema/library; (this library name is user customizable)

    • Create file/table in the GLUESYNC library;

    • Read/write file/table in the GLUESYNC library;

    • Modify the GLUESYNC library journal to delete old receivers via the CHGJRN command;

    • Read from journals via the DSPJRN command;

    • Modify tables in the GLUESYNC library via the CHGPF command;

    • Delete table in the GLUESYNC library via the DLTF command;

  • 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.

Journal library & Journal name

Starting from 2.0.10 Gluesync automatically detects the journal library and journal name from the source database.

If you are using an older version of Gluesync, you can specify from which Journal library and which Journal name to look for please follow the instructions below:

1) From the objects browser tab (while on the setup wizard or by editing the desired entity) click on the settings button

Gluesync object browser

2) Under the entity settings tab, look for the source settings at the top and edit the respective fields Journal name and Journal library, as shown in the picture below:

Source settings

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

Suggested user profile settings under IBM i

                                      User Profile Display - *BASIC

 User Profile . . . . . . . . . . . . . . . :   GLUESYNC01
 Previous Sign-on . . . . . . . . . . . . . :   05/15/25  12:38:49
 Invalid Password Verifications . . . . . . :   0
 Status . . . . . . . . . . . . . . . . . . :   *ENABLED
 Password Last Changed Date . . . . . . . . :   02/05/25  08:57:22
 Password is *NONE . . . . . . . . . . . . .:   *NO
 Password Expiration Interval . . . . . . ..:   *NOMAX
 Password Set to Expired via Command . . . .:   *NO
 Password Change Block . . . . . . . . . . .:   *SYSVAL
 Local Password Management . . . . . . . . .:   *YES
 Maximum Sign-on Attempts . . . . . . . . . :   *SYSVAL
 User Class . . . . . . . . . . . . . . . . :   *USER
 Creation Date/Time . . . . . . . . . . . . :   04/10/24  10:05:15
 Created by User . . . . . . . . . . . . . .:   PROBAS
 Modification Date/Time . . . . . . . . . . :   05/15/25  12:38:49
 Last Used Date . . . . . . . . . . . . . . :   05/15/25
 Restore Date/Time . . . . . . . . . . . . .:   10/28/24  14:13:37
 User Expiration Date . . . . . . . . . . . :   *NONE
 User Expiration Interval . . . . . . . . . :   *NONE
 User Expiration Action . . . . . . . . . . :   *NONE
 Special Authority . . . . . . . . . . . . .:   *NONE
 Group Profile . . . . . . . . . . . . . . .:   QPGMR
 Owner . . . . . . . . . . . . . . . . . . .:   *GRPPRF
 Group Authority . . . . . . . . . . . . . .:   *NONE
 Group Authority Type . . . . . . . . . . . :   *PRIVATE
 Supplemental Groups . . . . . . . . . . . .:   *NONE
 Assistance Level . . . . . . . . . . . . . :   *SYSVAL
 Current Library . . . . . . . . . . . . . . :   *CRTDFT
 Initial Program . . . . . . . . . . . . . . :   BAK010C
   Library . . . . . . . . . . . . . . . . . :     PROBAS
 Initial Menu . . . . . . . . . . . . . . . :   MAIN
   Library . . . . . . . . . . . . . . . . .:     *LIBL
 Limit Capabilities . . . . . . . . . . . . :   *NO
 Text . . . . . . . . . . . . . . . . . . . :   GLUESYNC
 Sign-on Information Display . . . . . . . .:   *SYSVAL
 Device Session Limit . . . . . . . . . . . :   *SYSVAL
 Keyboard Buffering . . . . . . . . . . . . :   *SYSVAL
 Storage Information:
   Maximum Storage Allowed . . . . . . . . . :   *NOMAX
   Storage Used . . . . . . . . . . . . . . .:   728
   Storage Used on Independent ASP . . . . . :   *NO
 Maximum Scheduling Priority . . . . . . . . :   3
 Job Description . . . . . . . . . . . . . . :   QDFTJOBD
   Library . . . . . . . . . . . . . . . . . :     QGPL
 Accounting Code . . . . . . . . . . . . . . :
 Message Queue . . . . . . . . . . . . . . . :   GLUESYNC01
   Library . . . . . . . . . . . . . . . . . :     QUSRSYS
 Message Queue Delivery . . . . . . . . . . .:   *NOTIFY
 Message Queue Severity . . . . . . . . . . .:   00
 Output Queue . . . . . . . . . . . . . . . .:   *WRKSTN
   Library . . . . . . . . . . . . . . . . . :
 Print Device . . . . . . . . . . . . . . . .:   *WRKSTN
 Special Environment . . . . . . . . . . . . :   *SYSVAL
 Attention Program . . . . . . . . . . . . . :   *SYSVAL
   Library . . . . . . . . . . . . . . . . . :

 Sort Sequence . . . . . . . . . . . . . . . :   *SYSVAL
   Library . . . . . . . . . . . . . . . . . :
 Language Identifier . . . . . . . . . . . . :   *SYSVAL
 Country or Region Identifier . . . . . . . .:   *SYSVAL
 Coded Character Set Identifier . . . . . . .:   *SYSVAL
 Character Identifier Control . . . . . . . .:   *SYSVAL
 Local Job Attributes . . . . . . . . . . . .:   *DATFMT
                                                *DECFMT
 Locale . . . . . . . . . . . . . . . . . . .:   *SYSVAL
 User Options . . . . . . . . . . . . . . . .:   *NONE
 Object Auditing Value . . . . . . . . . . . :   *NONE
 Action Auditing Values . . . . . . . . . . .:   *NONE
 User ID Number . . . . . . . . . . . . . . :   751
 Group ID Number . . . . . . . . . . . . . . :   *NONE
 User Entitlement Required . . . . . . . . . :   Yes
 Authorization Collection Active . . . . . . :   No
 Authorization Collection Repository
   Exists . . . . . . . . . . . . . . . . . .:   No
 Main address . . . . . . . . . . . /home/GLUESYNC01

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.