Hope you are doing good :). I have a query. We had recently deployed a parent(Logstream) task and its related child tasks in PRD. Source is Oracle ERP endpoint and the target is an Oracle DB. There are about 1200+ tables which are split into High Volume(HV), Medium Volume(MV) and Low Volume(LV) tasks. Almost all the 1200+ tables at source have unique indexes which I duly ensured that all these tables at target has unique indexes as well to ensure CDC functions effectively.
We had used the function operation_indicator('D', 'U', 'I') in global rule by adding a new column change_oper for all the 1200+ tables such that it does not issue a physical delete at the target when source deletes a row and rather have the same row at the target with 'D' in the change_oper column.
But there are many tables which are physically deleting the row inspite of adding this function. When I checked the Qlik guidelines, I see the below:
The operation_indicator function is not supported on tables that do not have a Primary Key.
This function is not supported when:
The Apply Conflicts error handling policy is set toNo record found for applying an update:INSERT the missing target record.
I have a query, when Qlik mentions about primary key, is it only for the target tables, or does the source table also need primary keys? In this case, all the source tables have only unique index and also since its an ERP environment , adding primary keys at source is not an option. Will this work if the source tables has unique index and if I promote the existing unique indexes to primary keys at the target?
Besides we have also enabled the below in all the child task to ensure duplicates are not replicated(upsert mode):
The Apply Conflicts error handling policy is set toNo record found for applying an update:INSERT the missing target record.
Will changing this to "Ignore Record" cause duplicates? Is just having "Duplicate key when applying INSERT: UPDATE the existing target record" sufficient to ensure no duplicates are replicated?
Any insights on this would help us to resolve the issue. Thanks a lot in advance.
You have already identified the root cause in your description. This configuration is not supported when Soft Delete is enabled together with the following setting UPSERT mode:
The Apply Conflicts error handling policy is set to No record found for applying an update:INSERT the missing target record.
Feel free to let me know if I missed anything.
Thanks,
John.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Hello @john_wang , Thank you for the clarification on the Apply Conflicts. However could you also advise on the primary key query mentioned above?
When Qlik mentions about primary key, is it only for the target tables, or does the source table also need primary keys present? In this case, all the source tables have only unique index and and its not possible to add primary keys for the source tables. Will this work if the source tables has only unique index and if I promote the existing unique indexes to primary keys at the target?
Hello @john_wang , My query is solely on using the operation_indicator function for the soft delete approach. In the qlik help pages ,it states as the below.
The operation_indicator function is not supported on tables that do not have a Primary Key.
Hence I wanted to know if primary key is needed for both the source and target tables to ensure the soft delete works fine or will just having a unique index for the source and target tables be sufficient for it to work?
Sorry I was not sure if the earlier response that you had provided was indeed for the soft delete approach (operation_indicator function) or not.
Yes, I’m addressing the concerns raised in your previous comments. Let me summarize:
If the source table has a unique index, it is sufficient for Replicate. The unique index will be used and applied as the primary key or unique index on the target side.
If the source table has no primary key and no index, it is still possible to define a primary key on the target side to use change_oper function. However, the data must comply with primary key constraints (i.e., no duplicate values and no NULLs), subject to the rules of the specific target database.
I hope this clarifies your concerns.
John.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Hello @john_wang , Even after the source having unique index and the target having the same columns as primary key, I find an example of a table not capturing the soft deletes instead doing a physical/hard delete at the target. I have ensured that I changed the task settings for the below to "Ignore Record":
The Apply Conflicts error handling policy is set to No record found for applying an update:INSERT the missing target record.
However even after doing all this, I see there are records which are getting physically deleted instead of being captured.
I was able to find this out because from the target tables of our tasks, another process runs which will replicate these update/inserts and deletes on to another table in a different schema which does based on the change_oper column used for soft delete. Now since the deletes are occurring physically in our target table, this is causing duplicates in the final target table.
I have attached the logs and also the screenshot of the records being physically deleted. Any advise or resolution for this issue would be much appreciated.
Hi All, Just checking if anyone has had a moment to look into the above query. I'd really appreciate any guidance or clarification on this as it would help to understand why the deletes are not being captured as soft deletes.
