Delete and Inserts being interpreted as Updates by Replicate?

[MoeyE]

Hi Support,

I hope whoever's reading this is having a good day.

This is the client's current setup. QR 2023.05.322. Task 1 is Microsoft SQL server to Microsoft SQL Server. They're both on prem. Task 2 goes from Microsoft SQL Server to PostgreSQL. Task 2's source endpoint is Task 1's target endpoint. It also uses the same tables. 

So to reiterate, it goes from database A to database B in Task 1. Then in Task 2, it goes from database B to database C. The tables are the same.

The strange behaviour is that in Task 1, a table receives only Inserts and Deletes. In Task 2, this same table doesn't show any Inserts or Deletes, it only shows that it is receiving updates. This is how the records are being changed on the source table. They do have primary keys.

My question is, is this how Qlik Replicate functions by default? My current theory is that the Delete/Inserts to a record is counted as an Update in Task 2, I just need confirmation or some further insight into how exactly this all works. Furthermore, I do see in the Microsoft SQL Server source limitations, a limitation for a task that does INSERT/DELETE instead of an UPDATE. https://help.qlik.com/en-US/replicate/May2024/Content/Global_Common/Content/SharedReplicateHDD/SQLSe... 

Regards,

Mohammed 

[sureshkumar]

Hello @MoeyE 

I have tried to replicate same behavior in my lab. i.e MSSQL-MSSQL [Task1] and MSSQL-POSTGRE [Task2]. Task 1's target is Source to Task 2.

Performed Inserts and Deletes on Source of Task1 and same behavior i have observed in Task2 as well. i,e INSERT and Delete DML operations captured not as UPDATE in Task2.

Limitations is regarding updating the Primark Key/Unique Index Key. Are you updating the PK/UI key?

Regards,

Suresh

[john_wang]

Hi Mohammed, @MoeyE 

Not sure what's the apply mode of task 1. If you change the apply mode from Batch mode to Transactional Mode in task 1, does it make difference?

BTW, if this happens to all data, or some rows only.

thanks,

John.

[MoeyE]

Hi John,

I initially tried in Batch Apply mode. This looks to be happening with all records that have a DELETE then INSERT done on it, in order to update it. Some inserts and deletes get through but it looks like it's because they are single actions done on these records. However, doing a DELETE then an INSERT at the same time results in it being interpreted as an UPDATE in Task 2.

I also tried in putting the first task Transactional Apply mode. Then I did a DELETE and INSERT of a record and executed them at the same time. Interestingly, the result showed INSERT and DELETE in the change processing monitor for both Task 1 and Task 2. I switched back to Batch Apply mode and did the same DELETE then INSERT in one execution, and it showed as UPDATE in task 2.

However, I also realised that doing a DELETE, executing the delete, then doing an INSERT, then executing the insert resulted in insert and delete being shown in both task 1 and task 2. No update was shown. 

I'm not really sure the exact database/Replicate mechanics behind this but it appears that when a DELETE/INSERT is executed at the same time for a record, it is bundled into the same transaction and applied to the table. Replicate then sees this as an UPDATE in task 2. I'm still trying to think about how exactly this is possible. Can someone please explain the mechanics behind this? It would be much appreciated

Regards,

Mohammed

[john_wang · Accepted solution]

Hello Mohammed, @MoeyE 

Thanks for the detailed information. This is exactly Batch Apply Mode behavior and how the transactions are optimized:

1- DELETE + INSERT operations are performed in source table and the 2 operations fall into the same applying "batch" then it will be converted to an UPDATE;

2-INSERT + UPDATE operations are converted to a single INSERT with the latest values;

However if the above operations are separated into different batches (eg put DELETE and INSERT into to different transactions and the time gap is long enough) then they will be individual DELETE and INSERT operations rather than a single UPDATE.

Hope this helps,

John.

[MoeyE]

Hi John,

Thanks for the confirmation. Much appreciated.

Regards,

Mohammed

[john_wang]

Thank you so much for your great support! Mohammed @MoeyE