Search docs.molo17.com
    Configuration guide Connectors

    Set up an Informix CDC source

    The Informix agent reads changes through the Informix change data capture (CDC) API. The API lives in a helper database called syscdcv1, and it only works on a logged database. This guide prepares the server, the user and the tables, creates the source connection, runs a snapshot and live changes into a MySQL target, and shows the failures you are most likely to meet: a user without the rights to open a CDC session, a user that cannot connect, and a wrong CDC database name.

    In this guide

    You will check that your database is logged, create the syscdcv1 database, turn on full row logging for your tables, and create a user with the privileges Gluesync needs. You will create the Informix source connection in the Control Plane, add an entity, and start it with a full copy followed by live changes. You will check the three operations on the target, and you will recover from the errors that stop CDC.

    Quick information

    Goal

    Replicate an Informix table with a snapshot followed by change data capture (CDC), with a user that has only the privileges it needs

    Audience

    Data engineers and database administrators who own an Informix server

    Level

    Intermediate

    Time required

    About 40 minutes

    Area

    Informix → syscdcv1 database; Control Plane → Agents setup wizard → Informix CDC agent → Base settings

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You have an Informix server, version 12.10 or later, and you can run SQL on it as the informix user. This guide was tested with Informix 14.10;

    2. You can open a shell on the server host, because you run a script from $INFORMIXDIR/etc and you create an operating system account;

    3. Your database is logged. Step 1 shows how to check it;

    4. Core Hub can open a connection to the Informix port, 9088 by default;

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

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

    7. You have a source table with a primary key. This guide uses customers (id int primary key, name varchar(100), email varchar(100), balance decimal(10,2), created_at datetime year to second).

    The messages quoted in this guide were captured in the Control Plane and in the Core Hub log with Informix 14.10 as the source and MySQL 8.0 as the target. The connection fields and the CDC background are described in the Informix CDC reference.

    Guide steps

    Check that the database is logged

    SQL

    The CDC API reads the logical log, so Informix can only capture a database that was created with logging. Check it from any database as the informix user:

    SELECT name, is_logging FROM sysmaster:sysdatabases WHERE name = 'demo';

    If you still have to create the database, add WITH LOG:

    CREATE DATABASE demo WITH LOG;
    Expected result

    is_logging is 1. A database with is_logging set to 0 cannot be captured: in step 3, cdc_set_fullrowlogging returns -83704 for a table of such a database.

    Create the CDC database

    CLI

    Gluesync connects to a second database, syscdcv1, and calls the CDC functions that Informix stores in it. Run the Informix script once, as the informix user, from the $INFORMIXDIR/etc directory. Set DB_LOCALE to the locale of the database you capture, as the reference page asks:

    export DB_LOCALE=en_us.utf8
cd $INFORMIXDIR/etc
dbaccess demo syscdcv1.sql

    Check that the database exists:

    SELECT name, is_logging FROM sysmaster:sysdatabases WHERE name = 'syscdcv1';
    Expected result

    The script prints Routine created. and the query returns one row with is_logging set to 1. The functions are owned by informix, and Informix creates them in DBA mode, so only a DBA can run them. Step 4 uses this.

    If your server is a managed Informix service, ask the provider to enable the CDC API. You cannot run the script yourself there.
    Turn on full row logging for the tables

    SQL

    The Informix documentation, and the reference page, ask you to turn on full row logging for each table you capture. Connect to the syscdcv1 database as informix and run the function once for each table. The first argument is <database>:<owner>.<table>, the second is 1 to turn the logging on:

    EXECUTE FUNCTION cdc_set_fullrowlogging('demo:informix.customers', 1);
    Expected result

    The function returns 0. A table that does not exist returns -83705. A table of a database without logging returns -83704. Call the function with 0 to turn the logging off again.

    In the test, a table with a primary key was captured correctly before the function was called: the insert, the update and the delete all reached the target. Do not rely on this. Turn full row logging on for every table you replicate, and always for tables without a primary key.
    Create the Gluesync user

    CLI + SQL

    Informix signs in with an operating system account. Create the account on the server host as root, then give it the privileges. Replace the password:

    useradd -m gluesync
