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

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #21996
    Donna Zehl
    Keymaster

    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 IntegrateCondition {} – 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 /IntegrateCondition {hvr_op} and adding it to the data to be loaded on the target

     

    Assumptions:

    You have created 3 locations S_TPC for the source and 2 targets, T_CDC and T_OLTP. You have a requirement to connect with only one user id though the data is being replicated to 2 different schemas.

    The target location T_CDC will have the data inserted along with the transaction date 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 received the transactional data that was processed on the source database and remain in sync to a different schema, ‘verify’.

    a.  You also have created 3 location groups one for each, S_TPCC, T_CDC and T_OLTP.

             b. You have added two TableProperties actions to populate data to two different schemas on the target, schema ‘verify’ and schema ‘oltp’.

             c.  You have created a capture action on the source S_TPC

             d.  You have created to Integrate action with Resilience on the T_OLTP target location group

     

     

    Steps

     

    1. Add a column to every table to include the last time the row was updated for the location group T_CDC. In HVR this is done through an action ColumnProperties with an IntegrateCondition {hvr_cap_tstamp} – i.e. the timestamp when the row was captured in the transaction log.

    Note: HVR generates the DDL for the target tables including the extra columns to store the capture timestamp and the column to indicate whether a row was deleted.

    For additional info see https://www.hvr-software.com/docs/Extra_Columns_for_Capture,_Fail_and_History_Tables

    Action ColumnProperties with an IntegrateCondition {hvr_cap_tstamp}

     

    2.  Add an integrate action to add column (dml_op) to replicate the operation that was performed on the row to the target {hvr_op} on the location group T_CDC.

    Action ColumnProperties with an IntegrateCondition {hvr_op}

     

     

    /Name=dml_op /Extra /IntegrateExpression={hvr_op} /TimeKey /Datatype=integer

     

    3.  The set the Coerce Error Policy for the error type ‘Rounding’ on the T_CDC target and schema name ‘ verify’ destination

    T_CDC

     

    4.  The set the Coerce Error Policy for the error type ‘Rounding’ on the T_CDC target and schema name ‘ oltp’ destination

    T_OLTP

     

    5.  The final configuration would look like this

    ..

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

© 2020 HVR

Test drive Contact us