insertallrecords in QLIK replicate - Oracle

[vchilakamarthi]

Hello,

We are looking to migrate all our Golden Gate replication set up to QLIK.

Some of the tables using current golden gate replication has the following settings

insertallrecords - any insert,update,delete (DML) operations on source tables are converted as inserts in to target tables.
insertupdates - update operations on source tables are converted as inserts in to target tables.
insertdeletes - delete operations on source tables are converted as inserts in to target tables.

Upon checking the options in QLIK UI, I couldn't find any option available.

Does QLIK provide these options out of the box by QLIK or are they usually handled outside QLIK?

I know we can handle them with triggers but some of the tables are very busy and enabling triggers on them adds latency, so wondering if the requirement can be handled by QLIK itself with out any DB triggers.

[Bill_Steinagle · Accepted solution]

Hello,

Thank you for the posted question to the Replicate Forums. For the processing and applying of the rows from the Oracle Source to the Target you can check the Apply Conflicts setting where we do have the Error Handling Policy that can control how the row is processed. 

https://help.qlik.com/en-US/replicate/November2021/Content/Global_Common/Content/SharedEMReplicate/C...

Also as shown you can set the Updates/Inserts as shown in the Error Handling Policy for the Source to the Target:

Under Task Settings Error Handling Apply Conflicts

You can change these settings for the Inserts how they are processed

You can change the Updates and how they are processed

Hope this information is helpful please let me know.

Regards,

Bill Steinagle

[Steve_Nguyen · Accepted solution]

@vchilakamarthi

i believe you are looking for upsert, see below :

https://community.qlik.com/t5/Knowledge/Qlik-Replicate-Implementing-UPSERT-and-MERGE-modes-by-applyi...

[vchilakamarthi]

@Steve_Nguyen Thank you Steve,

Yes I have gone through the video and we dont have any issues with upsert and merge and it works great.

But we do have a current process in place with golden gate.....with deletes on the source.

where when a record is deleted at the source it's processed as an insert into the target table.

This target table is a global temp table and the trigger captures the insert, adds the delete operation value (marking it as deleted record), and then inserts it into another table.

The fact that such an arrangement was possible because the golden gate was providing a way to insert deletes on source into the target.

Now the question is if QLIK supports capturing a delete on source as an insert into the target with any options.

[Michael_Litz]

Hi,

I am not aware of a way replicate task can change a delete to an insert.

We do have the ability in the task to use operation_indicator to change the delete to an update and mark a field with a value. You would do an add column transformation (maybe call the field (DeletedFlag) on the target table side and add this code:  operation_indicator("Y", NULL, NULL)
This will prevent the record on the target from being deleted. And the new field you added (DeletedFlag) will have the value "Y" in it.

Unfortunately this will be via an update statement and not as an insert.

Please see this article:

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

Thanks,
Michael

[Dana_Baldwin · Accepted solution]

Hi @vchilakamarthi 

I think what you are looking for is a soft delete. If so, there are 2 different ways to achieve the functionality you are requesting:

1. By using the SQLite function operation_indicator() in a transformation, Replicate will not replicate delete operations on the source to the target. Reference: https://help.qlik.com/en-US/replicate/May2022/Content/Global_Common/Content/SharedEMReplicate/Custom...

2. When doing CDC have the Store Changes option enabled (with or without also using Apply Changes depending on your needs). When Store Changes is enabled CDC will be done on __CT tables. This way you will have a record of delete operations. Reference: https://help.qlik.com/en-US/replicate/May2022/Content/Global_Common/Content/SharedEMReplicate/Custom...

Please let us know if this helps.

Thanks,

Dana

[vchilakamarthi · Accepted solution]

Thank you .....that is what I was looking for and it worked perfectly when tested.