Today at one of our GoldenGate customers we were faced with the following requirement: For one of the tables on the target we needed two additional columns which shall hold the insert and update timestamps for when the row was either inserted or updated. Just to be clear: It was not about getting the timestamps for the inserts or updates as they happened on the source but the target. In this post we’ll look at how GoldenGate can be configured to do the work requested.
All the below examples work with the well known scott/tiger schema. There is one extract running for capturing on the source and one replicat is re-playing the changes on the target (over sqlnet, no integrated mode here). This is the current status of the streams:
GGSCI (oelogg1) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTRSCO 00:00:00 00:00:02
REPLICAT RUNNING REPLSCO 00:00:00 00:00:01
GGSCI (oelogg1) 2> view params EXTRSCO
EXTRACT extrsco
USERIDALIAS DB1 DOMAIN admin
GETUPDATEBEFORES
REPORT AT 23:40
DDL INCLUDE OPTYPE TRUNCATE OBJNAME ARBOR.*, &
INCLUDE OPTYPE ALTER OBJNAME ARBOR.*
EXTTRAIL /u01/app/ogg/product/12.1.2.1.9/dirdat/es
TABLE SCOTT.*;
GGSCI (oelogg1) 3> view params REPLSCO
REPLICAT REPLSCO
USERIDALIAS DB2 DOMAIN admin
GETUPDATEBEFORES
GETTRUNCATES
APPLYNOOPUPDATES
DDL INCLUDE OPTYPE TRUNCATE OBJNAME SCOTT.*, &
INCLUDE OPTYPE ALTER OBJNAME SCOTT.* &
EXCLUDE ALL INSTR 'CONSTRAINT' &
EXCLUDE ALL INSTR 'TRIGGER'
ASSUMETARGETDEFS
DBOPTIONS DEFERREFCONST
MAP SCOTT.*, TARGET SCOTT.*;
Pretty basic, no unusual stuff here. The table we’ll use for the scope of this post is the “project” table which has the following contents in a fresh scott/tiger installation:
SQL> col DESCRIPTION for a50
SQL> r
1* select * from project
PROJECTNO DESCRIPTION START_DATE END_DATE
---------- -------------------------------------------------- ------------ ------------
1001 Development of Novel Magnetic Suspension System 2006-01-01 2007-08-13
1002 Research on thermofluid dynamics in Microdroplets 2006-08-22 2007-03-20
1003 Foundation of Quantum Technology 2007-02-24 2008-07-31
1004 High capacity optical network 2008-01-01
Of course the table looks the same on the target:
SQL> col DESCRIPTION for a50
SQL> r
1* select * from project
PROJECTNO DESCRIPTION START_DATE END_DATE
---------- -------------------------------------------------- ------------ ------------
1001 Development of Novel Magnetic Suspension System 2006-01-01 2007-08-13
1002 Research on thermofluid dynamics in Microdroplets 2006-08-22 2007-03-20
1003 Foundation of Quantum Technology 2007-02-24 2008-07-31
1004 High capacity optical network 2008-01-01
To prove that the streams are really working lets add an additional row to the source:
SQL> insert into project values (1005, 'my fun project 1', sysdate, sysdate + 30 ); 1 row created. SQL> commit; Commit complete. SQL>
… and then check if the row indeed was replicated to the target:
SQL> r
1* select * from project
PROJECTNO DESCRIPTION START_DATE END_DATE
---------- -------------------------------------------------- ------------ ------------
1005 my fun project 1 29-AUG-16 28-SEP-16
1001 Development of Novel Magnetic Suspension System 2006-01-01 2007-08-13
1002 Research on thermofluid dynamics in Microdroplets 2006-08-22 2007-03-20
1003 Foundation of Quantum Technology 2007-02-24 2008-07-31
1004 High capacity optical network 2008-01-01
Ok, looks fine, so back to the initial requirement. We need two additional columns on the target:
alter table SCOTT.PROJECT add create_dt timestamp with time zone;
alter table SCOTT.PROJECT add update_dt timestamp with time zone;
alter table SCOTT.PROJECT modify create_dt default to_date('01.01.2000','DD.MM.YYYY');
alter table SCOTT.PROJECT modify update_dt default to_date('01.01.2000','DD.MM.YYYY');
In our case we needed to set a default value as both column are not allowed to contain NULL values. For the moment the content of the table on the target is:
SQL> col CREATE_DT for a20
SQL> col UPDATE_DT for a20
SQL> select * from project;
PROJECTNO DESCRIPTION START_DATE END_DATE CREATE_DT UPDATE_DT
---------- -------------------------------------------------- ------------ ------------ -------------------- --------------------
1005 my fun project 1 29-AUG-16 31-AUG-16
1001 Development of Novel Magnetic Suspension System 2006-01-01 2007-08-13
1002 Research on thermofluid dynamics in Microdroplets 2006-08-22 2007-03-20
1003 Foundation of Quantum Technology 2007-02-24 2008-07-31
1004 High capacity optical network 2008-01-01
In real life when the table which will be extended holds millions of rows the following will probably be too simple and you’ll need to spend some time on thinking on how you organize the updates. For the scope of this post this is fine:
update SCOTT.PROJECT set create_dt = to_date('01.01.2000','DD.MM.YYYY') where create_dt is null;
update SCOTT.PROJECT set update_dt = to_date('01.01.2000','DD.MM.YYYY') where update_dt is null;
commit;
alter table SCOTT.PROJECT modify create_dt not null;
alter table SCOTT.PROJECT modify update_dt not null;
From now on we have identical insert and update timestamps for all of the rows on the target:
SQL> r
1* select * from project
PROJECTNO DESCRIPTION START_DATE END_DATE CREATE_DT UPDATE_DT
---------- -------------------------------------------------- ------------ ------------ ----------------------------------- -----------------------------------
1005 my fun project 1 29-AUG-16 31-AUG-16 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1001 Development of Novel Magnetic Suspension System 2006-01-01 2007-08-13 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1002 Research on thermofluid dynamics in Microdroplets 2006-08-22 2007-03-20 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1003 Foundation of Quantum Technology 2007-02-24 2008-07-31 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1004 High capacity optical network 2008-01-01 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
A final check on the source for being sure that the default values work:
SQL> insert into project values (1006, 'my fun project 2', sysdate, sysdate + 30 ); 1 row created. SQL> commit; Commit complete.
On the target:
SQL> r
1* select * from project
PROJECTNO DESCRIPTION START_DATE END_DATE CREATE_DT UPDATE_DT
---------- -------------------------------------------------- ------------ ------------ ----------------------------------- -----------------------------------
1005 my fun project 1 29-AUG-16 31-AUG-16 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1006 my fun project 2 29-AUG-16 28-SEP-16 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1001 Development of Novel Magnetic Suspension System 2006-01-01 2007-08-13 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1002 Research on thermofluid dynamics in Microdroplets 2006-08-22 2007-03-20 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1003 Foundation of Quantum Technology 2007-02-24 2008-07-31 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1004 High capacity optical network 2008-01-01 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
All fine. Time to do the GoldenGate work. Obviously the first step is to stop the replicat:
GGSCI (oelogg1) 2> stop REPLSCO Sending STOP request to REPLICAT REPLSCO ... Request processed. GGSCI (oelogg1) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXTRSCO 00:00:00 00:00:09 REPLICAT STOPPED REPLSCO 00:00:00 00:00:01
Lets do the insert case in a first step by adding the following line (COLMAP) to the replicat configuration:
REPLICAT REPLSCO
USERIDALIAS DB2 DOMAIN admin
GETUPDATEBEFORES
GETTRUNCATES
APPLYNOOPUPDATES
DISCARDFILE /u01/app/ogg/product/12.1.2.1.9/dirdsc/dscsco.dsc, APPEND, MEGABYTES 10
DDL INCLUDE OPTYPE TRUNCATE OBJNAME SCOTT.*, &
INCLUDE OPTYPE ALTER OBJNAME SCOTT.* &
EXCLUDE ALL INSTR 'CONSTRAINT' &
EXCLUDE ALL INSTR 'TRIGGER'
ASSUMETARGETDEFS
DBOPTIONS DEFERREFCONST
MAP SCOTT.PROJECT, TARGET SCOTT.PROJECT, COLMAP (usedefaults,
create_dt = @IF (@STREQ (@GETENV ('GGHEADER', 'OPTYPE'), 'INSERT'), @DATENOW(), @COLSTAT (MISSING)));
MAP SCOTT.*, TARGET SCOTT.*;
Start again:
GGSCI (oelogg1) 6> start REPLSCO Sending START request to MANAGER ... REPLICAT REPLSCO starting GGSCI (oelogg1) 7> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXTRSCO 00:00:00 00:00:01 REPLICAT RUNNING REPLSCO 00:00:00 00:00:00
Looks good from a configuration perspective. Time to start:
SQL> insert into project values (1007, 'my fun project 3', sysdate, sysdate + 30 ); 1 row created. SQL> commit; Commit complete.
On the target we should now see the exact insert date of the record instead of the default value of the column:
SQL> r
1* select * from project
PROJECTNO DESCRIPTION START_DATE END_DATE CREATE_DT UPDATE_DT
---------- -------------------------------------------------- ------------ ------------ ----------------------------------- -----------------------------------
1005 my fun project 1 29-AUG-16 31-AUG-16 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1006 my fun project 2 29-AUG-16 28-SEP-16 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1001 Development of Novel Magnetic Suspension System 2006-01-01 2007-08-13 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1002 Research on thermofluid dynamics in Microdroplets 2006-08-22 2007-03-20 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1003 Foundation of Quantum Technology 2007-02-24 2008-07-31 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1004 High capacity optical network 2008-01-01 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1007 my fun project 3 29-AUG-16 28-SEP-16 29-AUG-16 12.52.56.000000 PM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
Perfect, this works. Lets go on with the update case: All we need is to add the additional column for the update case and populate it:
REPLICAT REPLSCO
USERIDALIAS DB2 DOMAIN admin
GETUPDATEBEFORES
GETTRUNCATES
APPLYNOOPUPDATES
DISCARDFILE /u01/app/ogg/product/12.1.2.1.9/dirdsc/dscsco.dsc, APPEND, MEGABYTES 10
DDL INCLUDE OPTYPE TRUNCATE OBJNAME SCOTT.*, &
INCLUDE OPTYPE ALTER OBJNAME SCOTT.* &
EXCLUDE ALL INSTR 'CONSTRAINT' &
EXCLUDE ALL INSTR 'TRIGGER'
ASSUMETARGETDEFS
DBOPTIONS DEFERREFCONST
MAP SCOTT.PROJECT, TARGET SCOTT.PROJECT, COLMAP (usedefaults,
create_dt = @IF (@STREQ (@GETENV ('GGHEADER', 'OPTYPE'), 'INSERT'), @DATENOW(), @COLSTAT (MISSING)),
update_dt = @IF (@VALONEOF (@GETENV ('GGHEADER', 'OPTYPE'), 'UPDATE', 'SQL COMPUPDATE', 'PK UPDATE' ), @DATENOW(), @COLSTAT
(MISSING)));
MAP SCOTT.*, TARGET SCOTT.*;
Then stop and start the replicat to bring the changes into effect. Lets create a new row just to see that this does not have any effect on the insert case:
SQL> insert into project values (1008, 'my fun project 4', sysdate, sysdate + 30 ); 1 row created. SQL> commit; Commit complete.
What we should see on the target are two rows with an exact insert date but a default update date:
SQL> r
1* select * from project
PROJECTNO DESCRIPTION START_DATE END_DATE CREATE_DT UPDATE_DT
---------- -------------------------------------------------- ------------ ------------ ----------------------------------- -----------------------------------
1005 my fun project 1 29-AUG-16 31-AUG-16 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1006 my fun project 2 29-AUG-16 28-SEP-16 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1001 Development of Novel Magnetic Suspension System 2006-01-01 2007-08-13 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1002 Research on thermofluid dynamics in Microdroplets 2006-08-22 2007-03-20 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1003 Foundation of Quantum Technology 2007-02-24 2008-07-31 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1004 High capacity optical network 2008-01-01 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1008 my fun project 4 29-AUG-16 28-SEP-16 29-AUG-16 12.58.40.000000 PM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1007 my fun project 3 29-AUG-16 28-SEP-16 29-AUG-16 12.52.56.000000 PM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
Perfect. What about the update itself?
SQL> update project set DESCRIPTION = upper(description) where PROJECTNO = 1008; 1 row updated. SQL> commit; Commit complete.
This should result in one exact update date for my fun project 4:
SQL> r
1* select * from project
PROJECTNO DESCRIPTION START_DATE END_DATE CREATE_DT UPDATE_DT
---------- -------------------------------------------------- ------------ ------------ ----------------------------------- -----------------------------------
1005 my fun project 1 29-AUG-16 31-AUG-16 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1006 my fun project 2 29-AUG-16 28-SEP-16 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1001 Development of Novel Magnetic Suspension System 2006-01-01 2007-08-13 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1002 Research on thermofluid dynamics in Microdroplets 2006-08-22 2007-03-20 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1003 Foundation of Quantum Technology 2007-02-24 2008-07-31 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1004 High capacity optical network 2008-01-01 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1008 MY FUN PROJECT 4 29-AUG-16 28-SEP-16 29-AUG-16 12.58.40.000000 PM +02:00 29-AUG-16 01.04.49.000000 PM +02:00
1007 my fun project 3 29-AUG-16 28-SEP-16 29-AUG-16 12.52.56.000000 PM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
8 rows selected.
Perfect, works fine, too. Just to be sure that deletes still work lets do a final test:
SQL> delete from project where PROJECTNO = 1008; 1 row deleted. SQL> commit; Commit complete. SQL>
The two additional columns should not prevent Goldengate from being able to delete the rows, lets see:
SQL> r
1* select * from project
PROJECTNO DESCRIPTION START_DATE END_DATE CREATE_DT UPDATE_DT
---------- -------------------------------------------------- ------------ ------------ ----------------------------------- -----------------------------------
1005 my fun project 1 29-AUG-16 31-AUG-16 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1006 my fun project 2 29-AUG-16 28-SEP-16 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1001 Development of Novel Magnetic Suspension System 2006-01-01 2007-08-13 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1002 Research on thermofluid dynamics in Microdroplets 2006-08-22 2007-03-20 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1003 Foundation of Quantum Technology 2007-02-24 2008-07-31 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1004 High capacity optical network 2008-01-01 01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
1007 my fun project 3 29-AUG-16 28-SEP-16 29-AUG-16 12.52.56.000000 PM +02:00 01-JAN-00 12.00.00.000000 AM +02:00
The fun project 4 is gone and all works as expected. Hope this helps ….
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/OLS_web-min-scaled.jpg)