This discussion is archived
0 Replies Latest reply: Oct 21, 2013 4:30 AM by AntonyLosev RSS

Apply cumulative row value captured within transaction (for DWH incremental update)?

AntonyLosev Newbie
Currently Being Moderated

Dear community,

 

Don't know how to properly name it (applying cumulative row value, or merged row value, or something else) but consider the following scenario for Oracle to Oracle replication.

 

I have a table on the source system

create table tab( 
  id number( 14, 0 ) not null
  , f1 varchar2( 30 char )
  , f2 varchar2( 30 char )
  , constraint pk_tab primary key ( id ) 
);
/

 

Having following DMLs within the first transaction

 

insert into tab( id, f1, f2 ) values ( 1, 'Hello', 'Dolly' );
/
update table tab
set f1 = 'Bye'
where id = 1;
/
update table tab
set f2 = 'Mary'
where id = 1;
/

and following ones within the second

 

update table tab
set f2 = 'Mary'
where id = 1;
/
delete tab
where id = 1;
/

 

can I make GoldenGate to apply the following to the target table

insert into tab( id, f1, f2 ) values ( 1, 'Bye', 'Mary' ); -- for the first transaction (source mode replication )
/

 

delete tab where id = 1; -- for the second one (source mode replication)
/

or just to do nothing in normal mode (capturing changes across the 1rst and the 2nd transaction and merging insert with delete into NOOP)?

Our team considers using GoldenGate to apply incremental data changes to DWH and definitely there is no need for applying intermediate values.

As far as I understand using BATCHSQL wont help in that case, cause it will only organize 2 updates withing 1rst transaction into one batch operation. Am I right?

 

Regards, Anton.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points