How to report only the soft deletes in a different target table appended with the name _deletes”?  Additionally, what is the best way to capture a timestamp when the record was changed?

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #18187

    Donna Zehl
    Keymaster

    How to report only the soft deletes in a different target table appended with the name <table_name>_deletes”?  Additionally, what is the best way to capture a timestamp when the record was changed?

     

    Tag: 28581

     

    Details:

    To achieve this you’ll have to add the same table twice with a different name to the channel. One of the tables should have an /Extra column with /SoftDelete transformation and an /IntegrateCondition which filters the dataset to the deleted rows. On the other table HVR will always perform a hard-delete.

     

    As far as capturing timestamp, there are two variables  that represent two different things.

    {hvr_cap_timestamp}: The date and time that the change occurred on the source location
    {hvr_integ_tstamp}: The date and time that the change was integrated into the target location.
    
    
    

    So if you want to track the time when the record was changed in the source then I would suggest using {hvr_cap_timestamp}. If you want to track when the record was changed on the target then you would use {hvr_integ_tstamp}. For this example I will record the time the record is populated on the target

     

    Below is my test channel’s configuration. I have created 2 tables on the source. T1 & T1_deletes

     

    Note that the two tables have the same base table name, T1

     

     

     

    Here are my action definitions.

    We need both a Capture and an Integrate action to move the data.  In addition I’ve created one ColumnProperties actios on the target that is says I want to create an extra column on the target t1_deleted table, hvr_is_deleted . And a second ColumnProperties to capture a timestamp (hvr_integ_tstamp ) of when that the row was removed from the original T1 table and inserted to the T1_deletes table, column tstamp.

     

    The Action Restrict defines that only rows that satisfy a certain condition should be replicated. The restriction logic is enforced during capture and integration and also during compare and refresh.

     

    Finally Action TableProperties defines properties of a replicated table in a database location. The action has no effect other than that of its parameters.  So in this case it is used only as a mapping. These parameters affect both replication (on the capture and integrate side) and HVR refresh and compare.

     

    The commands that I executed in my source system:

    SQL> create table t1 (a numeric, b char(100), constraint t28581_pk primary key (a, b));
    Table created.
    
    SQL> insert into t1 (a,b) values (10, 'a')
    1 row created.
    
    SQL> insert into t1 (a,b) values (11, 'b');
    1 row created.
    
    SQL> commit;
    Commit complete. 
    
    SQL>  delete from t1 where a = 11;
    1 row deleted.
    
    SQL> commit;
    Commit complete.

     

    And here’s the output I got in my target database:

    SQL> select * from demo_t.t1;
             A       B
    ---------- --------
            10       a
    
    SQL> select * from demo_t.t1_deletes;
             A       B     HVR_IS_DELETED      TSTAMP
    ---------- --------  -------------------  -----------------------
            11       b                   1      25-FEB-2019 16:16:28

     

    As you can see a hard delete was performed on the “normal” table and the other table holds only the deleted rows in addition when the record was integrated on the target in the TSTAMP field {hvr_integ_tstamp}

     

    Note:

    The initial refresh will only be done for the base/original table, ie t1 and not for the table that contains deletes, ie t1_deletes as this table is empty on source.

    For initial setup the %_deletes table for this example it is t1_deletes. Do not refresh the %_deletes table , ie. t1_deletes as you won’t lose history on target. This should be an insert only table on target & not refreshed else previous transactions that were moved could be lost.

     

     

Viewing 1 post (of 1 total)

The forum ‘How to’ is closed to new topics and replies.

© 2019 HVR

Live Demo Contact Us