Introduction

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

Environment

Local Data Processing

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

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'

   

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

   

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}.

   

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

5. Now initialize the channel

   

6. Next, Refresh to instantiate the existing data in the table from the source to the target

   

7. Then start the Capture & Integrate jobs

   

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