Source IBM Informix #
replicant-cli will be referred to as the
$REPLICANT_HOME directory in the proceeding steps.
I. Set up CDC Replication #
For enabling CDC-based replication from the source Informix server, please follow the instructions in Enabling CDC Replication for Informix.
II. Set up Logical Log Configuration #
For setting up logical log configuration, follow the instructions in Logical Log Configuration Guidelines.
III. Create the Heartbeat Table #
For CDC replication, you must create the heartbeat table on the source database with the following DDL:
Remember to grant
DELETE privileges for this table to the user that you provided to Replicant.
CREATE TABLE tpch:tpch.replicate_io_cdc_heartbeat(timestamp INT8 NOT NULL, PRIMARY KEY(timestamp) CONSTRAINT cdc_heartbeat_id_repl1_repl1) LOCK MODE ROW
IV. Set up Connection Configuration #
$REPLICANT_HOME, navigate to the sample connection configuration file:
If you store your connection credentials in AWS Secrets Manager, you can tell Replicant to retrieve them. For more information, see Retrieve credentials from AWS Secrets Manager.
Otherwise, you can put your credentials like usernames and passwords in plain form like the sample below:
type: INFORMIX host: localhost #Replace localhost with your Informix server's hostname port: 9088 # In case of SSL connection use SSL port server: 'informix' database: 'tpch' # Name of the catalog from which the tables are to be replicated username: 'informix' #Replace informix with the user that connects to your Informix server password: 'in4mix' #Replace in4mix with your user's password informix-user-password: 'in4mix' #Password for the "informix" user, required for performing CDC. Not required in snapshot replication. max-connections: 15 max-retries: #Number of times any operation on the source system will be re-attempted on failures. #ssl: # trust-store: # enable: true # path: "/home/informix/ssl/truststore.jks" #Path to the JKS truststore containing the trust certificate of the Informix server # password: "in4mix" #The truststore password
You have to connect to the syscdcv1 catalogue on the server as the user
informixto be able to use Change Data Capture (CDC). The
informix-user-passwordparameter of the config file above should have the password of user
informix. For more information, see Preparing to use the Change Data Capture API on IBM Informix Documentation.
You can use SSL to connect to the Informix server using the configuration parameters shown above. To konw about Informix server side SSL setup, see Configuring Informix server instance for SSL connections on IBM Informix Documentation.
V. Set up Extractor Configuration #
$REPLICANT_HOME, navigate to the Extractor configuration file:
The configuration file has two parts:
- Parameters related to snapshot mode.
- Parameters related to realtime mode.
Parameters related to snapshot mode #
For snapshot mode, make the necessary changes as follows:
snapshot: threads: 16 fetch-size-rows: 5_000 # lock: # enable: true # scope: TABLE # DATABASE, TABLE # force: false # timeout-sec: 5 # min-job-size-rows: 1_000_000 # max-jobs-per-chunk: 32 split-method: MODULO # Allowed values are RANGE, MODULO per-table-config: - catalog: "tpch" schema: "tpch" tables: lineitem: split-key: "l_orderkey" # row-identifier-key: ["l_linenumber", "l_orderkey"] # split-method : MODULO # Table specific overridable config : allowed values are RANGE, MODULO # num-jobs: 1 orders: split-key: "o_orderkey" # num-jobs: 1
Parameters related to realtime mode #
For operating in realtime mode, use the
realtimesection to specify your configuration. The following parameters are specific to Informix:
db-charset[v22.02.12.23]: JDK equivalent of Informix code set. For example, UTF-8, ISO-8859-1 etc.
start-position: This parameter allows using user specified sequence number or timestamp to start reading Change Data Capture operations.
sequence-number: Specifies the start position for the Change Data Capture.
timestamp-ms: Optional parameter. Used as a filter—any transaction with commit timestamp older than this is ignored. Should be used together with
sequence-numberfor finer tuning.
false. If set to
true, it does two things:
a. Obtains the current
sequence-numberfrom the Informix server and logs it in the
trace.logfile. The log has the following format:
Successfully created replication checkpoint at CDC sequence number: <seq_number>
b. Sets up a checkpoint, from which you can resume the replication at any point. You can do so by appending the
--resumeargument to the replication command that created the checkpoint.
The following is a sample configuration for realtime mode:
realtime: threads: 4 fetch-size-rows: 256 _buffer-size: 1000 _read-timeout: 3 db-charset: UTF-8 start-position: # create-checkpoint: false # sequence-number: 51553788137 # timestamp-ms: 1647378865000 heartbeat: enable: true catalog: tpch schema: tpch interval-ms: 10000
With Informix as Source, you have the option to use the Source Column Transformation feature of Replicant for the following pipelines:
- Informix to PostgreSQL
- Informix to Kafka
For more information, see Source Column Transformation.
Example of creating a checkpoint and resuming replication #
Let’s assume you used the following command to start the replication, with your
realtimeconfiguration same as above:
./bin/replicant real-time conf/conn/informix.yaml conf/conn/postgresql.yaml \ --filter filter/informix_filter.yaml \ --extractor conf/src/informix.yaml \ --replace
trace.logfile, you wil find the
sequence-numbercorresponding to this replication job.
With a valid
true, you can resume replication from the position of the most recently created checkpoint (which is the checkpoint you created via the previous command) in the following way:
./bin/replicant real-time conf/conn/informix.yaml conf/conn/postgresql.yaml \ --filter filter/informix_filter.yaml \ --extractor conf/src/informix.yaml \ --replace --resume
For a detailed explanation of configuration parameters in the Extractor file, read Extractor Reference.