Qlik Replicate table with changing PKs causing latency and Error creating Net Changes in Batch Optimized Apply mode

[KellyHobson]

Task Details:

Source - Oracle
Target - Microsoft Azure Synapse Analytics

Issue:

The customer experienced large latency on a task which contains 216 tables. The task hit 26hrs+ of latency during CDC mode.

The Customer seeing the following the in the log:

00011428: 2022-07-01T03:11:36 [ASSERTION ]W: The 'INSERT' event received for table xxx after 'UPDATE (3)' event with the same primary key at stream position '2476;637921680139646570;20211205001400949446|000020e7.688c8538.00000001.002c.01.0001:503765.287848.16' (bulk_apply.c:1664)
00011428: 2022-07-01T03:11:36 [TARGET_APPLY ]T: Failed to execute statement. Error is: Error creating Net Changes in Batch Optimized Apply mode. For details, see the task log.
INSERT INTO [stgClinIh].[attrep_apply_exceptions] ( [TASK_NAME],[TABLE_OWNER],[TABLE_NAME],[ERROR_TIME],[STATEMENT],[ERROR] ) VALUES ('IHPRD_LS_SYNPSPRD','stgClinIh','xxx','2022-07-01 07:11:36.443','INSERT INTO [stgClinIh].[attrep_apply_exceptions] ( [TASK_NAME],[TABLE_OWNER],[TABLE_NAME],[ERROR_TIME],[STATEMENT],[ERROR] ) VALUES (''IHPRD_LS_SYNPSPRD'',''stgClinIh'',''xxx'',''xxx'', _FLAG],[OWNER],[PREVIOUS_OWNER],[UPDATE_COUNT],[ENROLLMENT_START_END_IND],[CONF_IN_SCREENING],[CONF_IN_BASELINE],[CONF_IN_TREATMENT],[CONF_IN_FOLLOW_UP],[CONF_IN_FOLLOW_UP_DROP_TREAT],[CONF_COMP_TRIAL_DROP_TREAT],[CONF_COMPLETED_TRIAL],[LOAD_DTM],[DELETED_FLAG],[DELETED_DATETIME] ) VALUES (xxx,''xx'',''xx'',''xxxx'',x,x,''xxxx'',''x'',''xx'',xx,xx,''x'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,''xxxx'',''N'',NULL)','Error creating Net Changes in Batch Optimized Apply mode. For details, see the task log.') (endpointshell.c:6679)

We confirmed PKs (primary keys) were changing for two of the tables in the task. This will cause an error with the batch apply mode and can lead to latency.

 

Workaround:

The customer removed the two tables with changing PKs from task and latency dropped to an acceptable level.

The customer moved the tables to a separate task and planned to manage the tables with full loads.

Ref #16 from this article:

Troubleshooting Qlik Replicate Latency and Performance Issues

Environment

• Qlik Replicate 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.