How To: Define a target column with a timestamp that is populated with a date & time when the row is replicated
Scenario:
There are times you want to record the time that a row was copied to the target and have its value included in the table as a new column. This new column will store the date/time a record was inserted into the target for possibly for another ETL process to pick up or some other requirement.
Overview:
I believe the substitution you’re looking for is {hvr_integ_tstamp} that you want to add to the target table.
The details on this variable that one could use is found in the HVR documentation on the page /ColumnProperties for /IntegrateExpression
“{hvr_integ_tstamp [ spec ] } is replaced with the moment (time) that the change was integrated into target location. If the target column has a character based data type or if /Datatype = <character data type> then the default format is %Y:%m:%d %H:%M:%S[.SSS], but this can be overridden using the timestamp substitution format specifier spec . For more information, see Timestamp Substitution Format Specifier .”
Steps:
Step 1] First setup replication for this use case by defining 2 locations in the configuration section. For this example I created 2 locations 'sora' and 'tora'. Then created a channel 'ch' and added 2 location groups for each one of the locations 'OSRC' that from my location 'sora' and 'OVERIFY' for my target location 'tora'
Step 2] Then I launch my Table Explorer and select the table(s) in my replication set. For this example I will only select the table 'myusers'
My structure of my source, 'myusers' table can be show with the Table Explorer
Step 3] For replication we must have two actions defined – Capture and Integrate. In addition we need to add an additional action called /ColumnProperties to define the Extra field we want to replicate {hvr_integ_tstamp}.
Step 4] And add the following /ColumnProperties Action on the Target group, OVERIFY
Note if you click on the Text at the bottom you will see these selections made create this command to add the extra column with the name 'insert_date' on the target with the integration timestamp
/Name=insert_date /Extra /IntegrateExpression={hvr_integ_tstamp} /Datatype=datetime
Step 5] Now initialize the channel
Step 6] Next, Refresh to instantiate the existing data in the table from the source to the target
Step 7] Then start the Capture & Integrate jobs
Step 8] Describe to look at your target structure then Insert a few records into your source table then check the data your target table
SQL> desc verify.myusers Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER NAME VARCHAR2(10) INSERT_DATE NOT NULL TIMESTAMP(6)
And the data that is now there
SQL> select id, name,to_char(insert_date,'DD-MON-YYYY HH24:MI:SS') inserted from verify.myusers; ID NAME INSERTED ---------- ---------- ----------------------------- 1 Bob 02-MAY-2019 14:44:57 2 Tim 02-MAY-2019 14:46:22 3 Brad 02-MAY-2019 14:47:14 4 Bobby 02-MAY-2019 14:48:03