topic Re: Skip Deletes transactions for a table in Qlik Replicate

Skip Deletes transactions for a table

murali23krishna — Wed, 13 Dec 2023 11:46:08 GMT

Hi Team,

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?

Appreciate your help.

Re: Skip Deletes transactions for a table

boaz_newman — Wed, 13 Dec 2023 14:18:47 GMT

Hello

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-->Target Table Preparation-->If target table already exists:

If one chooses "Do nothing" then constraint violation of duplicates will fail the task.

Regards

Boaz

Re: Skip Deletes transactions for a table

murali23krishna — Wed, 13 Dec 2023 14:29:20 GMT

1. Database type type is oracle and the command used for purging is DELETE

Re: Skip Deletes transactions for a table

boaz_newman — Wed, 13 Dec 2023 14:46:46 GMT

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.

Boaz

Re: Skip Deletes transactions for a table

murali23krishna — Thu, 14 Dec 2023 04:14:29 GMT

is there way if we truncate the table we can skip those data using qlik replicate

Re: Skip Deletes transactions for a table

Heinvandenheuvel — Thu, 14 Dec 2023 15:35:54 GMT

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.

Hein.

Re: Skip Deletes transactions for a table

john_wang — Sat, 16 Dec 2023 02:40:21 GMT

Hello @murali23krishna ,

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.

Re: Skip Deletes transactions for a table

shashi_holla — Thu, 28 Dec 2023 00:01:33 GMT

@murali23krishna

Looks like you are talking about soft delete operation. Please refer the following articles which provides more details on the same:

https://community.qlik.com/t5/Official-Support-Articles/How-to-create-a-Qlik-Replicate-task-to-capture-soft-deletes-and/ta-p/2121662

https://community.qlik.com/t5/Official-Support-Articles/How-to-implement-quot-soft-quot-deletes-on-replicated-table/ta-p/1825769

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Global-Transformation-Soft-Delete-Limitation/ta-p/2054414

Thank you,