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

KellyHobson — Fri, 08 Jul 2022 09:50:09 GMT

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

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.

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

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

Workaround:

Environment