topic Re: How do these Error Handling settings function without primary keys? in Qlik Replicate

How do these Error Handling settings function without primary keys?

MoeE — Tue, 19 Mar 2024 22:34:20 GMT

Hi,

If there is no primary key on the target database how does this function? I know that according to this article: Click here, it does a delete of all the records in the batch then just does an insert. However if there are no primary keys to identify the record, does it simply scan the table till it finds it? Assume the task is initially in batch apply processing mode.

This is for a task that has only tables without primary keys and searching for ways to stop it from entering one-by-one mode whenever changes appear. Any pointers with this scenario would be helpful. Thank you.

Regards,

Mohammed

Re: How do these Error Handling settings function without primary keys?

john_wang — Wed, 20 Mar 2024 01:59:07 GMT

Hello Mohammed, @MoeE

Thanks for opening the article. Good question!

Assume the task is initially in batch apply processing mode.

Batch Optimized Apply mode requires the table has Unique Index or Primary Key, otherwise it will be switch to Transactional Apply mode.

Regarding the error handling policy and one-by-one mode doubts, If you can share the source/target databases type, we may confirm it for you again, much better if you can open a support ticket.

Regards,

John.

Re: How do these Error Handling settings function without primary keys?

DesmondWOO — Wed, 20 Mar 2024 02:36:17 GMT

Hi @MoeE ,

As John mentioned earlier, please provide source/target database types since the handling method may vary. I observe that "Apply Conflicts Handling Policy" setting may affect also.

In my environment, when I set "Insert the missing target record", INSERT records are inserted into the attrep_change table. However, if the setting is "Log record to the exceptions table", no changes are inserted into the same table.

For further investigation, please submit a support ticket and provide a diagnostic package.

Regards,
Desmond

Re: How do these Error Handling settings function without primary keys?

MoeE — Wed, 20 Mar 2024 02:39:03 GMT

Hi @john_wang and @DesmondWOO ,

Source is Oracle and target is SQL Server. Just wondering about the behavior in this particular scenario. Thanks.

Regards,

Mohammed

Re: How do these Error Handling settings function without primary keys?

DesmondWOO — Wed, 20 Mar 2024 05:01:15 GMT

Hi @MoeE ,

Please find my test result based on your particular scenario.

INSERT: Changes are applied from the attrep_change table.
UPDATE/DELETE: It is one-by-one.

You can verify the result by enabling verbose logging on TARGET_APPLY.

Regards,
Desmond

Re: How do these Error Handling settings function without primary keys?

SushilKumar — Fri, 22 Mar 2024 01:56:20 GMT

Hello @MoeE

primary keys are serves not only as a means to uniquely identify each record but also supports the performance, integrity, defining primary keys is essential for efficient and reliable way for DML Operation as This is crucial for accurately identifying records.

The more QR accurately and efficiently identify the records it will process UPDATE/DELETE faster, in that PK plays a very big role.

Regards,

Sushil Kumar