How To Define a Target Column With a Timestamp That Is Populated With a Date & Time When the Row Is Replicated

Question

I 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 as a record inserted into the target for possibly for another ETL process to pick up or some other requirement.

How can I define a target column with a timestamp that is populated with a date and time when the row is replicated?

Environment

HVR 5

Answer

The substitution you’re looking for is {hvr_integ_tstamp} that you want to add to the target table. Learn more about this variable in our ColumnProperties documentation.

{hvr_integ_tstamp [ spec ] } is replaced with the time when 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]. However, this can be overridden using the timestamp substitution format specifier spec. For more information, see our Timestamp Substitution Format Specifier.

Steps

1. Create two locations (this example uses the names sora and tora), then create a channel ch. Add two location groups for each of the locations: OSRC for the location sora and OVERIFY for the target location tora.

   

2. Launch Table Explorer and select the table(s) in your replication set. (This example only selects the table myusers.)

   

   The Table Exploreer shoqs the structure of the myusers table in the source.

   

3. For replication, you must define two actions – Capture and Integrate. You also need to add another action called /ColumnProperties to define the Extra field we want to replicate ({hvr_integ_tstamp}).

   

4. Add the following /ColumnProperties Action to the target group, OVERIFY.

   

   NOTE: If you click on Text at the bottom, you will see that the selections created 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. Initialize the channel.

   

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

   

7. Start the Capture & Integrate jobs.

   

8. Look at your target structure, then Insert a few records into your source table. Finally, check the data in your target table.

SQL> desc verify.myusers Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER NAME VARCHAR2(10) INSERT_DATE NOT NULL TIMESTAMP(6)

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