Search docs.molo17.com
    Configuration guide Connectors

    Set up an IBM Db2 LUW source with log-based CDC

    Gluesync has two source agents for IBM Db2 for Linux, UNIX and Windows (LUW). The transaction logs-based agent reads the Db2 recovery log through a native library that runs inside the Db2 server. The triggers-based agent adds triggers to your tables. This guide helps you choose, then sets up the log-based agent end to end: archive logging, the two database users, the library, the connection, a snapshot, live changes, and the failures you are most likely to meet.

    In this guide

    You will compare the two Db2 LUW agents and pick one. You will switch the database to archive logging, create a Gluesync user and a log reader user, and set up the credentials that the log reader library needs. You will create the source connection in the Control Plane, add an entity, start it with a full copy followed by live changes, and check what Gluesync created inside Db2. You will end with the checks and fixes for the errors that stop CDC.

    Quick information

    Goal

    Replicate a Db2 LUW table with a snapshot followed by change data capture (CDC) read from the transaction log

    Audience

    Data engineers and database administrators who own a Db2 LUW database

    Level

    Advanced

    Time required

    About 60 minutes, plus a database backup and an instance restart

    Area

    Db2 database configuration; Control Plane → Pipelines → IBM Db2 for LUW → Transaction logs-based CDC agent; entity → start dialog

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You have an IBM Db2 for LUW database, version 11.5.8.0 or later, on Linux. Db2 11.5.0.0 to 11.5.7.0 leak engine memory in the log read call, so they are not supported for CDC. This guide was tested with Db2 11.5.8.0;

    2. You can run commands as the instance owner (here db2inst1) on the Db2 server, and you have SYSADM or DBADM in the database;

    3. You can run an offline backup of the database, and you can restart the Db2 instance. Both are part of the steps below. Plan a maintenance window;

    4. You have the libdb2.so library that matches your Db2 version, from the reference page, and you can copy it to the Db2 server host. The library must sit on the Db2 server, not on the Core Hub host;

    5. Core Hub can reach the Db2 port, 50000 by default;

    6. You have a target to write to. This guide uses a PostgreSQL database: any target works, and the steps for the source are the same;

    7. You can sign in to the Control Plane with a role that can create pipelines;

    8. You have a source table with a primary key. This guide uses SALES.ORDERS with 1,000 rows (ID INTEGER primary key, CUSTOMER VARCHAR(50), STATUS VARCHAR(20), AMOUNT DECIMAL(10,2), CREATED_AT TIMESTAMP).

    The messages quoted in this guide were captured in the Control Plane and in the Core Hub log with Db2 11.5.8.0 as the source and PostgreSQL 16 as the target. The options of the agent are listed in the Db2 LUW CDC reference.

    Guide steps

    Choose the Db2 LUW agent

    UI

    The Control Plane lists one IBM Db2 for LUW tile with the label 2 connectors available. Select it to see the two source agents. Choose with this table:

    Transaction logs-based CDC agent Triggers-based CDC agent

    How it captures changes

    A native library, libdb2.so, reads the Db2 recovery log and a stored procedure, GS_SP_LOGMINER, writes the changes to one CDC table for each source table

    Triggers on the source tables write the key of each changed row to a CDC table

    What you install on the Db2 server

    The library file, a log reader user with DBADM, and environment variables for the instance

    Nothing. The agent creates the triggers

    Database settings

    Archive logging must be on, which needs an offline backup. The instance is restarted once

    No log settings

    Db2 version

    11.5.8.0 or later, including 12.1

    11.5 or later

    Objects on the source tables

    No triggers. The log is read behind the writes

    Triggers on every source table, which fire on inserts, updates and deletes

    Transaction details

    Transaction ids, operation timestamps and the user that made the change

    Row ids and the type of change

    Choose the log-based agent when you can change the database settings and install a file on the server, and when you do not want triggers on your tables. Choose the triggers-based agent when you cannot do either. The rest of this guide uses the log-based agent. The triggers agent is described in the triggers setup page.

    Agents setup wizard with the IBM Db2 for LUW tile selected and two agents shown below it
    Figure 1. The two Db2 LUW source agents in the setup wizard.
    Turn on archive logging

    CLI

    The log reader needs a database that keeps its logs, which Db2 calls archive logging, or log retain. Without it the read call is refused, see the troubleshooting table. Sign in as the instance owner and check the setting:

    su - db2inst1
