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

Viewing 1 post (of 1 total)
  • Author
  • #19312
    Donna Zehl

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


    Tag: 28974



    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.



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



    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


Viewing 1 post (of 1 total)
  • The forum ‘How to’ is closed to new topics and replies.

© 2020 HVR

Test drive Contact us