IBM Db2 LUW logo IBM Db2 LUW CDC with Gluesync: Setup

Source data from IBM Db2 LUW (via CDC)

Prerequisites

To have Gluesync’s IBM Db2 LUW CDC agent working on your database instance you will need to have:

Db2 version requirement and engine memory fix

Change Data Capture relies on the libdb2.so native shared library invoking Db2’s binary log reader API (db2ReadLog).

Db2 11.5.8.0+ is strictly required for CDC

Releases of IBM Db2 for LUW prior to 11.5.8.0 (including 11.5.0.0 GA through 11.5.7.0) suffer from a critical engine-level defect in db2sysc during db2ReadLog calls:

  • Engine-side private memory leak: Every invocation of the db2ReadLog API leaks anonymous private memory inside the main database process (db2sysc) — approximately 30 MB per call even when the log window contains no transactions, and up to ~1.9 GB per 50 MB of log data scanned.

  • Invisible to DB2 memory pools: Because this leak consists of unnamed anonymous virtual memory mappings ([anon] in /proc/<db2sysc>/smaps), it is not managed by DB2 memory pools and remains invisible to db2pd -dbptnmem. Setting INSTANCE_MEMORY caps or tuning buffer pools cannot constrain or reclaim this memory.

  • Resolved in 11.5.8.0: IBM addressed this defect starting in IBM Db2 LUW 11.5.8.0 (Fix Pack 8) and Db2 12.1+. In these versions, memory allocated by db2ReadLog is properly reclaimed and memory growth per invocation is minimal (~1 MB).

Attempting to run the Gluesync CDC Log Reader on Db2 versions older than 11.5.8.0 will cause continuous RSS memory exhaustion of the db2sysc process and eventual server out-of-memory crashes. Upgrading to Db2 11.5.8.0 or higher is therefore mandatory.

Creating the Gluesync database user

If Gluesync must read and write all objects and call all routines in the GLUESYNC schema and in the chosen application schemas, use schema-level privileges. Run the following as a DB2 administrator:

GRANT CONNECT ON DATABASE TO USER gluesync;

GRANT SELECTIN, INSERTIN, UPDATEIN, DELETEIN, EXECUTEIN
ON SCHEMA GLUESYNC
TO USER gluesync;

GRANT SELECTIN, INSERTIN, UPDATEIN, DELETEIN, EXECUTEIN
ON SCHEMA <APPLICATION_SCHEMA>
TO USER gluesync;

Repeat the second GRANT statement for every application schema that Gluesync needs to access.

SELECTIN, INSERTIN, UPDATEIN, and DELETEIN grant access to the data in the schema; EXECUTEIN grants execution of the schema’s stored procedures, functions, and packages, including objects created in the future. See the IBM Db2 documentation on schema privileges for more details.

The repository stored procedure resides in the DEMO schema by default. To allow Gluesync to call it, also grant: 
GRANT EXECUTEIN ON SCHEMA DEMO TO USER gluesync;

If Gluesync must also create, replace, or drop stored procedures within a schema, additionally grant:

GRANT CREATEIN, ALTERIN, DROPIN
ON SCHEMA <APPLICATION_SCHEMA>
TO USER gluesync;

To create an external C stored procedure such as SP_CALL_WRAPPER, Gluesync also needs:

GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO USER gluesync;
This last privilege is elevated. Grant it only if Gluesync genuinely needs to deploy or update external libraries or stored procedures.

CDC table structure

For each source table, Gluesync creates a corresponding CDC table in the GLUESYNC schema with the following columns:

  • GS_TRANSACTION_ID - Unique transaction identifier (BIGINT)

  • GS_ROW_ID - Timestamp-based row identifier (TIMESTAMP)

  • GS_SESSION_USER_NAME - User who performed the operation (VARCHAR)

  • GS_OPERATION_TS - Operation timestamp (TIMESTAMP)

  • GS_OPERATION - Operation type: I (INSERT), U (UPDATE), D (DELETE) (CHAR)

  • GS_CDC_TABLE_INSERT_TIMESTAMP - When the change was recorded (TIMESTAMP)

  • GS_TRANSACTION_READ - Flag indicating if transaction has been read (SMALLINT)

  • GS_TRANSACTION_CONSUMED - Flag indicating if transaction has been consumed (SMALLINT)

  • Source table columns — all columns from the source table

Optimized range batch updates (Gluesync 2.2.11.3+)

Starting from Gluesync 2.2.11.3, CDC transaction status updates (marking events as consumed via GS_TRANSACTION_CONSUMED = 1 or resetting pending reads via GS_TRANSACTION_READ = 0) are batched into contiguous transaction ID ranges (GS_TRANSACTION_ID >= ? AND GS_TRANSACTION_ID ⇐ ?) and executed via JDBC batch statements (addBatch() / executeBatch()). This drastically reduces SQL execution overhead and database round-trips compared to individual row updates, dramatically speeding up high-throughput pipelines.

Configuration

Server-level configuration

The following server-level settings control the overall CDC behavior:

