topic Re: Prevent DELETE statement to be executed on target tables when doing CDC. in Qlik Replicate

Prevent DELETE statement to be executed on target tables when doing CDC.

Anonymous — Tue, 14 Jun 2022 14:47:13 GMT

Is there any way to prevent DELETE statement to be executed in the target table, and instead preserve the deleted rows by just marking them as deleted? Qlik Replicate is performing DELETE statements on my Redshift target endpoint and I dont want it to happen.

Regards.

Re: Prevent DELETE statement to be executed on target tables when doing CDC.

SwathiPulagam — Tue, 14 Jun 2022 14:54:49 GMT

Hi @rmartinezdezayas,

You can achieve this by doing soft delete.

Please follow the below article for more information:

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

Thanks,

Swathi

Re: Prevent DELETE statement to be executed on target tables when doing CDC.

lyka — Tue, 14 Jun 2022 14:55:24 GMT

Good day!

Let me know if this will help. Please refer to the article:

https://community.qlik.com/t5/Knowledge/Filter-Deletes-in-Replicate/ta-p/1801945

Thanks

Lyka

Re: Prevent DELETE statement to be executed on target tables when doing CDC.

Anonymous — Tue, 14 Jun 2022 15:43:18 GMT

Hi, thanks for the reply.

In my case soft delete is not working fine with Redshift target endpoint.
As you can see on the image, i have a global rule to track in a column the operation type (delete, update, insert) and still some rows are being deleted on target table. And after the delete, it insert again the same batch, to the same table of course.

delete code in Redshift:

DELETE FROM "schema"."table" WHERE ("var1", "var2", "var3", "var4") IN ( SELECT CAST ( "schema"."attrep_changes307356B32BD75E25"."seg1" as NVARCHAR(9) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."seg2" as NVARCHAR(30) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."seg3" as NVARCHAR(18) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."seg4" as NVARCHAR(12) ) FROM "schema"."attrep_changes307356B32BD75E25" WHERE "schema"."attrep_changes307356B32BD75E25"."seq" >= 7011 and "schema"."attrep_changes307356B32BD75E25"."seq" <= 7016 )

Insert code in Redshift:

INSERT INTO "schema"."table" ( "var1", "var2", ...etc "qlik_event_type" ) SELECT CAST ( "schema"."attrep_changes307356B32BD75E25"."col1" as NVARCHAR(51) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col2" as TIMESTAMP ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col3" as TIMESTAMP ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col4" as TIMESTAMP ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col5" as TIMESTAMP ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col6" as TIMESTAMP ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col7" as TIMESTAMP ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col8" as TIMESTAMP ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col9" as TIMESTAMP ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col10" as TIMESTAMP ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col11" as NVARCHAR(36) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col12" as NVARCHAR(30) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col13" as NVARCHAR(30) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col14" as NVARCHAR(30) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col15" as NVARCHAR(30) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col16" as NVARCHAR(30) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col17" as NVARCHAR(30) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col18" as NVARCHAR(30) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col19" as NVARCHAR(24) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col20" as FLOAT8 ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col21" as NVARCHAR(20) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col22" as NVARCHAR(20) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col23" as NVARCHAR(20) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col24" as NVARCHAR(20) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col25" as NVARCHAR(20) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col26" as NVARCHAR(18) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col27" as NVARCHAR(18) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col28" as NUMERIC(13, 3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col29" as NUMERIC(13, 3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col30" as NUMERIC(13, 3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col31" as NUMERIC(13, 3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col32" as NUMERIC(13, 3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col33" as NUMERIC(13, 3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col34" as NUMERIC(13, 3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col35" as NVARCHAR(15) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col36" as NVARCHAR(12) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col37" as NVARCHAR(12) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col38" as NVARCHAR(12) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col39" as NVARCHAR(9) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col40" as NVARCHAR(9) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col41" as NVARCHAR(9) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col42" as NVARCHAR(9) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col43" as NVARCHAR(9) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col44" as NVARCHAR(9) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col45" as NUMERIC(5, 0) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col46" as NUMERIC(5, 0) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col47" as NVARCHAR(6) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col48" as NVARCHAR(6) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col49" as NVARCHAR(6) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col50" as NVARCHAR(6) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col51" as NVARCHAR(3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col52" as NVARCHAR(3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col53" as NVARCHAR(3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col54" as NVARCHAR(3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col55" as NVARCHAR(3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col56" as NVARCHAR(3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col57" as NVARCHAR(3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col58" as NVARCHAR(3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col59" as NVARCHAR(3) ), CAST ( "schema"."attrep_changes307356B32BD75E25"."col60" as NVARCHAR(3) ) FROM "schema"."attrep_changes307356B32BD75E25" WHERE "schema"."attrep_changes307356B32BD75E25"."seq" >= 7011 and "schema"."attrep_changes307356B32BD75E25"."seq" <= 7016

Re: Prevent DELETE statement to be executed on target tables when doing CDC.

lyka — Tue, 14 Jun 2022 15:57:27 GMT

Hello,

Can you try to filter the delete operation to make sure that we dont apply the delete transactions on the target.

Here is the article on how to filter deletes.

https://community.qlik.com/t5/Knowledge/Filter-Deletes-in-Replicate/ta-p/1801945

Thanks

Lyka

Re: Prevent DELETE statement to be executed on target tables when doing CDC.

Anonymous — Tue, 14 Jun 2022 19:02:41 GMT

It looks like when having __ct tables besides the target table, the DELETE statement is executed on the target table and then insert again the deleted rows in another INSERT statement executed after a few seconds. If I do not use the __ct table then there will be only one statement executed (an update). This is what I have read so far.

If this behavior is normal, I believe it would be better if you could work on this not to happen this way if future updates of Qlik Replicate.

Thanks for the links shared and the quick response.
Best regards.

Re: Prevent DELETE statement to be executed on target tables when doing CDC.

lyka — Tue, 14 Jun 2022 20:27:38 GMT

Whats the source endpoint? I've seen this behavior with paired transactions wherein a delete followed by an insert with the same LSN will be treated as an update as we are netting the changes.

Thanks

Lyka

Re: Prevent DELETE statement to be executed on target tables when doing CDC.

Anonymous — Wed, 15 Jun 2022 06:28:10 GMT

This task takes changes from Logstream to Redshigt. The source of the task that put the changes in the Logstream is SAP Application (DB). So it looks like this:

Task1- SAP Application (DB) >> Logstream
Task2- Logstream >> Redshift

Re: Prevent DELETE statement to be executed on target tables when doing CDC.

Anonymous — Wed, 15 Jun 2022 11:34:58 GMT

Any updates? I still have INSERT statements after DELETE statements over the same records on my Qlik Replicate SAP Application (DB) to AWS Redshif task. Is this a common thing?

Re: Prevent DELETE statement to be executed on target tables when doing CDC.

Barb_Fill21 — Fri, 17 Jun 2022 21:58:38 GMT

@Anonymous

What is the backend Database for SAP Application (DB)?

Many Thanks

Barb