db2 connect to DEMO
db2 get db cfg for DEMO | grep -E "LOGARCHMETH1|LOGPRIMARY|LOGFILSIZ"

    LOGARCHMETH1 = OFF means circular logging. Set an archive method. This example archives to a directory on the server. Use the method your backup policy needs, for example a vendor or a TSM target:

    mkdir -p /database/logs /database/backup
db2 update db cfg for DEMO using LOGARCHMETH1 DISK:/database/logs

    Db2 now puts the database in backup pending state. Connect again and read the message, then take an offline backup:

    db2 connect to DEMO
db2 backup db DEMO to /database/backup
db2 connect to DEMO
    Expected result

    The UPDATE DATABASE CONFIGURATION command ends with the warning SQL1363W and the next CONNECT fails with SQL1116N A connection to or activation of database "DEMO" failed because the database is in BACKUP PENDING state. SQLSTATE=57019. After Backup successful, the CONNECT works and db2 get db cfg for DEMO | grep -E "LOGARCHMETH1|Backup pending" shows LOGARCHMETH1 = DISK:/database/logs/ and Backup pending = NO.

    Changes made while the database used circular logging cannot be read later. In the test, a row inserted while archive logging was off never reached the target, and CDC kept running after logging was on again. Turn archive logging on before you start the entity, and keep it on.

    The log size settings (LOGFILSIZ, LOGPRIMARY, LOGSECOND) decide how large a transaction your database can hold, and are not specific to Gluesync. Keep the archive directory on a disk with room for the logs that your backup policy keeps.

    Create the Gluesync user

    SQL

    Db2 does not store passwords. A Db2 user is an operating system user on the Db2 server, or a user of the directory you use for authentication. Create the operating system user first, for example gluesync in the group of the instance owner, then grant the privileges. Run the SQL as the instance owner, connected to the database:

    -- the schema that holds the CDC tables, owned by the Gluesync user
CREATE SCHEMA GLUESYNC AUTHORIZATION GLUESYNC;

GRANT CONNECT ON DATABASE TO USER GLUESYNC;

-- read the source tables, and let Gluesync switch DATA CAPTURE on for them
GRANT SELECTIN, INSERTIN, UPDATEIN, DELETEIN, EXECUTEIN, ALTERIN
ON SCHEMA SALES TO USER GLUESYNC;

-- read and write what the log reader creates in the GLUESYNC schema
GRANT SELECTIN, INSERTIN, UPDATEIN, DELETEIN, EXECUTEIN
ON SCHEMA GLUESYNC TO USER GLUESYNC;

-- create the external stored procedure on top of the library
GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO USER GLUESYNC;

    Repeat the SALES grant for every schema you replicate. Why each grant is there:

    1. The user creates the GLUESYNC schema if it does not exist, and one CDC table for each source table in it. The CREATE SCHEMA statement above does it for you with the right owner;

    2. For each source table of a started entity, Gluesync runs ALTER TABLE <schema>.<table> DATA CAPTURE CHANGES. This makes Db2 write the full row to the log for updates and deletes, so the library can read the old and the new row. The statement needs the ALTER privilege on the table, or ALTERIN on its schema. A table you only copy once, without CDC, is not altered;

    3. CREATE_EXTERNAL_ROUTINE is an elevated privilege. Without it, Gluesync cannot create GS_SP_LOGMINER. If you create the procedure yourself, set autoCreateStoredProcedure to false and keep the privilege away from the user, see the reference page;

    4. The GLUESYNC schema grant is needed because the library creates its state tables as the log reader user of the next step. Without it, Gluesync cannot read them, see the troubleshooting table.

    Expected result

    Every statement ends with DB20000I The SQL command completed successfully. Check the user from any machine that reaches the server, for example with the Db2 command line: db2 connect to DEMO user gluesync using '<password>'. A wrong password returns SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001.

    Create the log reader user and deploy the library

    CLI

    The library opens a second Db2 connection to read the log. That connection does not inherit the Gluesync user, so it signs in with a dedicated user whose credentials come from environment variables of the Db2 instance. The log read call needs DBADM or SYSADM, so give the user DBADM. Create the operating system user, for example cdc_reader, then as the instance owner:

    su - db2inst1
. ~/sqllib/db2profile
db2 connect to DEMO
db2 "GRANT DBADM ON DATABASE TO USER CDC_READER"
db2 connect reset

    Put the credentials in a file that only the instance owner can read. Use the home directory of the instance owner (~db2inst1; on some images it is not /home/db2inst1). DB_HOST must be an address that the Db2 server itself can reach, and localhost works when the library and the database run on the same host:

    cat > ~/.db2-cdc.env <<'EOF'