Parameter Default Description

sourceChangeRetention

24

Number of hours to retain change data in CDC tables. Older changes are automatically cleaned up.

storedProcedurePollingIntervalSeconds

10

Interval in seconds between consecutive stored procedure invocations. Controls how frequently the agent polls for changes. Minimum value is 1 second.

autoCreateStoredProcedure

true

When enabled, the agent automatically creates the GS_SP_LOGMINER stored procedure if it doesn’t exist in the database. Set to false if you prefer to manage the stored procedure manually.

storedProcedureLibraryPath

/function/libdb2.so

Path to the libdb2.so native library on the DB2 server host. This shared library is provided by Gluesync and must be downloaded and placed on the server before the stored procedure can be created. See Setting Up libdb2.so for details.

technicalFields

empty

Optional list of technical fields to include in the CDC output. Available options: * GS_TRANSACTION_ID - Transaction identifier * GS_TRANSACTION_TIMESTAMP - Transaction timestamp * GS_TRANSACTION_OPERATION - Operation type (I/U/D)

storedProcedureMaxTxEvents

0

Maximum DML events buffered by the log reader for a single source transaction before spilling to the staging tables (0 preserves the library’s built-in default).

cdcBatchSize

10000

Batch size for flushing read and consumed CDC table updates. Defaults to 10000 (increased from 1000 starting in 2.2.11.3).

disableTruncateOnSnapshot

false

When false, target tables are truncated before snapshot inserts. Set to true to preserve existing data during snapshot operations.

Connection configuration

The following connection-level settings control how Gluesync connects to the Db2 LUW database:

Parameter Default Description

useConnectionPool

true

Enable connection pooling for better resource utilization.

loginTimeout

30

Login timeout in seconds. Controls how long to wait for a successful database login.

connectionTimeout

0

Connection timeout in seconds. 0 means no timeout (wait indefinitely).

socketTimeout

0

Socket timeout in seconds. 0 means no timeout. Useful for detecting network issues.

keepAlive

true

Enable TCP keep-alive on the connection socket.

enableAutoReconnect

false

Automatically reconnect if the connection is lost.

maxReconnects

3

Maximum number of reconnection attempts.

initialReconnectDelay

1000

Initial delay in milliseconds before the first reconnection attempt.

driverType

4

JDBC driver type. Type 4 is for TCP/IP connections (recommended).

securityMechanism

3

Security mechanism for authentication. 3 corresponds to CLEAR_TEXT_PASSWORD_SECURITY.

retrieveMessagesFromServerOnGetMessage

true

Retrieve detailed error messages from the server.

currentSchema

null

Optional: Set the current schema for the connection.

clientProgramName

null

Optional: Identifier for the client program (useful for monitoring).

clientWorkstation

null

Optional: Identifier for the client workstation.

clientUser

null

Optional: Operating system user running the client.

Table-level configuration

Parameter Default Description

whereClause

empty

Optional SQL WHERE clause (without the WHERE keyword) to filter which rows are included in snapshots. Example: DEPARTMENT_ID = 10

Setting up libdb2.so

The GS_SP_LOGMINER stored procedure relies on libdb2.so, a native shared library provided by Gluesync. You must download and deploy it on the DB2 server host before enabling CDC.

Download

A dedicated build of libdb2.so (version 1.0.14) is available for each supported DB2 version. Download the appropriate file for your DB2 server:

The links below are also resolved dynamically from the public catalog:

After downloading, rename the file to libdb2.so before copying it to the target directory.

See the native library changelog for tagged releases.

Installation

  1. Copy libdb2.so to a directory accessible by the DB2 instance. The recommended path is /function:

    cp libdb2.so /function/libdb2.so
chmod 755 /function/libdb2.so

    You may use a different directory, but make sure to set storedProcedureLibraryPath to the exact path you choose.

  2. Once the library is in place, Gluesync will automatically create the GS_SP_LOGMINER stored procedure on top of it the next time the agent starts (provided autoCreateStoredProcedure is true).

The library must reside on the DB2 server host itself (not on the Gluesync host), because DB2 loads the shared object at procedure invocation time.

Configuring database environment variables for libdb2.so

The libdb2.so library opens a second DB2 connection to read the transaction log. This connection does not automatically inherit the user that invoked the stored procedure (the CALL statement); it must instead receive credentials through environment variables. No changes to the library itself are required.

1. Create a dedicated user

Create a dedicated DB2 user, for example cdc_reader, with a valid password. This user must have DBADM on the monitored database, because the DB2 API used by the library (db2ReadLog) requires either DBADM or SYSADM.

Run the following as the instance owner (db2inst1), replacing <DATABASE_NAME> with the actual database name:

su - db2inst1
. ~/sqllib/db2profile

db2 connect to <DATABASE_NAME>

db2 "GRANT DBADM ON DATABASE TO USER cdc_reader"

db2 connect reset

2. Configure credentials for the DB2 instance

Create the file /home/db2inst1/.db2-cdc.env with the following content:

export DBNAME='<DATABASE_NAME>'
export DB_HOST='<DB2_HOST>'
export DB_PORT='<DB2_PORT>'
export DB_USER='cdc_reader'
export DB_PASSWORD='<CDC_READER_PASSWORD>'

Example:

export DBNAME='PRODDB'
export DB_HOST='db2-prod.company.local'
export DB_PORT='50000'
export DB_USER='cdc_reader'
export DB_PASSWORD='********'
DB_HOST must be the DB2 server address reachable from the machine running the library. Use localhost only if the database and the library run on the same host.

Protect the file so that only the instance owner can read it:

chown db2inst1:db2iadm1 /home/db2inst1/.db2-cdc.env
chmod 600 /home/db2inst1/.db2-cdc.env

3. Load the file automatically

Add the following line to the end of /home/db2inst1/sqllib/userprofile:

[ -r "$HOME/.db2-cdc.env" ] && . "$HOME/.db2-cdc.env"

4. Allow stored procedures to receive the variables

As db2inst1, run:

su - db2inst1
. ~/sqllib/db2profile

db2set DB2ENVLIST

If the command returns nothing, set it as follows:

db2set DB2ENVLIST="DBNAME DB_HOST DB_PORT DB_USER DB_PASSWORD"

If it already returns some names, keep them and append the five new ones. For example, if it returns FOO BAR:

db2set DB2ENVLIST="FOO BAR DBNAME DB_HOST DB_PORT DB_USER DB_PASSWORD"

5. Restart the instance

Still as db2inst1:

. ~/sqllib/db2profile
. ~/.db2-cdc.env

db2stop force
db2start
This operation temporarily stops the database and should be planned within a maintenance window.

6. Final verification

First verify that the dedicated user can connect:

su - db2inst1
. ~/sqllib/db2profile

db2 connect to <DATABASE_NAME> user cdc_reader using '<CDC_READER_PASSWORD>'

Then run the Log Reader stored procedure as usual.

Expected outcome: a status of RESET, SUCCESS, SUCCESS:EMPTY, or SUCCESS:DRYRUN.

If you still see:

ERROR:SQLConnect/SQLDriverConnect

verify the values of DBNAME, DB_HOST, DB_PORT, DB_USER, and DB_PASSWORD, and make sure they are included in the DB2ENVLIST configuration.

Operational behavior

Checkpoint management

The agent maintains checkpoints to track which changes have been processed:

  • Checkpoint Reset: On first start, if requested, the agent resets the checkpoint to begin capturing from the current point in time

  • Checkpoint Advancement: The checkpoint advances as transactions are successfully processed

  • Retention: Changes older than sourceChangeRetention hours are automatically cleaned up

Transaction processing

  • Transactions are identified by unique GS_TRANSACTION_ID values

  • Each transaction can contain multiple row operations (INSERT, UPDATE, DELETE)

  • Operations are batched for efficient processing (batch size: 1000 operations)

  • Read status is tracked to avoid reprocessing the same transaction

Error handling

The agent implements robust error handling:

  • Stored procedure errors are captured and reported as notifications

  • Connection errors trigger automatic reconnection attempts (if enabled)

  • The polling loop continues even if individual invocations fail

  • Error details are logged for troubleshooting

Monitoring and troubleshooting

Key metrics

Monitor these metrics to understand CDC performance:

  • Rows processed per invocation: Indicates change volume

  • Polling interval: Verify it matches your configuration

  • Stored procedure status: Check for ERROR or KO status codes

  • CDC table size: Monitor growth to ensure cleanup is working

Common issues

No changes detected

  • Verify the stored procedure is created: SELECT * FROM SYSCAT.PROCEDURES WHERE PROCNAME = 'GS_SP_LOGMINER'

  • Check that autoCreateStoredProcedure is enabled or the procedure exists

  • Verify source tables have CDC enabled

High CDC table growth

  • Reduce sourceChangeRetention to clean up older changes faster

  • Verify that transactions are being marked as consumed

  • Check for long-running transactions that prevent cleanup

Connection timeouts

  • Increase connectionTimeout or socketTimeout if network is slow

  • Enable enableAutoReconnect for automatic recovery

  • Verify network connectivity to the database

Continuous memory growth in the db2sysc process

  • Verify that the database instance is running IBM Db2 for LUW version 11.5.8.0 or higher (or Db2 12.1+). Earlier Db2 11.5 releases (11.5.0.0 through 11.5.7.0) suffer from a known engine-level memory leak inside db2sysc during db2ReadLog calls that causes uncontrolled anonymous private memory growth, which can only be resolved by upgrading Db2 to 11.5.8.0+ (Fix Pack 8) or higher.

Stored procedure errors

  • Ensure libdb2.so has been downloaded from the Gluesync distribution and copied to the path specified in storedProcedureLibraryPath (default: /function/libdb2.so) on the DB2 server host

  • Verify the file permissions allow the DB2 instance user to read and execute it (chmod 755)

  • Verify the procedure has execute permissions in the database

  • Review database logs for detailed error messages