echo 'gluesync:<a strong password>' | chpasswd

    In the database you capture, the user needs CONNECT and SELECT on the tables. The snapshot reads them with normal queries:

    -- database demo
GRANT CONNECT TO gluesync;
GRANT SELECT ON customers TO gluesync;

    In syscdcv1, the user needs the DBA privilege, because the CDC functions run in DBA mode:

    -- database syscdcv1
GRANT DBA TO gluesync;

    Repeat the SELECT grant for every table you add to the pipeline.

    Expected result

    Both databases answer Permission granted. Informix does not check the grants when you create the connection, so the first real test is the start of the entity in step 7.

    CONNECT alone is not enough in syscdcv1. A user with only CONNECT there can copy the snapshot, then the CDC part fails with Routine (cdc_opensess) can not be resolved. See the troubleshooting table.
    Create the Informix 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, choose the Informix CDC agent tile and fill Base settings:

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

      • Port: 9088, or the port of your Informix instance;

      • Database name: the database that holds the tables, demo here;

      • Username and Password: the user of step 4;

      • CDC Database Name: syscdcv1, the default. Change it only if you created the CDC database under another name;

    3. Keep Allow new lines off, unless your data holds new lines that you want to read as they are. Leave Enable TLS off unless your server uses TLS, see Enable HTTPS and TLS to databases;

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

    Agents setup wizard with the Informix CDC agent
    Figure 1. The Base settings of the Informix CDC agent.

    1. In 2. Setup target(s), choose your target agent and fill its connection. For the MySQL target of this guide, enter the host, 3306, the database, the user and the password. Turn on Allow public key retrieval and Trust server certificate if your lab server uses the default self-signed certificate. Without a trust setting, the test fails with Trust store not found, please upload a trust store when using verify server certificate. Select Test and continue, then Next;

    2. Enter a Pipeline name and select Finish.

    Expected result

    The pipeline appears in the HEALTHY group with the Informix and the target icons, and the entity table is empty. The connection test checks the sign-in. It does not check the privileges on syscdcv1, so a user that fails in step 7 still passes the test here.

    Add the entity and create the target table

    UI

    1. In the pipeline, select Entities. The Add entities page opens with the Object browser. The schema is the owner of the tables: expand informix and tick customers. The Field mapping dialog opens;

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

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

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

    Field mapping dialog with the source table informix.customers and the target table demo.customers
    Figure 2. The Field mapping of the table: Informix types on the left, the proposed MySQL types on the right.
    Expected result

    The entity appears in the pipeline on Hold as informix.customers. The statement the dialog shows for the test table is:

    CREATE TABLE `demo`.`customers` (
   `id` int,
   `name` varchar(100),
   `email` varchar(100),
   `balance` decimal(10, 2),
   `created_at` datetime(0),
   PRIMARY KEY (`id`)
)

    Data types follow the target. datetime year to second becomes datetime(0) on MySQL. The reference page lists limits for the types set, blob and lvarchar(4096), see the Informix CDC reference. 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 with a full copy

    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. The other choices are Where it stopped, which is not available before the first run, and Now, which 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 informix.customers with Start from Full copy selected
    Figure 3. The start dialog of a new entity, with Full copy and Keep and merge.

    Check the rows on the target:

    SELECT count(*) FROM demo.customers;
    Expected result

    The target holds the three rows of the source and the entity is Active. The read position of the entity, shown in Last trans., is the sequence number of the Informix logical log. If the privileges of step 4 are wrong, the rows still arrive, and the Control Plane shows the notification Error reading Ifmx CDC Engine, see the troubleshooting table. The entity keeps retrying and recovers by itself when you fix the privilege, without a restart.

    Pipeline informix-to-mysql with the entity informix.customers in the Active group
    Figure 4. The entity is active and follows the changes.
    Follow live changes and check the three operations

    SQL

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

    INSERT INTO customers VALUES (5, 'Margaret', 'margaret@example.com', 55.50, '2026-01-05 08:30:00');
