Assume that we have a table configured for replication and periodically the table gets purged as part of the cleanup activity, is there an intelligent way Qlik to identify those delete statements and mark them as skipped from the regular delete events? is there any such configuration available?
Also, another question that I have is, the replication setup is done and due to some reason task failed, if we use reload functionality, how it will ensure that reload will not cause any duplication of events? is there any documentation of the internal working of rdlib library?
1. Regarding "periodically the table gets purged" - what is the database type? (Oracle, SQLServer etc.) What is the command that is used to do this "purge"? is it a DROP TABLE command? or a TRUNCATE TABLE? or something else?
2. When a task is reloaded, the behavior upon the target table (dropping, truncating or doing nothing) depends on the task settings. Task settings-->Full Load-->Full Load Settings-->If target table already exists:
If one chooses "Do nothing" then constraint violation of duplicates will fail the task.
If an entire table is deleted using a delete statement (DELETE FROM <Table Name>) then this will show in the Oracle redo-log as a regular delete, and Replicate will capture and apply it to the target, there is no way to ignore it.
Well, if you can use TRUNCATE instead of delete on the source, the you could change the task setting for "When source table is truncated:" to "Ignore TRUNCATE" in the UI.
If you need to use DELETE on the source, then as Boaz indicated every single row affected by the delete will become a delete event and will be processed. You would have to filter those out somehow. Perhaps you could have a dedicated source control table entry with 'truncate_date' and use a source_lookup with TLL of a few minutes to see is a delete is in the truncate range or not.
The brute force solution is to declare or utilize a maintenance window around the deletes: Stop replicating in the window. Wait for it to be 'done'. Delete. re-start replication with 'timestamp tables are already loaded'. Close maintenance window. The risk/gotcha is of course the all changes in the maintenance window will be ignored and you may have to reload some tables to deal with the discrepancies due to that.
I totally agree with @Heinvandenheuvel , the best option is using a filter in Replicate. BTW, I do not see other options in Oracle side eg trying to prevent the DELETE operations are recorded into REDO LOG attempts etc.
Good luck,
John.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
.png "Former Employee"){kind=icon}
