How are you all , it has been a long while since my last blog so thought of sharing some useful information on goldengate, let us try to implement soft deletes in the goldengate.
Concept:
In the source table record got deleted , in the replicated target update the record attribute delete_flag from
N to Y, if record got reinserted in the source, in the target modify
delete_flag from ‘Y’ to ‘N’. If new record got inserted then delete_flag will be N
and if source updates a record in the target also record should get updated but there is no
change in the delete_flag value i.e. it will be N only.
Implementation details…..for example let us have source target tables as follow
CREATE TABLE source.SOFT_DETELE_IMPLEMENTATION
(
ROW_ID VARCHAR2(15 CHAR) ,
LOGIN VARCHAR2(50 CHAR)
);
CREATE TABLE target.SOFT_DETELE_IMPLEMENTATION
(
ROW_ID VARCHAR2(15 CHAR) ,
LOGIN VARCHAR2(50 CHAR) ,
DELETE_FLG CHAR(1 CHAR)
);
In extract parameter file add the table which you are interested
in
example: edit /app/ggate/dirprm/eextract.prm
EXTRACT EEXTRACT
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID dbuser_name@DB_Instance, PASSWORD password
EXTTRAIL /app/gg/trail/
-- etc
TABLE source.SOFT_DETELE_IMPLEMENTATION, &
COLS (ROW_ID,
LOGIN);
--Modify the Replicat parameter file and add following
syntax for corresponding table then perform all necessary task need for extract / replication to work.
edit /app/gg/dirprm/rextract.prm
---------------------------------
-- TARGET Table mapping --
---------------------------------
-- following tag will allow multiple maps for single source
ALLOWDUPTARGETMAP
GETINSERTS
GETUPDATES
-- IGNOREDELETES will ignores deleted records
IGNOREDELETES
MAP source.SOFT_DETELE_IMPLEMENTATION, TARGET target.SOFT_DETELE_IMPLEMENTATION,
&
COLMAP ( ROW_ID =
ROW_ID, LOGIN = LOGIN, DELETE_FLG =
"N" ),HANDLECOLLISIONS;
IGNOREINSERTS
IGNOREUPDATES
GETDELETES
-- UPDATEDELETES will convert delete operations into update
operations.
UPDATEDELETES
MAP source.SOFT_DETELE_IMPLEMENTATION, TARGET target.SOFT_DETELE_IMPLEMENTATION,
&
COLMAP ( ROW_ID =
ROW_ID, LOGIN = LOGIN, DELETE_FLG =
"Y" ),HANDLECOLLISIONS;
Unit testing:
Inserted a record into source ( insert into SOFT_DETELE_IMPLEMENTATION
values ('400','aaa');)
Target Table Values ---- 400 aaa
N
Updated a record in the source (update
SOFT_DETELE_IMPLEMENTATION set login ='upd'
where row_id ='400';)
Target Table Values ---- 400 upd
N
deleted a record from source (delete
SOFT_DETELE_IMPLEMENTATION where row_id ='400';)
Target Table Values ---- 400 upd
Y
Inserted same record 2nd time in the source
Target Table Values ----400 aaa
N
Source record got updated
Target Table Values ----400 upd
N
Source record got deleted second time
Target Table Values ----400 upd
Y
