How To Define a Target Column With a Timestamp That Is Populated With a Date & Time When the Row Is Replicated
Introduction
How to define a target column with a timestamp that is populated with a date and time when the row is replicated.
Environment
HVR 5
Scenario
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 as a record inserted into the target for possibly for another ETL process to pick up or some other requirement.
Overview
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
Create two locations (this example uses the names
sora
andtora
), then create a channelch
. Add two location groups for each of the locations:OSRC
for the locationsora
andOVERIFY
for the target locationtora
.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.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}
).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
Initialize the channel.
Refresh to instantiate the existing data in the table from the source to the target.
Start the Capture & Integrate jobs.
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