Search docs.molo17.com
    A newer version of this documentation is available.
    View Latest (v2.3)

    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.
    Tables meant to be replicated must have journaling enabled with BOTH, meaning before and after image must be enabled.
    Logical files and views are snapshot-only

    Logical files and views are supported for snapshot only (reading them is available starting from 2.1.7.1). CDC isn’t available on IBM i logical files or SQL views, because the journal records changes against the underlying physical files.

    To capture changes, create the entity on the underlying physical file instead, journaled with IMAGES(*BOTH) as required above. To find it, run DSPFD FILE(<library>/<logical file>) TYPE(*MBR) to see the physical file that the logical file is based on, or run DSPDBR on a physical file to list the logical files built over it. If the logical file uses select/omit criteria, you can recreate them where possible with data filtering: filters are inclusive, so a row is replicated only when it matches all the conditions.

    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.

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

    • Enable DDS Discovery: Whether to enable DDS discovery (default: false);

    • Use database timezone: Whether to apply database timezone to the source incoming data (default: false, leaving it untouched);

    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 Core Hub’s Rest API via curl to setup the connection for this Agent.

    Connect the agent

    curl --location --request PUT 'http://core-hub-ip-address/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/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.

    Usage of RRN vs Logical Keys

    RRN stands for Record Number and it is a unique identifier for each row in a table. It is a numeric value that is assigned to each row when it is inserted into the table. This record is subject to change if the table is reorganized, leading the Gluesync agent to fail to apply the changes to the target tables, as the primary key will not match or matches a different row, which is even worse.

    We are aware that multiple users are bound by strict company policies not to alter target database schemas with new Primary Keys. Using a Logical Key in Gluesync solves this without violating policies. A Logical Key is a purely internal Gluesync concept; it tells the engine how to uniquely identify a row during transit, but it will not execute any ALTER TABLE or modify the physical Primary Key constraints on your target database.

    We highly recommend using a logical key (or selecting all columns as a unique row identifier) instead of RRN. This immediately addresses the issue of Duplicate Key errors in target staging tables and prevents any data loss due to table reorganization.

    Gluesync supports RRN (Record Number) as a primary key from version 2.1.10.10, and can be selected as a column from the field editor during entity configuration. Gluesync’s approach to logical keys has been successfully adopted by multiple customers and has been proven to be more reliable and less prone to errors, compared to RRN. We highly recommend to use a logical key, such as a unique identifier or a combination of columns that are unique to each row, in case you don’t have a column or a combination of columns that are unique to each row, you just need to select all columns of the table as a unique row identifier. To read more about logical keys, please refer to the Logical keys documentation.