IBM iSeries logo IBM i Series CDC with Gluesync: Journal Setup Guide

Source data from Db2 for IBM i

Sourcing data from DB2 for IBM i series uses the QjoRetrieveJournalEntries API to read journal entries.

For a detailed explanation of the journal reader pipeline, caching, and checkpoint mechanics, see the journal data capture architecture page.

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 in BOTH mode (before and after image);

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

  • Ability to create a Library called GLUESYNC (this library name is user customizable), or an existing library with that name in which the user can create and delete objects. Gluesync creates the library only if it does not exist yet. See Contents of the Gluesync library below.

  • List of permissions for Journal APIs:

    • Authorities and Locks

      • Journal Authority: *USE

      • Journal Library Authority: *EXECUTE

      • Journal Receivers Authority: *USE

      • Journal Receivers Library’s Authority: *EXECUTE

      • Non-Integrated File System Object Authority (if specified on Key 16 (file) or Key 17 (object)): *USE

      • Non-Integrated File System Object Library Authority: *EXECUTE

      • Integrated File System Object Authority (if specified on Key 18 (object path) or Key 19 (object file identifier)): *R (also *X if object is a directory and *ALL is specified for the directory subtree key)

      • Directory Authority (for each directory preceding the last component in the path name): *X

Only tables that are subject to journaling will be able to provide changes feed. Both Libraries & Journals are configurable via the respective settings at entity configuration 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 mode, meaning before and after image must be enabled.

Contents of the Gluesync library

The Gluesync library holds service objects only, never application data:

  • DDSKEYS: the output file of DSPFD TYPE(*ACCPTH), used to read the DDS keys of files without an SQL primary key. It is written only when Enable DDS Discovery is on;

  • one SQL alias for each entity that reads a member of a multi-member physical file, named after the entity ID. Such entities can be created only when Enable multi-member physical file support is on;

  • the DSPJRN output files of the legacy journal reader, only when Legacy mode journal reader is enabled. The default reader calls the IBM i journal APIs (QjoRetrieveJournalEntries, QjoRetrieveJournalInformation and QjoRtvJrnReceiverInformation in QJOURNAL) and writes nothing to the library.

No Gluesync program is stored in the library, and Gluesync does not run any job on the IBM i: all the activity starts from the agent through the standard host servers (database server jobs QZDASOINIT and remote command/program call server jobs QZRCSRVS).

Versions from 2.1.11.0 to 2.2.4.1 compiled two CL programs in this library, LASTJRNSEQ and OLDSTPFSEQ, to read the first and last sequence number of a journal. Since 2.2.4.2 they are no longer created, and Gluesync deletes them at startup if a previous version left them in the library.

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 used by versions earlier than 2.1.9.0 (default: JOURNALSCP). Gluesync reads it only once, to migrate the stored journal position when upgrading from those versions, and then drops it. Newer versions do not store the journal position on the IBM i;

  • 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

Gluesync publishes the backlog of each entity as Prometheus metrics (see the Per-entity CDC lag section of the Metrics reference). For an IBM i source:

  • gluesync_source_pending_rows counts the journal entries not read yet. The count covers every entry of the journal, not only the entries of the entity, so gluesync_source_pending_rows_is_upper_bound is 1;

  • gluesync_source_read_timestamp_seconds is the time of the last journal entry read, which tells how far behind the reader is;

  • gluesync_cache_pending_rows and gluesync_cache_lag_milliseconds report the changes already read from the journal and still waiting to be delivered to the target, for the entities served by the shared journal reader.

gluesync_source_lag_milliseconds is not published for IBM i: dating the oldest unread entry would require scanning the journal.

The journal position is not stored on the IBM i, so the backlog cannot be computed with a query on the source database.

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.

EBCDIC encoding

By default, Gluesync won’t automatically convert EBCDIC encoded columns to UTF-8 when reading data from the source, this is done to preserve binary data integrity. However, if you want to enable this feature, you can do so by setting the translate binary option to true in the agent configuration (under "Custom host credentials"); in this case, Gluesync will convert EBCDIC encoded columns to UTF-8, e.g. in the case you have a CHAR FOR BIT DATA column stored in EBCDIC format.

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.