What is the best strategy to have single capture and have a regular target table and a timekey table for every captured table

Scenario

A customer inquired to see what the best strategy is to have single capture and have a regular target table and a timekey table for every captured table? The client needs to maintain a regular/mirrored copy of the source tables and timekey versions of the table. Additionally, the requirement is to connect to the target with one login but populate the two different target schemas.

Overview

The HVR timekey feature provides the . This means we record a security-relevant chronological record, set of records, and/or destination and source of records that provide documentary evidence of the sequence of activities that have affected at any time a specific operation, procedure, or event. Which simply means we perform inserts only into the target database so you can track changes to each record in chronological order. An audit table is a table that contains the full history of rows changes. Based on the primary key of a row in the source table, one can query the full history of the row in the audit table and find out when the row was created, modified (possibly many times), and maybe eventually deleted. Most replication tools provide the ability to maintain audit tables and so does HVR. Some real-time data replication vendors have a separate product on their price list for auditing when in the end it is the same core replication capability that provides the functionality (you just pay for it twice).
We are going to create a single channel, with one source location group and two location groups for the target. You need to configure two separate connections to the target i.e. two locations and for this customer using the same connection credentials. We are going to replicate the historical transition records (timekey records) to one schema and the oltp transactions into separate schema on the target.
At a high level, you define your locations then after that, the setup is straight forward. You will set up replication as you do normally for transactions. The setup for timekey is

1. Add a column to every table to include the last time the row was updated. In HVR this is done through an action ColumnProperties with an IntegrateExpression {} – i.e. the timestamp when the row was captured by HVR from the transaction log. This column is made a key column by checking a check box in the action. Additionally add what operation was performed on the source database (I/U/D) by with the /IntegrateExpression {hvr_op} and adding it to the data to be loaded on the target

Pre-requisite

1. You have a channel with 1 source location S_TPC and 2 Target locations T_CDC and T_OLTP. The target location T_CDC will have the data inserted along with the transaction date, hvr integrate key that has been added to its key value and also the DML operation that was performed on the row. The transactional target, T_OLTP, will receive the transactional data that was processed on the source database
2. You have simple capture and integrate actions for source and Target locations.

Step 1: Add extra columns to the Target T_CDC
       1) Add ColumnProperties action on the T_CDC target to get capture_time from the source for each table. Follow the same for test2 table. {hvr_cap_tstamp} is the timestamp when the row was captured in the transaction log

        chcdc       T_CDC            test1    *          ColumnProperties /Name=capture_time /Extra /IntegrateExpression={hvr_cap_tstamp} /Datatype="timestamp (oracle)"   

       2) Add ColumnProperties action on the T_CDC target to get dml operation for each table. Follow the same for test2 table. Dml operation is the operation that was                performed on the source.

         chcdc      T_CDC            test1    *          ColumnProperties /Name=dml_op /Extra /IntegrateExpression={hvr_op} /Datatype=integer  

       3) Add ColumnProperties action on the T_CDC target to get hvr_integ_key for each table. Follow the same for test2 table. Hvr_integ_key is replaced with 16 byte string value (hex characters) which is unique and continuously increasing for all rows integrated into the target location. By defining this as the timekey we are telling hvr to sort the table on this column.

         chcdc      T_CDC            test1    *          ColumnProperties /Name=hvr_integ_key /Extra /IntegrateExpression={hvr_integ_key} /TimeKey /Datatype=varchar2 /Length=100  

Step 2: Add TableProperties action to add CoerceErrorPolicy

chcdc       *          *          *          TableProperties /CoerceErrorPolicy=SILENT /CoerceErrorType=ROUNDING       

Step 3: Optionally you can add /NoBeforeUpdate parameter to capture action incase you don’t want to capture before image in your T_CDC target for the tables. By default hvr will replicate the before update image for a row which is identified by 4.

     
The final configuration would look like this

Step 4: Perform initialize with Scripts and Jobs

Step 5: Perform refresh to recreate the table on the Target with new columns.