Sync data from Informix to Oracle using Debezium/Kafka Connect/Kafka/JDBC Sink

 Following commands and details will be helpful when configuring Debezium to sync data from Informix to Oracle database.

1. Docker compose to create Kafka connect, Kafka, Zookeeper

Copy Informix connector and streaming jar files into connectors directory in docker compose file exists

services:
  zookeeper:
    image: confluentinc/cp-zookeeper:7.5.0
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
    ports:
      - "2181:2181"
  kafka:
    image: confluentinc/cp-kafka:7.5.0
    ports:
      - "9092:9092"
    environment:
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:9092
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
  connect:
    image: my-debezium-connect  # Custom image you’ll build below
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "8083:8083"
    environment:
      BOOTSTRAP_SERVERS: kafka:9092
      GROUP_ID: connect-cluster
      CONFIG_STORAGE_TOPIC: connect-configs
      OFFSET_STORAGE_TOPIC: connect-offsets
      STATUS_STORAGE_TOPIC: connect-status
      KEY_CONVERTER: org.apache.kafka.connect.json.JsonConverter
      VALUE_CONVERTER: org.apache.kafka.connect.json.JsonConverter
      CONFIG_STORAGE_REPLICATION_FACTOR: 1
      OFFSET_STORAGE_REPLICATION_FACTOR: 1
      STATUS_STORAGE_REPLICATION_FACTOR: 1
      VALUE_CONVERTER_SCHEMAS_ENABLE: "false"
      KEY_CONVERTER_SCHEMAS_ENABLE: "false"
    volumes:
      - ./connectors:/kafka/connectors

2. Build docker compose

sudo docker compose build --no-cache

3. Stop docker compose docker containers

sudo docker compose down

4. Start the docker compose containers

sudo docker compose up -d

5. Check the docker connect container logs

sudo docker compose logs -f connect

6. Informix Kafka connect json

{

  "name": "informix-source-connector",

  "config": {

    "connector.class": "io.debezium.connector.informix.InformixConnector",

    "tasks.max": "1",

    "database.hostname": "172.27.xx.xx",

    "database.port": "1528",

    "database.user": "xxxx",

    "database.password": "xxxx@23",

    "database.dbname": "xxxx",

    "database.server.name": "informix",

    "database.informixserver":"debezium_test",

    "topic.prefix": "informixcdc",

    "table.include.list": "table1",

    "snapshot.mode": "schema_only",

    "name": "informix-source-connector",

    "schema.history.internal.kafka.bootstrap.servers": "kafka:9092",

    "schema.history.internal.kafka.topic": "schema-changes.informix"

  }

}

7. Deploy Informix source connector to Kafka connect

curl -X POST http://localhost:8083/connectors -H "Content-Type: application/json" -d @informix-source.json|jq

8. Check the status of the connector

sudo curl http://localhost:8083/connectors/informix-source-connector/status | jq

9. Check the Kafka connect deployed connectors

curl -s http://localhost:8083/connector-plugins | jq

10. Delete Informix source connector

sudo curl -X DELETE http://localhost:8083/connectors/informix-source-connector

11. Login to Kafka connect container 

sudo docker exec -it cdc1-connect-1 sh

12. Oracle JDBC sink connector

{

  "name": "oracle-sink-connector",

  "config": {

    "connector.class": "io.debezium.connector.jdbc.JdbcSinkConnector",

    "tasks.max": "1",

    "topics": "informixcdc.informix.table1",

    "connection.url": "jdbc:oracle:thin:@//172.27.xx.xxx:1521/PDB_TST",

    "connection.username": "TEST",

    "connection.password": "test123",

    "connection.driver": "oracle.jdbc.OracleDriver",

    "insert.mode": "upsert",

    "delete.mode": "none",

    "primary.key.fields": "mobile_no",

    "primary.key.mode": "record_value",

    "table.name.format": "table1",

    "auto.evolve": "false",

    "auto.create": "false",

    "dialect.name": "OracleDatabase",

    "schema.evolution":"basic"

  }

}

13. Deploy Oracle sink connector

curl -X POST -H "Content-Type: application/json" --data @oracle-sink.json http://localhost:8083/connectors

14. Check Oracle sink connector status

curl http://localhost:8083/connectors/oracle-sink-connector/status|jq

15. Restart Oracle sink connector

curl -X POST http://localhost:8083/connectors/oracle-sink-connector/tasks/0/restart

16. Check configuration of Oracle connector

curl http://localhost:8083/connectors/oracle-sink-connector/config | jq

17. Login to Kafka and check first 5 messages in the Topic

docker exec -it cdc1-kafka_1 sh -c "kafka-console-consumer.sh --bootstrap-server localhost:9092 --topic informixcdc.informix.table1 --from-beginning"

/bin/kafka-console-consumer   --bootstrap-server kafka:9092   --topic informixcdc.informix.smgt004   --from-beginning   --max-messages 5

18. Retrieve details of the Topic

/bin/kafka-topics --bootstrap-server kafka:9092 --describe --topic informixcdc.informix.smgt004

Informix query optimizer

 

The Informix Query Optimizer

• The query optimizer attempts to determine the most efficient way to execute a SQL statement

 • Examines every possible method to implement the query and selects the least costly method

• It is Dynamic, so when data changes the same SQL can select a better path

How Optimizes the SQL statement to determine the best access method

– Which table to read first….

– Which index to use first…

– Which filter to use first…

 

How to Turn ON Query Explain? To See the SQL Optimizer Query Plan

