Re: How to replicate consistant Database to Sybase ?

From: Peder Holst <pederh_at_elektrosoft.se>
Date: 1997/09/16
Message-ID: <01bcc2dd$a1413f20$1a0a0a0a_at_pederh>#1/1


Frederic GIGNOUX <fgignoux_at_ota.fr.socgen.com> wrote in article <3416B7E4.4C23_at_ota.fr.socgen.com>...
> Because no standard product seem to fullfill my requirement because of
> my Harware (VMS) and my ORACLE version 7.1, i will have to build a
> tool that will do a replication (twice a day minimum) of my ORACLE
> database on SYBASE (that's life). It should respect Transaction, so the
> target copy MUST always be consistent.
>
> Does anyone has an experience of writting a tool to get
> a consitant delta from an ORACLE Database (10 linked Tables). ???
>
>
> After that i know how to apply this delta on Sybase.(Not a big deal).
>
> Regards
>
> Please to Post && Email any answer.
>
>
> Frederic GIGNOUX
> --
> -----------------------------------------------------------------------
> Frederic GIGNOUX /
> Tel:(33)01-42-13-46-68 Fax:(33)01-42-13-69-66 Pager:(33)06-51-01-21-87
> Email: mailto:fgignoux_at_ota.fr.socgen.com / mailto:fgignoux_at_worldnet.fr
> -----------------------------------------------------------------------
>

We make a tool that does one-way or two-way replication between heterogeneous ODBC compliant datasources called SQLMover. The tool can be installed on a NT Server or a WS (or Windows 95) and initiated manually or automatically. It uses standard, simple or complex SQL Select statement in one end and a INSERT, UPDATE, INSERT then UPDATE, UPDATE then INSERT or DELETE statement towards the destination database table with support for transaction and prepared statements.

Consistent delta replication Suggestion #1: Since you need to update the Sybase database twice a day I suggest that you use two shadow tables for each of the 10 Oracle tables (total 20 extra tables). The shadow tables are updated by using a trigger on the original ORACLE tables. The trigger decides which of the two tables to update depending on the system time. For example, if it is before noon you copy to table 1 and after to table 2. At 12:01 you schedule SQLMover to fetch data from all the table 1s to the corresponding shadow tables (10 extra tables) in the Sybase database which has a trigger on them which updates the "original" Sybase table. The SQLMover tasks (one for each table) starts with a delete * of the Sybase shadow table. When the 10 transfer tasks that updates the Sybase system completed you schedule another 10 tasks that Selects * from the Sybase shadow table and DELETEs the corresponding shadow "table 1s" in the Oracle system. After midnight you run another set of 10 fetches and 10 deletes to replicate and clear the "table 2s".

The only risk with suggestion #1 is that you have a long transaction running when the time passes midday/midnight and your trigger updating the ORACLE shadow table checks the time on "the wrong end" of the trigger.

Another approach that would provide almost consistent deltas would be to use either ten machines in parallell executing at the same time each fetching data.

In short: SQLMover provides the best possibility there is without being a log reader - and a much lower price !

To evaluate until Sept.30 you can download from www.sqlmover.com (follow the download buttons) and use the evaluation key CYUEJSMP. The key allows you to transfer max. 500 rows per transfer/replication task and max. 5 tasks can be defined.

For DW purposes you can choose to let SQLMover pre-aggregate your data by using SELECT - GROUP BY. That is if you donĀ“t want to replicate each row.

If you have large tables or amounts of data you may want to spend some time desingning the optimal replication tasks. There are several ways this can be achieved: By using shadow tables and triggers in combination or by using a dynamic SELECT ... where date > (system generated date) for example.

Data conversion can be performed by using the SQL convert option, by using the built in Format Mask functions or by using triggers in the destination database.

Peder Holst,
ElektroSoft AB
pederh_at_elektrosoft.se Received on Tue Sep 16 1997 - 00:00:00 CEST

Original text of this message