Re: Multimaster Replication: Transform Data

Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message news:<55p3l0dunq8rfukmbs5mjcqbo4lp5att6l_at_4ax.com>...
> On 22 Sep 2004 12:16:55 -0700, froliol_at_yahoo.com (Louis Frolio) wrote:
>
> >Greetings All, is there some mechanism via Oracle Streams or Advanced
> >Replication (yes I am in the process of reading the documentation) to
> >transform the data being pushed? As an example, in MSSQL 2000 you can
> >use DTS as part of the replication process to transform the data
> >during replication. At this point of my inquiry I need only know if
> >it is possible. As a scenario I need to do bi-directional replication
> >on an existing product/db that is not inherently configured for
> >replication. What I was thinking of doing was to take the primary key
> >(artificial key, sequence generated) of a table and while it is being
> >pushed add some number to it, say 20,000,000. This number will be a
> >function of the theoretical limit(based on business rules) of that
> >primary key for that row. If I can do this I can in essence
> >horizontally partition the data and avoid collisions. I know this
> >option is not clean and is probably rife with holes, however, as I
> >said at this point I am only in the investigation phase of the process
> >and want to understand my options.
> >
> >Louis Frolio
>
>
> Replication is replication is replication: no transformation possible.
> Seems you need to resolve this by setting the 2 sequence generators
> 20000000 apart, and you won't have collisions and you won't have
> holes.

Sybrand, the feedback is much appreciated. From my research I found that it may be possible to transform the data during replication. With Oracle Streams and Advanced Replication you can effectively change the data, augment a table, and what seems many other possibilities. Using Rule Based Transformations you have the option to:

From Oracle:

Renaming or removing a column
Splitting a column into several columns Combining several columns into one column Modifying the contents of a column

The downside to this is that it seems to be all custom code via a few proprietary packages/procedures intended for this type of transformation. The process includes writing sp's which make calls to the proprietary packages and unless the columns you want to change are all identical on each table you will have to write a procedure for each table. This is what I managed to cull from the docs with a quick read.

Regards, Louis Frolio. Received on Wed Sep 22 2004 - 21:50:48 CDT