UPDATE customers SET balance = 77.70, name = 'Grace Hopper' WHERE id = 3;
DELETE FROM customers WHERE id = 4;

    After a few seconds, read the target:

    SELECT id, name, balance FROM demo.customers ORDER BY id;
    Expected result

    Row 5 exists, row 3 shows Grace Hopper and 77.70, and row 4 is gone. Each statement of an Informix transaction arrives in order, one change at a time. To read the changes in batches instead, raise the entity polling interval, see Informix Troubleshooting.

    After a restart of Core Hub, or of the container that runs it, the entity resumes from its saved position and reads on from the log. In the test, the Informix log showed the CDC reader starting at the requested position, and the changes made during the stop reached the target.
    Tune the source

    UI

    1. Open the source from the pipeline list, select Advanced settings and use Add property. The list for this agent contains Source change retention in hours, Max Concurrency (0 means no limitation), Max Snapshot Concurrency (0 means no limitation), CDC client timeout, CDC idle pause in milliseconds, Query parallelism and Trim text values;

    2. Query parallelism sets the number of parallel queries of the snapshot. CDC idle pause in milliseconds sets the pause between two reads of the CDC stream when the last read returned nothing;

    3. For a faster copy, select Faster or Custom under Speed in the start dialog to raise the readers and the writers. More lanes put more load on both databases, see Tune replication throughput;

    4. If your target supports bulk operations, select Custom and switch the Writing method to Bulk load, see Bulk load.

    Saving a change to the source connection applies it at once: the CDC reader stops and starts again with the new values. All options are listed in the Informix CDC reference.

    Troubleshooting

    Symptom or message Cause Fix

    Notification Error reading Ifmx CDC Engine with Routine (cdc_opensess) can not be resolved. The snapshot rows arrive, live changes do not.

    The user has no DBA privilege in syscdcv1. The CDC functions run in DBA mode, so CONNECT is not enough.

    In syscdcv1, run GRANT DBA TO gluesync;. The entity recovers on its own at the next retry.

    Notification Error reading Ifmx CDC Engine with Database (syscdcv2) not found or no system permission. (the name is the value of CDC Database Name)

    The CDC database does not exist under that name, or the user cannot see it.

    Check the name with SELECT name FROM sysmaster:sysdatabases;. Run syscdcv1.sql if the database is missing (step 2). Correct CDC Database Name in the source and select Test and continue. The reader restarts at once.

    Core Hub log: java.sql.SQLException: No connect permission. while the CDC engine starts

    The user has no CONNECT privilege on syscdcv1, or it lost it.

    In syscdcv1, run GRANT DBA TO gluesync;. DBA includes CONNECT.

    cdc_set_fullrowlogging returns -83704

    The database of the table is not logged.

    Create the database WITH LOG, or add logging to it with your Informix backup tool, then run the function again.

    cdc_set_fullrowlogging returns -83705

    The name of the table is wrong. The first argument is <database>:<owner>.<table>.

    Check the owner of the table with the Object browser: for tables created by informix, it is informix.

    The snapshot fails with a permission error on a table

    The user has no SELECT on that table.

    Run GRANT SELECT ON <table> TO gluesync; in the database you capture.

    The target test fails with Trust store not found, please upload a trust store when using verify server certificate

    The MySQL target uses a self-signed certificate and the connection does not trust it.

    Turn on Trust server certificate, or upload the trust store, see Enable HTTPS and TLS to databases.

    Updates and deletes of a table without a primary key do not match rows on the target

    The entity has no keys, so Gluesync cannot find the row to change.

    Turn on full row logging for the table (step 3) and choose the keys, see Choose keys for tables without a primary key.
    Pipeline informix-to-mysql with the notification Error reading Ifmx CDC Engine and the message Routine cdc_opensess can not be resolved
    Figure 5. A start with a user that has no DBA privilege in syscdcv1: the snapshot rows arrive and the notification shows the server message.

    Related guides