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

[Anonymous]

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.

[SwathiPulagam · Accepted solution]

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/...

Thanks,

Swathi

[lyka]

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

[Anonymous]

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

[lyka]

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

[Anonymous]

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.

[lyka]

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

[Anonymous]

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

[Anonymous]

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?

[Barb_Fill21]

@Anonymous 

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

Many Thanks

Barb