{"id":8764,"date":"2016-08-29T16:18:35","date_gmt":"2016-08-29T14:18:35","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/"},"modified":"2016-08-29T16:18:35","modified_gmt":"2016-08-29T14:18:35","slug":"letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/","title":{"rendered":"Letting GoldenGate automatically maintain the insert and update timestamps on the target"},"content":{"rendered":"

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.<\/p>\n

<\/p>\n

All the below examples work with the well known scott\/tiger schema<\/a>. 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:<\/p>\n

\nGGSCI (oelogg1) 1> info all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING                                           \nEXTRACT     RUNNING     EXTRSCO     00:00:00      00:00:02    \nREPLICAT    RUNNING     REPLSCO     00:00:00      00:00:01    \n\n\nGGSCI (oelogg1) 2> view params EXTRSCO\n\nEXTRACT extrsco\nUSERIDALIAS DB1 DOMAIN admin\nGETUPDATEBEFORES\nREPORT AT 23:40\nDDL INCLUDE OPTYPE TRUNCATE OBJNAME ARBOR.*, &\n    INCLUDE OPTYPE ALTER OBJNAME ARBOR.*\nEXTTRAIL \/u01\/app\/ogg\/product\/12.1.2.1.9\/dirdat\/es\nTABLE SCOTT.*;\n\nGGSCI (oelogg1) 3> view params REPLSCO\n\nREPLICAT REPLSCO\nUSERIDALIAS DB2 DOMAIN admin\nGETUPDATEBEFORES\nGETTRUNCATES\nAPPLYNOOPUPDATES\nDDL INCLUDE OPTYPE TRUNCATE OBJNAME SCOTT.*, &\n    INCLUDE OPTYPE ALTER OBJNAME SCOTT.* &\n    EXCLUDE ALL INSTR 'CONSTRAINT' &\n    EXCLUDE ALL INSTR 'TRIGGER' \nASSUMETARGETDEFS\nDBOPTIONS DEFERREFCONST\nMAP SCOTT.*, TARGET SCOTT.*;\n<\/pre>\n
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:<\/p>\n
\nSQL> col DESCRIPTION for a50\nSQL> r\n  1* select * from project\n\n PROJECTNO DESCRIPTION\t\t\t\t\t      START_DATE   END_DATE\n---------- -------------------------------------------------- ------------ ------------\n      1001 Development of Novel Magnetic Suspension System    2006-01-01   2007-08-13\n      1002 Research on thermofluid dynamics in Microdroplets  2006-08-22   2007-03-20\n      1003 Foundation of Quantum Technology\t\t      2007-02-24   2008-07-31\n      1004 High capacity optical network\t\t      2008-01-01\n<\/pre>\n
Of course the table looks the same on the target:<\/p>\n
\nSQL> col DESCRIPTION for a50\nSQL> r\n  1* select * from project\n\n PROJECTNO DESCRIPTION\t\t\t\t\t      START_DATE   END_DATE\n---------- -------------------------------------------------- ------------ ------------\n      1001 Development of Novel Magnetic Suspension System    2006-01-01   2007-08-13\n      1002 Research on thermofluid dynamics in Microdroplets  2006-08-22   2007-03-20\n      1003 Foundation of Quantum Technology\t\t      2007-02-24   2008-07-31\n      1004 High capacity optical network\t\t      2008-01-01\n<\/pre>\n
To prove that the streams are really working lets add an additional row to the source:<\/p>\n
\nSQL> insert into project values (1005, 'my fun project 1', sysdate, sysdate + 30 );\n\n1 row created.\n\nSQL> commit;\n\nCommit complete.\n\nSQL> \n<\/pre>\n
… and then check if the row indeed was replicated to the target: <\/p>\n
\nSQL> r\n  1* select * from project\n\n PROJECTNO DESCRIPTION\t\t\t\t\t      START_DATE   END_DATE\n---------- -------------------------------------------------- ------------ ------------\n      1005 my fun project 1\t\t\t\t      29-AUG-16    28-SEP-16\n      1001 Development of Novel Magnetic Suspension System    2006-01-01   2007-08-13\n      1002 Research on thermofluid dynamics in Microdroplets  2006-08-22   2007-03-20\n      1003 Foundation of Quantum Technology\t\t      2007-02-24   2008-07-31\n      1004 High capacity optical network\t\t      2008-01-01\n<\/pre>\n
Ok, looks fine, so back to the initial requirement. We need two additional columns on the target:<\/p>\n
\nalter table SCOTT.PROJECT add create_dt timestamp with time zone;\nalter table SCOTT.PROJECT add update_dt timestamp with time zone;\nalter table SCOTT.PROJECT modify create_dt default to_date('01.01.2000','DD.MM.YYYY');\nalter table SCOTT.PROJECT modify update_dt default to_date('01.01.2000','DD.MM.YYYY');\n<\/pre>\n
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:<\/p>\n
\nSQL> col CREATE_DT for a20\nSQL> col UPDATE_DT for a20\nSQL> select * from project;\n\n PROJECTNO DESCRIPTION\t\t\t\t\t      START_DATE   END_DATE\tCREATE_DT\t     UPDATE_DT\n---------- -------------------------------------------------- ------------ ------------ -------------------- --------------------\n      1005 my fun project 1\t\t\t\t      29-AUG-16    31-AUG-16\n      1001 Development of Novel Magnetic Suspension System    2006-01-01   2007-08-13\n      1002 Research on thermofluid dynamics in Microdroplets  2006-08-22   2007-03-20\n      1003 Foundation of Quantum Technology\t\t      2007-02-24   2008-07-31\n      1004 High capacity optical network\t\t      2008-01-01\n<\/pre>\n
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:  <\/p>\n
\nupdate SCOTT.PROJECT set create_dt = to_date('01.01.2000','DD.MM.YYYY') where create_dt is null;\nupdate SCOTT.PROJECT set update_dt = to_date('01.01.2000','DD.MM.YYYY') where update_dt is null;\ncommit;\nalter table SCOTT.PROJECT modify create_dt not null;\nalter table SCOTT.PROJECT modify update_dt not null;\n<\/pre>\n
From now on we have identical insert and update timestamps for all of the rows on the target:<\/p>\n
\nSQL> r\n  1* select * from project\n\n PROJECTNO DESCRIPTION\t\t\t\t\t      START_DATE   END_DATE\tCREATE_DT\t\t\t    UPDATE_DT\n---------- -------------------------------------------------- ------------ ------------ ----------------------------------- -----------------------------------\n      1005 my fun project 1\t\t\t\t      29-AUG-16    31-AUG-16\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1001 Development of Novel Magnetic Suspension System    2006-01-01   2007-08-13\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1002 Research on thermofluid dynamics in Microdroplets  2006-08-22   2007-03-20\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1003 Foundation of Quantum Technology\t\t      2007-02-24   2008-07-31\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1004 High capacity optical network\t\t      2008-01-01                01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n<\/pre>\n
A final check on the source for being sure that the default values work:<\/p>\n
\nSQL> insert into project values (1006, 'my fun project 2', sysdate, sysdate + 30 );\n\n1 row created.\n\nSQL> commit;\n\nCommit complete.\n<\/pre>\n
On the target:<\/p>\n
\nSQL> r\n  1* select * from project\n\n PROJECTNO DESCRIPTION\t\t\t\t\t      START_DATE   END_DATE\tCREATE_DT\t\t\t    UPDATE_DT\n---------- -------------------------------------------------- ------------ ------------ ----------------------------------- -----------------------------------\n      1005 my fun project 1\t\t\t\t      29-AUG-16    31-AUG-16\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1006 my fun project 2\t\t\t\t      29-AUG-16    28-SEP-16\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1001 Development of Novel Magnetic Suspension System    2006-01-01   2007-08-13\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1002 Research on thermofluid dynamics in Microdroplets  2006-08-22   2007-03-20\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1003 Foundation of Quantum Technology\t\t      2007-02-24   2008-07-31\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1004 High capacity optical network\t\t      2008-01-01\t\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n<\/pre>\n
All fine. Time to do the GoldenGate work. Obviously the first step is to stop the replicat:<\/p>\n
\nGGSCI (oelogg1) 2> stop REPLSCO\n\nSending STOP request to REPLICAT REPLSCO ...\nRequest processed.\n\nGGSCI (oelogg1) 3> info all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING                                           \nEXTRACT     RUNNING     EXTRSCO     00:00:00      00:00:09    \nREPLICAT    STOPPED     REPLSCO     00:00:00      00:00:01    \n<\/pre>\n
Lets do the insert case in a first step by adding the following line (COLMAP) to the replicat configuration:<\/p>\n
\nREPLICAT REPLSCO\nUSERIDALIAS DB2 DOMAIN admin\nGETUPDATEBEFORES\nGETTRUNCATES\nAPPLYNOOPUPDATES\nDISCARDFILE \/u01\/app\/ogg\/product\/12.1.2.1.9\/dirdsc\/dscsco.dsc, APPEND, MEGABYTES 10\nDDL INCLUDE OPTYPE TRUNCATE OBJNAME SCOTT.*, &\n    INCLUDE OPTYPE ALTER OBJNAME SCOTT.* &\n    EXCLUDE ALL INSTR 'CONSTRAINT' &\n    EXCLUDE ALL INSTR 'TRIGGER' \nASSUMETARGETDEFS\nDBOPTIONS DEFERREFCONST\nMAP SCOTT.PROJECT, TARGET SCOTT.PROJECT, COLMAP (usedefaults,\n    create_dt = @IF (@STREQ (@GETENV ('GGHEADER', 'OPTYPE'), 'INSERT'), @DATENOW(), @COLSTAT (MISSING)));\nMAP SCOTT.*, TARGET SCOTT.*;\n<\/pre>\n
Start again:<\/p>\n
\nGGSCI (oelogg1) 6> start REPLSCO\n\nSending START request to MANAGER ...\nREPLICAT REPLSCO starting\n\n\nGGSCI (oelogg1) 7> info all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING                                           \nEXTRACT     RUNNING     EXTRSCO     00:00:00      00:00:01    \nREPLICAT    RUNNING     REPLSCO     00:00:00      00:00:00    \n<\/pre>\n
Looks good from a configuration perspective. Time to start:<\/p>\n
\nSQL> insert into project values (1007, 'my fun project 3', sysdate, sysdate + 30 );\n\n1 row created.\n\nSQL> commit;\n\nCommit complete.\n<\/pre>\n
On the target we should now see the exact insert date of the record instead of the default value of the column:<\/p>\n
\nSQL> r\n  1* select * from project\n\n PROJECTNO DESCRIPTION\t\t\t\t\t      START_DATE   END_DATE\tCREATE_DT\t\t\t    UPDATE_DT\n---------- -------------------------------------------------- ------------ ------------ ----------------------------------- -----------------------------------\n      1005 my fun project 1\t\t\t\t      29-AUG-16    31-AUG-16\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1006 my fun project 2\t\t\t\t      29-AUG-16    28-SEP-16\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1001 Development of Novel Magnetic Suspension System    2006-01-01   2007-08-13\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1002 Research on thermofluid dynamics in Microdroplets  2006-08-22   2007-03-20\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1003 Foundation of Quantum Technology\t\t      2007-02-24   2008-07-31\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1004 High capacity optical network\t\t      2008-01-01\t\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1007 my fun project 3\t\t\t\t      29-AUG-16    28-SEP-16\t29-AUG-16 12.52.56.000000 PM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n<\/pre>\n
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:<\/p>\n
\nREPLICAT REPLSCO\nUSERIDALIAS DB2 DOMAIN admin\nGETUPDATEBEFORES\nGETTRUNCATES\nAPPLYNOOPUPDATES\nDISCARDFILE \/u01\/app\/ogg\/product\/12.1.2.1.9\/dirdsc\/dscsco.dsc, APPEND, MEGABYTES 10\nDDL INCLUDE OPTYPE TRUNCATE OBJNAME SCOTT.*, &\n    INCLUDE OPTYPE ALTER OBJNAME SCOTT.* &\n    EXCLUDE ALL INSTR 'CONSTRAINT' &\n    EXCLUDE ALL INSTR 'TRIGGER' \nASSUMETARGETDEFS\nDBOPTIONS DEFERREFCONST\nMAP SCOTT.PROJECT, TARGET SCOTT.PROJECT, COLMAP (usedefaults,\n    create_dt = @IF (@STREQ (@GETENV ('GGHEADER', 'OPTYPE'), 'INSERT'), @DATENOW(), @COLSTAT (MISSING)),\n    update_dt = @IF (@VALONEOF (@GETENV ('GGHEADER', 'OPTYPE'), 'UPDATE', 'SQL COMPUPDATE', 'PK UPDATE' ), @DATENOW(), @COLSTAT \n(MISSING)));\nMAP SCOTT.*, TARGET SCOTT.*;\n<\/pre>\n
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:<\/p>\n
\nSQL> insert into project values (1008, 'my fun project 4', sysdate, sysdate + 30 );\n\n1 row created.\n\nSQL> commit;\n\nCommit complete.\n<\/pre>\n
What we should see on the target are two rows with an exact insert date but a default update date:<\/p>\n
\nSQL> r\n  1* select * from project\n\n PROJECTNO DESCRIPTION\t\t\t\t\t      START_DATE   END_DATE\tCREATE_DT\t\t\t    UPDATE_DT\n---------- -------------------------------------------------- ------------ ------------ ----------------------------------- -----------------------------------\n      1005 my fun project 1\t\t\t\t      29-AUG-16    31-AUG-16\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1006 my fun project 2\t\t\t\t      29-AUG-16    28-SEP-16\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1001 Development of Novel Magnetic Suspension System    2006-01-01   2007-08-13\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1002 Research on thermofluid dynamics in Microdroplets  2006-08-22   2007-03-20\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1003 Foundation of Quantum Technology\t\t      2007-02-24   2008-07-31\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1004 High capacity optical network\t\t      2008-01-01\t\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1008 my fun project 4\t\t\t\t      29-AUG-16    28-SEP-16\t29-AUG-16 12.58.40.000000 PM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1007 my fun project 3\t\t\t\t      29-AUG-16    28-SEP-16\t29-AUG-16 12.52.56.000000 PM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n<\/pre>\n
Perfect. What about the update itself?<\/p>\n
\nSQL> update project set DESCRIPTION = upper(description) where PROJECTNO = 1008;\n\n1 row updated.\n\nSQL> commit;\n\nCommit complete.\n<\/pre>\n
This should result in one exact update date for my fun project 4:<\/p>\n
\nSQL> r\n  1* select * from project\n\n PROJECTNO DESCRIPTION\t\t\t\t\t      START_DATE   END_DATE\tCREATE_DT\t\t\t    UPDATE_DT\n---------- -------------------------------------------------- ------------ ------------ ----------------------------------- -----------------------------------\n      1005 my fun project 1\t\t\t\t      29-AUG-16    31-AUG-16\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1006 my fun project 2\t\t\t\t      29-AUG-16    28-SEP-16\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1001 Development of Novel Magnetic Suspension System    2006-01-01   2007-08-13\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1002 Research on thermofluid dynamics in Microdroplets  2006-08-22   2007-03-20\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1003 Foundation of Quantum Technology\t\t      2007-02-24   2008-07-31\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1004 High capacity optical network\t\t      2008-01-01\t\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1008 MY FUN PROJECT 4\t\t\t\t      29-AUG-16    28-SEP-16\t29-AUG-16 12.58.40.000000 PM +02:00 29-AUG-16 01.04.49.000000 PM +02:00\n      1007 my fun project 3\t\t\t\t      29-AUG-16    28-SEP-16\t29-AUG-16 12.52.56.000000 PM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n\n8 rows selected.\n<\/pre>\n
Perfect, works fine, too. Just to be sure that deletes still work lets do a final test:<\/p>\n
\nSQL> delete from project where PROJECTNO = 1008;\n\n1 row deleted.\n\nSQL> commit;\n\nCommit complete.\n\nSQL> \n<\/pre>\n
The two additional columns should not prevent Goldengate from being able to delete the rows, lets see:<\/p>\n
\nSQL> r\n  1* select * from project\n\n PROJECTNO DESCRIPTION\t\t\t\t\t      START_DATE   END_DATE\tCREATE_DT\t\t\t    UPDATE_DT\n---------- -------------------------------------------------- ------------ ------------ ----------------------------------- -----------------------------------\n      1005 my fun project 1\t\t\t\t      29-AUG-16    31-AUG-16\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1006 my fun project 2\t\t\t\t      29-AUG-16    28-SEP-16\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1001 Development of Novel Magnetic Suspension System    2006-01-01   2007-08-13\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1002 Research on thermofluid dynamics in Microdroplets  2006-08-22   2007-03-20\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1003 Foundation of Quantum Technology\t\t      2007-02-24   2008-07-31\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1004 High capacity optical network\t\t      2008-01-01\t\t01-JAN-00 12.00.00.000000 AM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n      1007 my fun project 3\t\t\t\t      29-AUG-16    28-SEP-16\t29-AUG-16 12.52.56.000000 PM +02:00 01-JAN-00 12.00.00.000000 AM +02:00\n<\/pre>\n
The fun project 4 is gone and all works as expected. Hope this helps ….<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197,229],"tags":[328],"type_dbi":[],"class_list":["post-8764","post","type-post","status-publish","format-standard","hentry","category-application-integration-middleware","category-database-administration-monitoring","tag-goldengate"],"acf":[],"yoast_head":"\nLetting GoldenGate automatically maintain the insert and update timestamps on the target - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Letting GoldenGate automatically maintain the insert and update timestamps on the target\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-08-29T14:18:35+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Letting GoldenGate automatically maintain the insert and update timestamps on the target\",\"datePublished\":\"2016-08-29T14:18:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/\"},\"wordCount\":555,\"commentCount\":0,\"keywords\":[\"GoldenGate\"],\"articleSection\":[\"Application integration & Middleware\",\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/\",\"name\":\"Letting GoldenGate automatically maintain the insert and update timestamps on the target - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-08-29T14:18:35+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Letting GoldenGate automatically maintain the insert and update timestamps on the target\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Letting GoldenGate automatically maintain the insert and update timestamps on the target - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/","og_locale":"en_US","og_type":"article","og_title":"Letting GoldenGate automatically maintain the insert and update timestamps on the target","og_description":"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 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/","og_site_name":"dbi Blog","article_published_time":"2016-08-29T14:18:35+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Letting GoldenGate automatically maintain the insert and update timestamps on the target","datePublished":"2016-08-29T14:18:35+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/"},"wordCount":555,"commentCount":0,"keywords":["GoldenGate"],"articleSection":["Application integration & Middleware","Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/","url":"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/","name":"Letting GoldenGate automatically maintain the insert and update timestamps on the target - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-08-29T14:18:35+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/letting-goldengate-automatically-maintain-the-insert-and-update-timestamps-on-the-target\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Letting GoldenGate automatically maintain the insert and update timestamps on the target"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8764","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=8764"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8764\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=8764"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=8764"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=8764"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=8764"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}