My organization wants to migrate databases with many hundreds of millions of records and hundreds of tables from an Informix database on an AIX machine to an Informix database on a Red Hat Linux machine. I've become familiar with Enterprise Replication, and I've done a lot of experimentation with it. I've been successful 99% of the time with being able to replicate the data. However, from time to time I either encounter a situation in which the replication stalls for no apparent reason -- no lack of space, no error messages -- or else it indicates that it has finished the "synch" process for a given table, except that less than 100% of the data was sent to the new machine.

I have not had much luck with getting assistance from Informix Support for problems with Enterprise Replication.

Of course I need a solution that will work 100% of the time. My organization needs perfect results as the outcome of the replication.

Tomorrow morning I'm going to see a presentation from a sales person who works with https://www.stelodata.com, which has a third-party tool to help with replication. I'm thinking about using this Stelo product instead of the Enterprise Replication feature of Informix. I'm wondering if anyone has had experience with using this company's product or if anyone has knowledge of how helpful this software might or might not be. 

Hello

not sure what version you have used for your CDR tests. 

CDR is an area which is receiving ongoing improvements. Also there was a redesign in regards to stability and performance. 
Therefore I would suggest to use a version > 14.10.FC6 for any CDR tests (better would be to use the latest).

Regards

Hi John,

I would concur with Hedwig that 14.10.FC6+ is a vast improvement over previous releases and addressed some general issues around stalls and performance. I have seen the failure to synchronise all data on a number of occasions. An APAR was published in October last year about this:

IT47092: CDR CHECK REPLICATE -- REPAIR NOT REPAIRING ALL ROWS

So possibly this issue is now fixed? I am not sure whether any similar issue with 'cdr sync' has been resolved.

On Stelodata versus ER, Stelodata looks to be a a CDC based solution and it may well be more flexible, easier to use and put less load (and performance risk) on the database engine. These are certainly the advantages I would be interested in when evaluating such a solution.

I find 14.10.FC6+ ER generally works fine but you have to work with the constraints of defining replicates without being able to use joins and also, especially with the 14.10.FC6+ releases, it can be a bit too fast on some large synchs and a lot of logical logs can be needed. You can also see significant replication and log backup lags when it's really motoring. I have an RFE raised about this: https://ibm-data-and-ai.ideas.ibm.com/ideas/INFX-I-514

Ben.

I'd be surprised, really, if any external replication mechanism, like CDC based ones, would turn out superior to Informix' internal Enterprise Replication, esp. when looking at the greater picture (including synchronization, performance), but yes, it's got its learning curve, and certain things aren't obvious even to christened ;-)

That said, I'd like to take the opportunity to explain a few details on the APAR Ben mentioned:

IT47092: CDR CHECK REPLICATE -- REPAIR NOT REPAIRING ALL ROWS

First of all, though it might look fixed, it is not, and won't be, in the sense that the problem won't occur any more - simply because it is inherently difficult.

The problem can occur when replicating not via primary key (or, alternatively, ERKEY), but via unique index - an option that's being offered for tables not having a primary key.

One big difference between a primary key and a unique index is that the later allows NULLs  - even many, as long as the combination of all index fields is unique.

The primary purpose of this key in ER (the "replication key") is locating rows: e.g. if an update arrives ER needs to find the row to apply the update to.

In the context of "cdr sync/check", there's a secondary purpose: finding corresponding rows on participating servers, done by fetching one row from the master, then fetching the corresponding ones from other nodes using a WHERE clause with the first row's replication key values  --  WHERE field1 = val1_host_var AND field2 = val2_host_var ...

Unfortunately a NULL value in any of these host variables will not find the row it should find - that's how SQL works.
And figuring out a WHERE clause that satisfies these conditions while keeping it concise (each field and value occurring only once) is ... virtually impossible.

So any row having a NULL value in any of its replication key field will be "eclipsed" from "cdr check" and therefor not be caught as requiring repair.

What's really missing is an  is-equal-or-both-null operator that's cheap and also taking host variables - who else ever felt a need for this?

You judge for yourself if this was your case.

BR,
 Andreas

------------------------------
Andreas Legner
Informix Dev
HCL Software
------------------------------

Hi John, 

We have our own Java-based, CDC solution package. Stable running for some 100 million TX a day, for years. However, the CDC is not the same as ER - it has a different focus and use case. 
We have many years of experience with every kind of ER use case for years, too. If you need help on this topic, send me a PM .

Best regards

Henri