export DBNAME='DEMO'
export DB_HOST='localhost'
export DB_PORT='50000'
export DB_USER='cdc_reader'
export DB_PASSWORD='<a strong password>'
EOF
chmod 600 ~/.db2-cdc.env
echo '[ -r "$HOME/.db2-cdc.env" ] && . "$HOME/.db2-cdc.env"' >> ~/sqllib/userprofile
db2set DB2ENVLIST="DBNAME DB_HOST DB_PORT DB_USER DB_PASSWORD"

    If db2set DB2ENVLIST already returns names, append the five new ones instead of replacing the list. Then copy the library to the server, name it libdb2.so, and restart the instance so that it picks up the variables:

    mkdir -p /function
cp libdb2.so /function/libdb2.so
chmod 755 /function/libdb2.so
. ~/.db2-cdc.env
db2stop force
db2start
db2 connect to DEMO user cdc_reader using '<a strong password>'
    Expected result

    db2stop and db2start end with SQL1064N DB2STOP processing was successful. and SQL1063N DB2START processing was successful. The last command connects and shows SQL authorization ID = CDC_READ…​. The default path of the library in the agent is /function/libdb2.so. Use another directory if you like, and set storedProcedureLibraryPath to it in step 5.

    db2stop force ends every connection to the instance. Do it in a maintenance window. Running entities reconnect by themselves when the instance is back: the Core Hub log shows Error while starting entity SALES.ORDERS sync. Retrying…​ with the JDBC message A communication error occurred during operations on the connection’s underlying socket, and the entity continues without a new copy.
    Create the Db2 source connection

    UI

    1. In the Control Plane, select Create your first pipeline, or the + button of the pipeline list if you already have pipelines. The Agents setup wizard opens;

    2. In 1. Setup source, search for db2, select the IBM Db2 for LUW tile, then the IBM Db2 for LUW Transaction logs-based CDC agent tile;

    3. In Base settings, fill the form:

      • Host: the DNS name or address of the Db2 server, as Core Hub sees it;

      • Port: 50000, or the port of your instance;

      • Database Name: the database of step 2;

      • Username and Password: the Gluesync user of step 3, not the log reader user;

    4. Leave Min connections count (at least 3) and Max connections count at their defaults for now. The tab Advanced settings takes properties by name. Add one only when you need it, for example storedProcedureLibraryPath for a library outside /function, or storedProcedurePollingIntervalSeconds for the polling interval. The names and defaults are in the reference page;

    5. Select Test and continue. The source appears with a green check mark. Select Next.

    Agents setup wizard with the IBM Db2 for LUW Transaction logs-based agent
    Figure 2. The Base settings of the Db2 LUW source agent.

    1. In 2. Setup target(s), choose your target agent and fill its connection. For the PostgreSQL target of this guide, choose the PostgreSQL CDC agent tile, enter the host name, 5432, the database, the user and the password, then select Test and continue and Next;

    2. Enter a Pipeline name and select Finish.

    Expected result

    The pipeline appears in the HEALTHY group with the Db2 and the target icons, and the entity table is empty. The connection test checks that the user can sign in. It does not check archive logging, DBADM of the log reader, or the library. Core Hub already created the table GLUESYNC.LOG_MINER_TX_COMPLETE in the database by now: the object browser of the next step lists it.

    Add the entity and create the target table

    UI

    1. In the pipeline, select Entities. The Add entities page opens with the Object browser. Expand the schema SALES and tick the table ORDERS. The Field mapping dialog opens. The browser also lists the system schemas such as SYSCAT, so search for your own schema;

    2. Under Target, choose the Target Schema, for example public, then type orders in Target Table and choose Create table "orders". The dialog shows New table orders will be created on confirm. and the proposed target types;

    3. Select Confirm. The Create table orders dialog shows the read-only SQL statement. Read it, then select Create table orders to run it on the target;

    4. Back on the Add entities page, select Confirm and continue.

    Field mapping dialog with the source table SALES.ORDERS and the target table public.orders
    Figure 3. The Field mapping of the table: Db2 types on the left, the proposed PostgreSQL types on the right.
    Expected result

    The entity appears in the pipeline on Hold as SALES.ORDERS. The statement that the dialog shows creates "public"."orders" with the quoted upper case column names "ID", "CUSTOMER", "STATUS", "AMOUNT" and "CREATED_AT", because Db2 names are upper case. Quote them in queries on the target. The types are integer, varchar(50), varchar(20), decimal(10, 2) and timestamp(6) without time zone, and the primary key stays on "ID".

    Db2 XML columns are left out of the CDC table by the agent. Other types follow the target. A target that cannot hold a type shows it in the Field mapping dialog, where you change the target type or add a conversion. See Fix target length and truncation errors.
    Start the entity and check what Gluesync created

    UI + SQL

    1. On the row of the entity, select the play button. The Sync changes (CDC) dialog opens;

    2. Under Start from keep Full copy, Recommended for the first start. It copies all the rows, then follows the changes. Where it stopped is not available before the first run, and Now skips what changed before the start;

    3. Under Data already in the target keep Keep and merge, and under Speed keep Standard;

    4. Select Start entity.

    Sync changes CDC dialog for SALES.ORDERS with Start from Full copy selected
    Figure 4. The start dialog of a new entity, with Full copy and Keep and merge.

    When the entity is active, read what Gluesync created in Db2, connected as the instance owner:

    SELECT TABSCHEMA, TABNAME, DATACAPTURE FROM SYSCAT.TABLES
