IBM i Series CDC with Gluesync: Journal Setup Guide
Source data from Db2 for IBM i
|
How-to guides
For step-by-step walkthroughs tested on a running Gluesync, see the how-to guides Troubleshoot IBM i journals and receivers, Choose keys for tables without a primary key and Choose a key strategy: physical, logical, RRN or computed. |
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*Xif object is a directory and*ALLis 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 |
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 ( |
|
|
The library of the journal ( |
|
Requirement of the same API. |
The journal receivers ( |
|
Requirement of the same API. It applies to every receiver of the journal. A receiver that |
The library of the receivers |
|
Requirement of the same API. |
The replicated tables and their libraries |
|
Read access to the source data. The journal entries of a table are also read only with |
|
|
Normally already granted to |
|
|
Gluesync reads the catalog and the receiver information with SQL. |
The Gluesync library |
|
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 |
|---|---|
|
|
|
|
|
|
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
DSPJRNoutput files of the legacy journal reader, only when Legacy mode journal reader is enabled. The default reader calls the IBM i journal APIs (QjoRetrieveJournalEntries,QjoRetrieveJournalInformationandQjoRtvJrnReceiverInformationinQJOURNAL) 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_rowscounts the journal entries not read yet. The count covers every entry of the journal, not only the entries of the entity, sogluesync_source_pending_rows_is_upper_boundis1; -
gluesync_source_read_timestamp_secondsis the time of the last journal entry read, which tells how far behind the reader is; -
gluesync_cache_pending_rowsandgluesync_cache_lag_millisecondsreport 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.