How to: Define a target column with a timestamp that is populated with a date & time when the row is replicated

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