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.
Overview:
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 Local Data Processing 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 actions 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 ActionTableProperties 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 refresh and compare.
- Note: This integrate expression show is for Oracle. If it was Sql*Server the expression would be /Name=tstamp /Extra /IntegrateExpression="FORMAT ({hvr_integ_tstamp}, 'MM-dd-yyyy HH:mm:ss')" /Datatype=varchar /Length=36
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.