After executes below queries with “SET EXPLAIN” it is creates a output file called “SQL_EXPLAIN.OUT”

Method#1

                SET EXPLAIN ON -

                                Display the query plan that optimizer chooses, then execute the query.

                                Example:

                                                set explain on;

                                                select cust_name,cust_addrs from customer where cust_code=”C001”;

Method#2

                SET EXPLAIN ON FILE TO [“path/to/filename”]

                                Save the query plan into a specific file also execute the query.

Can useful when programming unit testing.

Method#3

                SET EXPLAIN ON AVOID_EXECUTE

                                Display the query plan that optimizer chooses, but do not execute the query.

Can useful when a query takes hours to run/for time consuming queries.

“SQL_EXPLAIN.OUT” file results explanation

A. Show SQL/SELECT statement.

B. Query cost

C. Number of Rows Expected to return

D. Order to access tables during execution.

E. Access Plan:

                SEQUENTIAL Scan - read all rows in sequence

                INDEX PATH - Scan 1 or more indexes

                AUTO INDEX PATH: Optimizer suggest to create an indexes.

F. Query Statistics

Shows tables map, estimated row count and estimated query costs step by step.

                This will helps to find out in which part of the query cost is high.

                So can change the query based on results output.

Retrieve few minutes older records from SQL in Informix

You can use following SQL to retrieve records older than 15 minutes in Informix. init_time is in datetime format.  

select * from table_name where init_time > (current - 15 units minute)

Informix 12.10 docker image (Rest Services)

You can find the docker image for "informix 12.10" database at following location. Informix 12.10 is rest enabled. That means CRUD opreations can be done using rest services without JDBC connectivity.

https://hub.docker.com/r/ibmcom/informix-developer-database/

For demonstration purposes, you can use above docker image as a informix database.

You can use standard http requests to CRUD operations.

insert = POST

get = GET

update = PUT 

delete = DELETE etc

Sample requests

Sample Testing

database = test_db

table = test_tbl

table schema = id int(11), name varchar(20)

Insert request

http://192.168.111.132:27018/test_db/test_tbl

{

  "id":0,

  "name":"sujith"

}

Get request

http://192.168.111.132:27018/test_db/test_tbl

{

{

  "id":0,

  "name":"sujith"

},

{

  "id":1,

  "name":"delachithra"

}

}

Update request

http://192.168.111.132:27018/test_db/test_tbl/id/2

{

  "id":2,

  "name":"test update"

}

Delete request

http://192.168.111.132:27018/test_db/test_tbl/id/2

Execute procedure in informix

Following command can be used to execute procedure in informix database server.
In informix "dbaccess" shell, execute the following command. This will run the "this_is_it" procedure with three parameters "1", "2" and "3".

execute procedure this_is_it ('1','2','3');

create procedure this_is_it(this CHAR(3), that CHAR(4), other CHAR(3))
select * from inff_charge into temp this_is_it;
end procedure;

Informix set connection timeout

jdbc:informix-sqli://192.168.2.15:1528/database:INFORMIXSERVER=online;user=sujith;password=sujith;INFORMIXCONRETRY=3;INFORMIXCONTIME=60

Above JDBC connection URL can be used for connection timeout issues in Informix database.

INFORMIXCONRETRY : connection retry times
INFORMIXCONTIME : connection time

Connection timeout can be occurred due to database slowness and after maximum connection reach.
You have to change JDBC connection URL.

Copy data from one table to another table

You can copy data from one table to another table using following SQL command. This worked fine in informix database.

insert into TableA (field1, field2,field3)
select  field1, field2,field3 from TableB where added_on=today

Informix Database LOCK Transaction for UPDATE

In some cases, we have to lock records for update. Below code segments can be used for locking table and update desired record in informix database server.

• statement.executeUpdate("Begin Work");
• statement.executeUpdate("set transaction isolation level serializable");

Above code must be used be before the SELECT statement execution which must be UPDATE.

• String sql_1="select * from table where name='sujith' FOR UPDATE";

Execute the sql and proceed with the business logic. When you want to UPDATE the table do the following.

• String sql_2=update table set name="delp" where name="sujith";

Execute the UPDATE statement, like above statement.

Then use below command for commiting transaction to database.

• statement.executeUpdate("commit");

limit Resultset size in informix

Use the following SQL to limit the size of the result set in informix. 

SELECT FIRST 1 * FROM orders

How to find informix cost factor

Cost factor of a table is used to measure the performance of the sql executions. When your table cost factor is high, it will decrease the sql exection . That means it will get some times to generate resultsets of the sql.
Here is the way to measure informix cost factor.
Example :-
set explain on;
select * from table

• Use set explain on; before your sql stetement in telnet client in dbaccess mode in informix.
• Then exit dbaccess mode and type "ls -ltrh" in your home directory.
• This will list all the files. Go to end. You will find a file called as "sqexplain.out".
• Vi the file. Then press "shift+g" to end of the file to see your table cost factor details.

Unload/load data from informix

unload to "table.txt" select * from table

This will unload data to your user login in database server. Suppose you logged as "test" user, it will unload data to folder "test".

load data from "table.txt" insert into table

Use this sql to load data from "table.txt" to a table.

create temporary table in informix

create temp table incomplete(id serial," +
"mobile_no varchar(50)," +
"totalNumber integer," +
"repeated integer," +
"achieved varchar(50))WITH NO LOG

when you execute the sql it will make the temporary table "incomplete" in database server temp space with no logs.

when you close the connection this type of table will be dropped. Sometime, you may have to drop it manully too.