WHERE TABSCHEMA IN ('SALES', 'GLUESYNC') ORDER BY 1, 2;

SELECT ROUTINENAME, LANGUAGE, FENCED, IMPLEMENTATION FROM SYSCAT.ROUTINES
WHERE ROUTINESCHEMA = 'GLUESYNC';

    And the row count on the target:

    -- on the target
SELECT count(*) FROM public.orders;
    Expected result

    SALES.ORDERS shows DATACAPTURE = Y. The GLUESYNC schema holds SALES_ORDERS_CDC (one CDC table for each source table, named after the schema and the table), the state tables LOG_MINER_STATE, LOG_MINER_PENDING_TX, LOG_MINER_PENDING_EVENTS, LOG_MINER_ERRORS and LOG_MINER_TX_COMPLETE, and the procedure GS_SP_LOGMINER, a fenced C procedure whose implementation is /function/libdb2.so!sp_call_wrapper. The target count grows until it equals the source count, 1000, and the entity is Active.

    The Core Hub log shows one line each time the procedure runs, every 10 seconds by default. An idle database gives GS_SP_LOGMINER result - status='SUCCESS:EMPTY:...' and a run that read changes gives a status that starts with SUCCESS: and carries counters, for example rows=1. Any status that starts with ERROR: is a failure, see the troubleshooting table.

    Follow live changes and check the three operations

    SQL

    On the source, insert, update and delete one row each. Commit them:

    INSERT INTO SALES.ORDERS VALUES (1001, 'customer-new', 'new', 19.90, CURRENT TIMESTAMP);
UPDATE SALES.ORDERS SET STATUS = 'paid', AMOUNT = 25.00 WHERE ID = 1;
DELETE FROM SALES.ORDERS WHERE ID = 2;

    After a few seconds, read the three keys on the target:

    SELECT "ID", "STATUS", "AMOUNT" FROM public.orders WHERE "ID" IN (1, 2, 1001) ORDER BY 1;
SELECT count(*) FROM public.orders;

    You can also read the CDC table in Db2, to see what the log reader wrote:

    SELECT GS_TRANSACTION_ID, GS_OPERATION, ID, GS_SESSION_USER_NAME, GS_TRANSACTION_READ, GS_TRANSACTION_CONSUMED
