Migrate Data Continuously from IBM Informix Database to AWS S3

Hi all, I have a requirement to send data from IBM Informix database to AWS S3, is there a solution that you could suggest for me? The data traffic should be initiated from Informix to cloud Thanks in advance, Eralper ------------------------------ Eralper Yilmaz ------------------------------

If it is informix to informix then use ER If it is informix to non-informix then there are tools out there to do it, SQDR being one (expensive and nearly OK), SymmetricDS (free and I have heard not that reliable). Using the CDC API you can write our own. Writing the actual capture using the APIs is trivial, building a transactionally robust solution takes a little longer. Cheers Paul

Eralper: AS Paul noted, if you need to sync an on-prem Informix server with a remote Informix server on AWS S3 then Enterprise Replication is the way to go. It is fast, reliable, and solid technology that has been in Informix for over 20 years. If you need to move the data to a different RDBMS or any other DBMS on the cloud, then you have several choices: There are 3rd party database replication products out there. Most take care of the initial data copy as well as maintaining the source and target in sync. As Paul noted some of these are not cheap. You can use the IBM Change Data Capture utility which links itself into the Informix CDC feature (see below), but that is also expensive. You will have to make an initial copy of the base data using export/import utilities (notes below on that as well). Informix itself offers three features for capturing changes to the data that you can use to write your own data sync application: Change Data Capture (CDC) - This is an industry standard API for capturing changes to data. You write an application that registers to receive inserts, updates, and/or deletes from specified tables which arrive as notices to which the application responds by requesting the new data through the large object read API functions which return standard CDC format records. Smart Triggers aka Push Data Triggers - This feature is similar to CDC offering two APIs, one for Java code (Smart Trigger API) and one for "C" code (Push Data Triggers). You write an external application that registers for the changes it wants to see using simple SELECT statements with optional WHERE filters then loops on a call to an API function that returns with the next appropriate data as JSON records. Asynchronous Post Commit Triggers - This works through the Informix v14.10 ER feature that sends changes to registered tables to a stored routine which runs inside the database and can be written in SPL (Informix's Stored Procedure Language), Java, or "C". The routine can do whatever it needs to with the data which is presented either in internal binary or JSON format. The routine can store the data in a table, hand it off to an external application, or send it as messages to MQTT or other transport services. I did a presentation on these latter three options for the IIUG Tech Talks. Here's the link: Informix Tech Talks - Three ways to capture data changes when they happen! by Art Kagel ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

If you decide do write your own don't underestimate the effort needed to be transactionally consistent nor the ability to stop/restart/catchup the replication. The capture is the trivial part:). It can be done though, I just completed a CDC based Informix to MariaDB project for a client. https://www.oninit.com/presentations/cdc.ppxs [PP Slide show] was something I did for WAIUG a while back, the latter half discusses the cdcapi.ec that ships with the demo code Cheers Paul

WAIUG just fixed the link - https://youtu.be/cXYCYa8Z4Sg Cheers Paul

Another option that you can look into if you are considering a cloud service is AWS Glue, here's a link with a basic setup - https://www.cdata.com/kb/tech/ibminformix-jdbc-aws-glue.rst . We use a similar pattern for our Datalake. Regards, Edcel

Hi Edcel, Unfortunately I have network security rules that prevent opening a connection from outside into our on-prem resources preventing AWS Glue to access Informix database via JDBC. But thanks for sharing the document which can help for using Glue for accessing other platforms within AWS ------------------------------ Eralper Yilmaz ------------------------------

Hi Eralper, we have leoloXCDC in use by our customers for such situations. It is one of the tools that use the Informix CDC API. We are currently putting it on the IBM RedHat marketplace ( https://marketplace.redhat.com/en-us/preview/products/leoloxcdc ) . It is many years proven, cost-effective, with very low impact on the servers, and stable. Write me directly if you want to test it. Best Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion 2021 2022 2023 IIUG Board Member henri.cujass@leolo.com ------------------------------