Search docs.molo17.com

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

    Source data from Db2 for IBM i

    How-to guides

    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, and *OBJEXIST (see Authorities of the Gluesync user)

        • 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.
    Logical files and views are snapshot-only

    Logical files and views are supported for snapshot only. CDC isn’t available on IBM i logical files or SQL views, because the journal records changes against the underlying physical files. In the Objects Browser, logical files and views are shown with the view icon.

    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.

    Authorities of the Gluesync user

    The authorities below are those of the user profile in the Username of the source connection. Gluesync needs no special authority (*ALLOBJ, *SECADM or *JOBCTL). The journal authorities apply to every journal that an entity reads.

    Object Authority Why

    The journal (*JRN)

    *USE and *OBJEXIST

    *USE is enough to read the entries of the registered tables with the QjoRetrieveJournalEntries API. IBM i asks for *OBJEXIST as well when the journal is read without a list of files: Gluesync does this to find the oldest position of the journal when the receiver it was reading from is no longer on the system, when more than 300 tables of one journal are registered, and when Disable display journal files filter is on. *USE and *CHANGE do not include *OBJEXIST, so the *CHANGE authority that *PUBLIC gets on a journal created with the CRTJRN defaults is not enough.

    The library of the journal (*LIB)

    *EXECUTE

    Requirement of the same API.

    The journal receivers (*JRNRCV)

    *USE

    Requirement of the same API. It applies to every receiver of the journal. A receiver that CHGJRN JRNRCV(*GEN) creates gets the authorities of the receiver it replaces.

    The library of the receivers

    *EXECUTE

    Requirement of the same API.

    The replicated tables and their libraries

    *USE on the table, *EXECUTE on the library

    Read access to the source data. The journal entries of a table are also read only with *USE on it, and Gluesync reads the description and the DDS keys of the file with the QDBRTVFD API, which needs the same authority.

    QSYS/QDBRTVFD and QSYS/QJOURNAL

    *USE

    Normally already granted to *PUBLIC.

    QSYS2.SYSTABLES, QSYS2.SYSCOLUMNS, QSYS2.JOURNAL_RECEIVER_INFO and QSYS2.DISPLAY_JOURNAL

    SELECT

    Gluesync reads the catalog and the receiver information with SQL.

    The Gluesync library

    *CHANGE

    Gluesync manages its own objects there.

    If an object is secured by an authorization list, the authority comes from the entry of the user in the list. Check the entry with DSPAUTL, not only the object.

    Error [SQL0443] Not authorized to object

    [SQL0443] on a journal means that the IBM i refuses the Gluesync user access to the journal. Every entity of that journal stops, because the entities of a journal share one reader. The text of the message tells which authority of the table above is missing:

    Message Missing authority

    [SQL0443] Not authorized to object <journal> in <journal library>

    *OBJEXIST on the journal. The user has *USE

    [SQL0443] Not authorized to object <receiver> in <receiver library>

    *USE on that journal receiver

    [SQL0443] NOT AUTHORIZED TO JOURNAL

    *USE on the journal, or *EXECUTE on the library of the journal

    A user that lacks only *OBJEXIST reads the journal normally, because Gluesync reads the entries of the registered tables. The error appears when Gluesync reads the journal without a list of files, for example when a receiver that Gluesync still needs is removed. Then every entity of the journal fails with [SQL0443].

    The agent log can show the IBM i message of the journal API instead: CPF9802 (not authorized to the journal or to a receiver named in the message), CPF9820 (not authorized to the library). If the user lacks *USE on a registered table, the notification is [CPF9822] User not authorized to access file, and a query on the table returns [SQL0551].

    To find the missing authority, run the checks of the authority checklist: DSPOBJAUT on the journal, on the journal library and on the attached receiver, DSPAUTL on the authorization list that secures the journal, and DSPUSRPRF for the groups of the user. The steps to fix the error and to recover the read position are in Fix SQL0443 Not authorized to object on a journal.

    Journals secured by an authorization list or managed by MIMIX

    When a journal is secured by an authorization list, the user needs *USE and *OBJEXIST in its entry of the list. A *PUBLIC entry that gives less does not fix the error for the user.

    MIMIX keeps its journals in a library of its own, for example #MXJRN, and manages them. Gluesync can read these journals when the user has the authorities of the table above on each journal and on the library. When MIMIX re-creates a journal, changes its receivers or reapplies authorities, the authorities of the user can change: check them again after such maintenance, for every journal of the library that an entity reads.

    Contents of the Gluesync library

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

    • 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

    The RRN (relative record number) is the position of a row in a physical file. Gluesync can use it as the key of a file that has no primary key, unique constraint or DDS key: turn on the _RRN technical field in the agent settings and select _RRN as the key in the field editor during entity configuration. The RRN is unique in the file and takes a single column on the target, so it is also a good choice for wide files whose natural key would need more columns than a target index allows.

    A logical key is the other choice: one or more real columns whose values are unique, selected as the key in Gluesync. To read more about logical keys, see the Logical keys documentation.

    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.

    When you use the RRN as the key, keep in mind that a reorganize (RGZPFM) gives the rows new RRNs. Gluesync detects the reorganize from the journal and stops the entity, and you resume it with a full copy of the data. For a comparison of the key choices, including wide files, see the how-to guide Choose a key strategy: physical, logical, RRN or computed.