FROM GLUESYNC.SALES_ORDERS_CDC ORDER BY GS_TRANSACTION_ID;
    Expected result

    Row 1 shows paid and 25.00, row 1001 exists, and row 2 is gone. The count is 1000. The CDC table has one row for each change, with GS_OPERATION set to I, U and D, and the Db2 user that made the change in GS_SESSION_USER_NAME. A D row has an empty ID: the old image of the row sits in the O_ID column, and the agent uses it as the key. The entity row in the Control Plane shows the end-to-end latency in milliseconds.

    Pipeline db2-to-postgres with the entity SALES.ORDERS in the Active group
    Figure 5. The entity is active and follows the changes.

    An uncommitted change is not replicated, and a rolled back change never is. A change appears on the target after its commit is in the log and after the next poll.

    Tune the speed and the retention

    UI

    1. The agent polls the log every 10 seconds by default (storedProcedurePollingIntervalSeconds, minimum 1). A shorter interval lowers the latency and adds load on the Db2 server. A latency of a few seconds is normal with the default;

    2. sourceChangeRetention keeps the rows of the CDC tables for 24 hours by default, and cdcBatchSize is the size of the batches that mark rows as read and consumed. Both are in the reference page;

    3. For the copy, raise the lanes in the start dialog under Speed, or choose Custom. More lanes copy faster and put more load on both databases. The Db2 LUW CDC agent does not support bulk load, so the speed of live changes is set by the polling interval and by the target. See Tune replication throughput;

    4. Keep the CDC tables in mind when you size the database: each one holds the old and the new image of every changed row for the retention time.

    Expected result

    A change made in Db2 reaches the target within about one polling interval plus the commit time of the target.

    Troubleshooting

    Symptom or message Cause Fix

    The Core Hub log shows GS_SP_LOGMINER result - status='ERROR:READLOG_QUERY:-2651|LR-9001' and GS_SP_LOGMINER run failed: [LR-9001] Log reader failure: ERROR:READLOG_QUERY:-2651, and the pipeline reports a critical error

    The database uses circular logging. Db2 returns SQL2651N The log records associated with the database can not be asynchronously read: only a database with LOGARCHMETH1 (log retain) can have its log read.

    Do step 2: set LOGARCHMETH1, take the offline backup, and the entity recovers by itself on the next poll. Changes made while logging was circular are lost, so realign the target with a full copy.

    SQL1116N A connection to or activation of database "DEMO" failed because the database is in BACKUP PENDING state. SQLSTATE=57019

    You changed LOGARCHMETH1 from OFF to an archive method and did not take the backup yet.

    Run db2 backup db DEMO to <directory> as the instance owner, offline. Connect again.

    Could not read GLUESYNC.LOG_MINER_ERRORS: …​ does not have the required authorization or privilege …​ Operation: "SELECT". Object: "GLUESYNC.LOG_MINER_ERRORS" (SQLCODE=-551, SQLSTATE=42501) next to a log reader error

    The library creates its state tables as the log reader user, so the Gluesync user does not own them. The Gluesync user has no privilege on the GLUESYNC schema.

    Run GRANT SELECTIN, INSERTIN, UPDATEIN, DELETEIN, EXECUTEIN ON SCHEMA GLUESYNC TO USER GLUESYNC; as in step 3. The message hides the real error of the library, so read GLUESYNC.LOG_MINER_ERRORS again once the grant is in place.

    The status of the procedure is ERROR:SQLConnect/SQLDriverConnect

    The library cannot open its own connection. The five variables are missing, wrong, or not passed to the procedure.

    Check DBNAME, DB_HOST, DB_PORT, DB_USER and DB_PASSWORD, check that db2set DB2ENVLIST lists all five, and restart the instance. See the reference page. This case was not reproduced for this guide.

    Gluesync cannot create GS_SP_LOGMINER, or a call fails with SQL0551N

    The Gluesync user has no CREATE_EXTERNAL_ROUTINE, no EXECUTEIN on the schema, or the file is not at storedProcedureLibraryPath on the server.

    Check the grants of step 3, check that /function/libdb2.so exists on the Db2 server with chmod 755, or set the path to the real location in Advanced settings.

    SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001

    The operating system user does not exist on the Db2 server, or the password is wrong.

    Create or reset the operating system user on the server, then edit the source connection.

    The Core Hub log shows Error while starting entity SALES.ORDERS sync. Retrying…​ with a communication error and ERRORCODE=-4499

    The Db2 instance was stopped or restarted, or the network dropped. The connections of the pool are gone.

    Nothing, when the server comes back. The agent retries and the entity goes on from its checkpoint. If it does not recover, see Diagnose and recover a stalled pipeline or entity.

    Updates and deletes of a table do not carry the old values, or the CDC rows are incomplete

    DATA CAPTURE CHANGES is not set on the table. Gluesync tried to set it and the statement failed, for example for a missing ALTER privilege. The Core Hub log shows Failed to enable DATA CAPTURE on table as a warning.

    Run ALTER TABLE SALES.ORDERS DATA CAPTURE CHANGES; yourself, or grant ALTERIN on the schema and start the entity again. Check SELECT DATACAPTURE FROM SYSCAT.TABLES WHERE TABSCHEMA = 'SALES' AND TABNAME = 'ORDERS'.

    The db2sysc process keeps growing in memory

    The server runs a Db2 release before 11.5.8.0. The engine leaks memory in the log read call.

    Upgrade Db2 to 11.5.8.0 or later. Tuning INSTANCE_MEMORY does not help, see the reference page.

    The CDC tables in the GLUESYNC schema grow

    Retention is long, an entity is paused, or a long transaction keeps changes open.

    Lower sourceChangeRetention, resume or delete the entities you no longer use, and look for transactions that stay open on the source.

    Related guides