Hi,

I'm facing a database out of sync problem in our application and would
like the communities help.

We have 30 tables in Oracle database and 30 replica tables in Informix
(similar schema). We have a process that unloads daily
inserts/updates/deletes in Oracle and loads it into the Informix
tables. These tables have gone out of sync over the years and requires
to be resync'ed asap.

On Informix tables, there are insert/update/delete triggers which
populate the changes in a corresponding temporary table, hence any
activity in the Informix tables fires these triggers and updates the
same in temporary tables.

A few tables have records in the order of 100,000,000 and other tables
also have large number of rows. Our problem is to re-sync the Informix
tables with Oracle tables in such a way so that triggers on the
Informix tables are fired for the actual records
Inserted/Updated/Deleted in the Informix tables (i.e. the actual data
which was out of sync). This is required to be done in this fashion so
that the temporary tables have only the missed out data over the years
and not the full load after the re-sync has been done.

This activity would require an outage on our system hence needs to be
done in the most efficient manner.

I would appreciate your help in guiding me towards a effective
solution.

ndmathur@gmail.com napisa³(a):
> Hi,
>
> I'm facing a database out of sync problem in our application and would
> like the communities help.
>
> We have 30 tables in Oracle database and 30 replica tables in Informix
> (similar schema). We have a process that unloads daily
> inserts/updates/deletes in Oracle and loads it into the Informix
> tables. These tables have gone out of sync over the years and requires
> to be resync'ed asap.
>
> On Informix tables, there are insert/update/delete triggers which
> populate the changes in a corresponding temporary table, hence any
> activity in the Informix tables fires these triggers and updates the
> same in temporary tables.
>
> A few tables have records in the order of 100,000,000 and other tables
> also have large number of rows. Our problem is to re-sync the Informix
> tables with Oracle tables in such a way so that triggers on the
> Informix tables are fired for the actual records
> Inserted/Updated/Deleted in the Informix tables (i.e. the actual data
> which was out of sync). This is required to be done in this fashion so
> that the temporary tables have only the missed out data over the years
> and not the full load after the re-sync has been done.
>
> This activity would require an outage on our system hence needs to be
> done in the most efficient manner.
>
> I would appreciate your help in guiding me towards a effective
> solution.
>
I've wrote utility for syncronising Informix databases. It is available
in IIUG downloads as load2 archive, utility named dbdiff (see
description in dbdiff.txt file).
Possible you can use it. The only condition is that you have to get full
unload of Oracle tables in Informix unl format.
Then dbdiff can compare these unloads with Informix database. As a
result you will obtain file with missing deletes/inserts/updates.
I do not have information if this works for other people. I know that
this utility works ok for me.

There is a free, open-source, cross-platform (operating system and
database) tool called SchemaCrawler on SourceForge that will compare
schemas between two or more different systems. SchemaCrawler outputs
details of your schema (and data) in a diff-able plain-text format
(text, CSV, or XHTML). You can use a standard diff program to diff the
current output with a reference version of the output. SchemaCrawler
can be run either from the command line, or as an ant task. A lot of
examples are available with the download to help you get started.

http://schemacrawler.sourceforge.net/

Sualeh Fatehi.

dono how your setup exactly looks; however
if you have constraints on them to
garantee uniquenes or??
you may want to look at a thing called violations tables and have your
constraints
set to filterring without error.
check the manual for it do not have the syntax at hand sorry.

it boils down to

tab1( col1int, col2 char) has pk on col1
and has say 2 records in it

say

1,"one"
2,"two"

when you reinsert 1,"one" it does not end
up in tab1 but in it's violation table.

so if you want to get rid of the unwanted you can get rid of the
violation table
stop violations and set your constraints to be 'normal' again.

you also can use dbload and set the
# of errors big i guess.


Superboer.