Database synchronization
A user wanted to replicate selected tables from IDS 9.4 to IDS 10 (and possibly later to Oracle/MySQL/Postgres), with differing table names/structures, while avoiding ER, HDR, triggers and UDRs, and asked whether logical logs could be read directly for change capture. Replies said no: apart from onlog there's no supported way to mine logical logs — ER is the built-in tool (and it does handle differing table names/columns), but it isn't available in Workgroup Edition. Suggested alternatives: triggers calling a C or Java UDR that pushes rows to a file/MQ pipeline or via JDBC, a change-log helper table drained by a periodic sync job, cross-server INSERT...SELECT for Informix-to-Informix, or ETL/replication products (Ascential DataStage, WebSphere Replication/Federation Server). No single choice was confirmed by the poster.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi guys,
we have a database running on Informix 9.4.
We want to setup a new database running on Informix 10 and we want to replicate
some of our tables from the first instance to the second one.
Tables don't have the same structure accross these two databases.
We don't want to use ER or HDR.
Is there a way to synchronize tables between these two databases without using
triggers and UDR?
Is there a possibility to read information from logical logs to capture
transactions (from the first instance) we want to replicate to the second
instance (as ER does)?Except onlog, is there a utility for reading information
from logical logs?
Do you know some ETL that does the job? Currently the destination database is
running on Informix 10 but in the future we might support other DBMS like
Postgres,MySQL,Oracle ...
Thanks
Sounds like ER is the tool you want. Why do you resist? Resistance is futile!
Art S. Kagel
----- Original Message -----
From: Georges Martin <ids@iiug.org>
At: 2/23 9:37:15
Hi guys,
we have a database running on Informix 9.4.
We want to setup a new database running on Informix 10 and we want to
replicate
some of our tables from the first instance to the second one.
Tables don't have the same structure accross these two databases.
We don't want to use ER or HDR.
Is there a way to synchronize tables between these two databases without using
triggers and UDR?
Is there a possibility to read information from logical logs to capture
transactions (from the first instance) we want to replicate to the second
instance (as ER does)?Except onlog, is there a utility for reading information
from logical logs?
Do you know some ETL that does the job? Currently the destination database is
running on Informix 10 but in the future we might support other DBMS like
Postgres,MySQL,Oracle ...
Thanks
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Art for your answer
One reason is that we're using the IDS workgroup edition and as I know it
does not support replication.
The other reason is that the destination database might later be replaced by
another DBMS like Oracle, MySQL or Postgres and in that case we won't be
able to use ER.
For my information, is it possible to replicate between 2 tables that
haven't the same name and the same structure?
Thanks
>From: "ART KAGEL, BLOOMBERG/ 731 LEXIN" <kagel@bloomberg.net>
>Reply-To: ids@iiug.org
>To: ids@iiug.org
>Subject: Re: Database synchronization [8487]
>Date: Fri, 23 Feb 2007 09:42:02 -0500 (EST)
>
>Sounds like ER is the tool you want. Why do you resist? Resistance is
>futile!
>
>Art S. Kagel
>----- Original Message -----
>From: Georges Martin <ids@iiug.org>
>At: 2/23 9:37:15
>
>Hi guys,
>we have a database running on Informix 9.4.
>We want to setup a new database running on Informix 10 and we want to
>replicate
>some of our tables from the first instance to the second one.
>Tables don't have the same structure accross these two databases.
>We don't want to use ER or HDR.
>Is there a way to synchronize tables between these two databases without
>using
>triggers and UDR?
>Is there a possibility to read information from logical logs to capture
>transactions (from the first instance) we want to replicate to the second
>instance (as ER does)?Except onlog, is there a utility for reading
>information
>from logical logs?
>Do you know some ETL that does the job? Currently the destination database
>is
>running on Informix 10 but in the future we might support other DBMS like
>Postgres,MySQL,Oracle ...
>
>Thanks
>
>
>*******************************************************************************
>Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
Your Space. Your Friends. Your Stories. Share your world with Windows Live
Spaces. http://spaces.live.com/?mkt=en-ca
ids-bounces@iiug.org wrote on 02/23/2007 03:36:26 PM:
> Hi guys,
> we have a database running on Informix 9.4.
> We want to setup a new database running on Informix 10 and we want to=
> replicate
> some of our tables from the first instance to the second one.
> Tables don't have the same structure accross these two databases.
> We don't want to use ER or HDR.
> Is there a way to synchronize tables between these two databases
> without using
> triggers and UDR?
> Is there a possibility to read information from logical logs to captu=
re
> transactions (from the first instance) we want to replicate to the se=
cond
> instance (as ER does)?Except onlog, is there a utility for reading
> information
> from logical logs?
No, not possible - unless you use ER. Why don't you want to use ER ?
> Do you know some ETL that does the job? Currently the destination
database is
> running on Informix 10 but in the future we might support other DBMS =
like
> Postgres,MySQL,Oracle ...
Ascential Datastage is one ETL tool I know of.
Mit freundlichen Gr=FCssen - With kind regards - Cordialement
Tilman Model-Bosch
IBM SWG Premium Support
-----------------------------------------------------------------------=
--------
IBM Deutschland GmbH
Vorsitzender des Aufsichtsrats: Hans Ulrich Maerki
Gesch=E4ftsf=FChrung: Martin Jetter (Vorsitzender), Rudolf Bauer, Chris=
tian
Diedrich, Christoph Grandpierre, Matthias Hartmann, Andreas Kerstan
Sitz der Gesellschaft: Stuttgart; Registergericht: Amtsgericht Stuttgar=
t,
HRB 14562 WEEE-Reg.-Nr. DE 99369940=
I understand your points.
On the last item, yes ER is fully configurable. The source and target tables
don't need to be anything but compatible for the specific columns being
replicated/distributed/collected.
Your best bet would be a trigger that calls a UDR written in C to perform the
replication by passing the record contents to some service that can be made
generic ODBC so that the target can be anything. Altogether not hard to do.
The UDR can be very simple. Just take the row contents and push them into a
reliable pipeline (file, MQ Series, etc.) and forget it. All of the complexity
can be in the consumer of the pipeline.
Art
----- Original Message -----
From: Georges Martin <ids@iiug.org>
At: 2/23 10:00:28
Thanks Art for your answer
One reason is that we're using the IDS workgroup edition and as I know it
does not support replication.
The other reason is that the destination database might later be replaced by
another DBMS like Oracle, MySQL or Postgres and in that case we won't be
able to use ER.
For my information, is it possible to replicate between 2 tables that
haven't the same name and the same structure?
Thanks
>From: "ART KAGEL, BLOOMBERG/ 731 LEXIN" <kagel@bloomberg.net>
>Reply-To: ids@iiug.org
>To: ids@iiug.org
>Subject: Re: Database synchronization [8487]
>Date: Fri, 23 Feb 2007 09:42:02 -0500 (EST)
>
>Sounds like ER is the tool you want. Why do you resist? Resistance is
>futile!
>
>Art S. Kagel
>----- Original Message -----
>From: Georges Martin <ids@iiug.org>
>At: 2/23 9:37:15
>
>Hi guys,
>we have a database running on Informix 9.4.
>We want to setup a new database running on Informix 10 and we want to
>replicate
>some of our tables from the first instance to the second one.
>Tables don't have the same structure accross these two databases.
>We don't want to use ER or HDR.
>Is there a way to synchronize tables between these two databases without
>using
>triggers and UDR?
>Is there a possibility to read information from logical logs to capture
>transactions (from the first instance) we want to replicate to the second
>instance (as ER does)?Except onlog, is there a utility for reading
>information
>from logical logs?
>Do you know some ETL that does the job? Currently the destination database
>is
>running on Informix 10 but in the future we might support other DBMS like
>Postgres,MySQL,Oracle ...
>
>Thanks
>
>
>*******************************************************************************
>Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
Your Space. Your Friends. Your Stories. Share your world with Windows Live
Spaces. http://spaces.live.com/?mkt=en-ca
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Georges,
Look at the description of WebSphere Replication Server and WebSphere
Federation Server (its old name was WebSphere Information Integrator
Standard (or Advanced) edition).
With WFS, you could federate heterogenous databases (DB2, Informix, Ora=
cle,
MS SQL, Teradata, ODBC resources...) and more (Flat files, XML files,
WebSphere MQ message,...).
And with WRS, you could replicate your modifications from/to heterogeno=
us
databases (DB2, Informix, Oracle, Sybase, MS SQL Server, ...). It's a
powerful product with many features :
- You have a graphical tool to define and supervize your replicat=
es.
- If you have severals tables and severals sources/target, you co=
uld
write scripts to define and manage your replicates.
- You could select the columns to replicate
- You could add filters to the replication flow
- The products manages technical tables in order to garantee to
resend the modifications if a error occurs (network problem)
- ...
Link to WRS :
http://www-306.ibm.com/software/data/integration/replication_server/
Link to WFS :
http://www-306.ibm.com/software/data/integration/federation_server/
Regards.
________________________________
Philippe PIGNON
DB2 Information Management IT Specialist
IBM Software Group - Lab Services
T=E9l : + 33 1 49 05 37 79
Mobile :+ 33 6 74 93 35 69
Tour Descartes, 2 avenue Gambetta
92400 Courbevoie
http://www.ibm.com/software/fr/
________________________________
=
"GEORGES MARTIN" =
<georges_martin_1 =
@hotmail.com> =
To
Sent by: ids@iiug.org =
ids-bounces@iiug. =
cc
org =
Subj=
ect
Database synchronization [8486]=
23/02/2007 15:36 =
=
=
Please respond to =
ids@iiug.org =
=
=
Hi guys,
we have a database running on Informix 9.4.
We want to setup a new database running on Informix 10 and we want to
replicate
some of our tables from the first instance to the second one.
Tables don't have the same structure accross these two databases.
We don't want to use ER or HDR.
Is there a way to synchronize tables between these two databases withou=
t
using
triggers and UDR?
Is there a possibility to read information from logical logs to capture=
transactions (from the first instance) we want to replicate to the seco=
nd
instance (as ER does)?Except onlog, is there a utility for reading
information
from logical logs?
Do you know some ETL that does the job? Currently the destination datab=
ase
is
running on Informix 10 but in the future we might support other DBMS li=
ke
Postgres,MySQL,Oracle ...
Thanks
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
If you are smart you will stay on informix. Otherwise this will not work.
DISCLAIMER - I haven't tried this but it should work. Please test first
insert into dbname@service_name:owner.tabname --remote table to add data
select * from local_tabname
where someid not in (select someid from dbname@service_name:owner.tabname)
-- make sure you are not adding dupes
"Georges Martin"
<georges_martin_1
@hotmail.com> To
Sent by: ids@iiug.org
ids-bounces@iiug. cc
org
Subject
Re: Database synchronization
02/23/2007 10:00 [8488]
AM
Please respond to
ids@iiug.org
Thanks Art for your answer
One reason is that we're using the IDS workgroup edition and as I know it
does not support replication.
The other reason is that the destination database might later be replaced
by
another DBMS like Oracle, MySQL or Postgres and in that case we won't be
able to use ER.
For my information, is it possible to replicate between 2 tables that
haven't the same name and the same structure?
Thanks
>From: "ART KAGEL, BLOOMBERG/ 731 LEXIN" <kagel@bloomberg.net>
>Reply-To: ids@iiug.org
>To: ids@iiug.org
>Subject: Re: Database synchronization [8487]
>Date: Fri, 23 Feb 2007 09:42:02 -0500 (EST)
>
>Sounds like ER is the tool you want. Why do you resist? Resistance is
>futile!
>
>Art S. Kagel
>----- Original Message -----
>From: Georges Martin <ids@iiug.org>
>At: 2/23 9:37:15
>
>Hi guys,
>we have a database running on Informix 9.4.
>We want to setup a new database running on Informix 10 and we want to
>replicate
>some of our tables from the first instance to the second one.
>Tables don't have the same structure accross these two databases.
>We don't want to use ER or HDR.
>Is there a way to synchronize tables between these two databases without
>using
>triggers and UDR?
>Is there a possibility to read information from logical logs to capture
>transactions (from the first instance) we want to replicate to the second
>instance (as ER does)?Except onlog, is there a utility for reading
>information
>from logical logs?
>Do you know some ETL that does the job? Currently the destination database
>is
>running on Informix 10 but in the future we might support other DBMS like
>Postgres,MySQL,Oracle ...
>
>Thanks
>
>
>*******************************************************************************
>Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
Your Space. Your Friends. Your Stories. Share your world with Windows Live
Spaces. http://spaces.live.com/?mkt=en-ca
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi,
to synchronize with a different database, you could create some triggers,
calling a stored procedure in Java, which keeps the second instance up to date
by using JDBC.
The problem is only if the second instance is not available. Then your primary
operation either fails or the replication does not take place.
You could program around this e.g with a second table which is filled on
modification of the primary table using triggers (holding only primary key and
type of modification), and do the synchronization in a job (synchronize once
per minute, you have to build a little logic round this, because the data
could be changed more than once in the interval, or you have a delete,
followed by an insert with the same primary key).
We do something like that (including modification of the data on the fly). You
can run in locking problems (you should delete the records in the helper table
after synchronize), but generally, this is a working approach.
Of course, any ETL Tool should be able to do the job (and will do a similar
thing).
Marcus
----- Originalnachricht -----
Von: Darren_Jacobs@carmax.com
Gesendet: Fre, 23.2.2007 22:25
An: ids@iiug.org
Betreff: Re: Database synchronization [8496]
If you are smart you will stay on informix. Otherwise this will not work.
DISCLAIMER - I haven't tried this but it should work. Please test first
insert into dbname@service_name:owner.tabname --remote table to add data
select * from local_tabname
where someid not in (select someid from dbname@service_name:owner.tabname)
-- make sure you are not adding dupes
"Georges Martin"
<georges_martin_1
@hotmail.com> To
Sent by: ids@iiug.org
ids-bounces@iiug. cc
org
Subject
Re: Database synchronization
02/23/2007 10:00 [8488]
AM
Please respond to
ids@iiug.org
Thanks Art for your answer
One reason is that we're using the IDS workgroup edition and as I know it
does not support replication.
The other reason is that the destination database might later be replaced
by
another DBMS like Oracle, MySQL or Postgres and in that case we won't be
able to use ER.
For my information, is it possible to replicate between 2 tables that
haven't the same name and the same structure?
Thanks
>From: "ART KAGEL, BLOOMBERG/ 731 LEXIN" <kagel@bloomberg.net>
>Reply-To: ids@iiug.org
>To: ids@iiug.org
>Subject: Re: Database synchronization [8487]
>Date: Fri, 23 Feb 2007 09:42:02 -0500 (EST)
>
>Sounds like ER is the tool you want. Why do you resist? Resistance is
>futile!
>
>Art S. Kagel
>----- Original Message -----
>From: Georges Martin <ids@iiug.org>
>At: 2/23 9:37:15
>
>Hi guys,
>we have a database running on Informix 9.4.
>We want to setup a new database running on Informix 10 and we want to
>replicate
>some of our tables from the first instance to the second one.
>Tables don't have the same structure accross these two databases.
>We don't want to use ER or HDR.
>Is there a way to synchronize tables between these two databases without
>using
>triggers and UDR?
>Is there a possibility to read information from logical logs to capture
>transactions (from the first instance) we want to replicate to the second
>instance (as ER does)?Except onlog, is there a utility for reading
>information
>from logical logs?
>Do you know some ETL that does the job? Currently the destination database
>is
>running on Informix 10 but in the future we might support other DBMS like
>Postgres,MySQL,Oracle ...
>
>Thanks
>
>
>*******************************************************************************
>Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
Your Space. Your Friends. Your Stories. Share your world with Windows Live
Spaces. http://spaces.live.com/?mkt=en-ca
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.