topic Re: Batch optimized apply blocked by Db2 Backup in Qlik Replicate

Batch optimized apply blocked by Db2 Backup

micpage — Fri, 21 Feb 2025 15:28:26 GMT

Hello,

I wanted to ask here if you also have the problem that during an online backup, no data is saved in CDC mode with activated "Batch optimized apply" from the "Change Processing Tuning" Tasksettings (#Change Processing Tuning #Change Processing Tuning | Qlik Replicate Help)

I think the problem is that when truncating the temporary table, since a truncate and online backup are mutually exclusive in Db2 LUW. (https://www.ibm.com/docs/en/db2/11.5?topic=backup-compatibility-online-other-utilities)

So I only see the use of "Transactional apply" to get consistently the replicated data or have you an other idea?

00001816: 2025-02-20T11:56:45 [TASK_MANAGER ]I: Subtask #1 ended (replicationtask_util.c:591) 00003868: 2025-02-20T11:58:43 [TARGET_APPLY ]I: Net Changes table name for the task is 'attrep_changesE4DE529EF38FB2E7' (bulk_apply.c:3783) 00003868: 2025-02-20T12:00:45 [TARGET_APPLY ]I: Failed (retcode -1) to execute statement: TRUNCATE TABLE "CDC_QLIK"."attrep_changesE4DE529EF38FB2E7" IMMEDIATE [1022502] (ar_odbc_stmt.c:5082) 00003868: 2025-02-20T12:00:45 [TARGET_APPLY ]I: RetCode: SQL_ERROR SqlState: HY008 NativeError: -952 Message: [IBM][CLI Driver][DB2/LINUXX8664] SQL0952N Verarbeitung aufgrund eines Interrupts abgebrochen. SQLSTATE=57014 [1022502] (ar_odbc_stmt.c:5090) 00003868: 2025-02-20T12:00:45 [TARGET_APPLY ]I: RetCode: SQL_ERROR SqlState: HY008 NativeError: -952 Message: [IBM][CLI Driver][DB2/LINUXX8664] SQL0952N Verarbeitung aufgrund eines Interrupts abgebrochen. SQLSTATE=57014 [1022502] (ar_odbc_stmt.c:5090) 00003868: 2025-02-20T12:00:45 [TARGET_APPLY ]I: Execute truncate net changes table failed. 'TRUNCATE TABLE "CDC_QLIK"."attrep_changesE4DE529EF38FB2E7" IMMEDIATE' [1022502] (odbc_bulk.c:803) 00003868: 2025-02-20T12:00:45 [TARGET_APPLY ]I: Failed to truncate net changes table [1022502] (odbc_bulk.c:901) 00003868: 2025-02-20T12:00:45 [TARGET_APPLY ]I: Error executing command [1022502] (streamcomponent.c:1987) 00003868: 2025-02-20T12:00:45 [TASK_MANAGER ]I: Stream component failed at subtask 0, component st_0_DB2LUW-T-DTSDH [1022502] (subtask.c:1474) 00003868: 2025-02-20T12:00:45 [TARGET_APPLY ]I: Target component st_0_DB2LUW-T-DTSDH was detached because of recoverable error. Will try to reattach (subtask.c:1589) 00003868: 2025-02-20T12:00:50 [STREAM_COMPONEN ]I: Target last committed record id from the previous run is '141' (streamcomponent.c:1693)

Thanks in Advice,

Michael

Re: Batch optimized apply blocked by Db2 Backup

john_wang — Mon, 24 Feb 2025 04:00:01 GMT

Hello Michael, @micpage

Thanks for reaching out to Qlik Community!

It's unclear whether you've encountered this error during an ongoing backup operation. Please check the DB2 log file for more details, as Qlik Replicate only displays the error without providing further information. Alternatively, if the error disappears after the backup is complete, it may be intermittent.

Regards,

John.

Re: Batch optimized apply blocked by Db2 Backup

micpage — Mon, 24 Feb 2025 07:06:00 GMT

Hello @john_wang ,

This Information "Errors" in the Qlik Replicate Log appear during the backup. For Db2 there is no Error, because it is a normal Locktimeout. I also made a few attempts and found that as long as the temporary table is not deleted with Truncate, no new records are added to the target table as CDC replication. But if I use "Transactional apply", I have my current CDC replication. Even a short test by rewriting the TRUNCATE command in the Db2 profile to a DELETE ensured continuous CDC replication.

Have you never noticed such an impairment before, since you recommend the "batch optimized apply" everywhere?

Best Regards,

Michael

Re: Batch optimized apply blocked by Db2 Backup

john_wang — Tue, 25 Feb 2025 08:45:28 GMT

Hello Michael, @micpage

In general, it is recommended to set the task to Batch Optimized Apply mode for better performance and lower costs. Transactional Apply mode does not use the Net Change temporary table, which is why no conflicts occur during the backup.

Have you tried stopping and resuming the task when encountering the error?

thanks,

John.

Re: Batch optimized apply blocked by Db2 Backup

micpage — Fri, 07 Mar 2025 09:46:54 GMT

Hello John,

When i stop the task, the temporary table attrep_changes1CE0F43A4C68441A is dropped and when i start the task again during db2 online backup, the first changes were applied, until the next batch will truncate the new created Table attrep_changesFFC56AEFCBE1F628, who has got another name.

Best Regards,

Michael

Re: Batch optimized apply blocked by Db2 Backup

john_wang — Fri, 07 Mar 2025 13:12:13 GMT

Hi Michael, @micpage

I'd like suggest to open a support ticket we need more investigation on the behavior.

Thanks,

John.

Re: Batch optimized apply blocked by Db2 Backup

micpage — Fri, 07 Mar 2025 13:27:04 GMT

Hi John,

i open a case (00360284: Batch optimized apply blocked by Db2 Backup).

Thanks for Support.

Michael

Re: Batch optimized apply blocked by Db2 Backup

john_wang — Mon, 24 Mar 2025 03:50:25 GMT

Hello Michael, @micpage

Thank you so much for your outstanding support.

I’d like to summarize the latest status of the article and support ticket:

DB2 LUW Native Target Endpoint – Instead of using the generic ODBC target, a native endpoint for DB2 LUW is on our roadmap. We hope to introduce this feature later this year.
Truncate Table Behavior – In the current major versions, the internal parameter $info.query_syntax.truncate_table applies to both end-user target tables and Replicate’s internal net change tables. This is the expected behavior as of now. A feature request has been submitted, and we are awaiting feedback from the PM team.

I truly appreciate your exceptional support!

John.