Batch apply mode and record order

[MoeE]

Hi,

I'm hoping someone can help me with this question. When replicating to a change table exclusively, and batch processing mode is enabled, are changes from the source applied in the same sequence to the change tables in the target? I'm aware that batch apply mode may not apply the changes in order as is done with transactional apply mode.

For example - Record A is updated, then record B is updated. Is it possible that when batch apply mode is selected, record B will arrive in the change table before Record A?

Help with this question is appreciated as always. Thank you!

Regards,

Mohammed

[OritA]

Hi, 

In general Replicate reads the CDC events in the order that they were performed. However, when working in bulk apply mode it combines CDC events (of a transaction) when possible to one update event. For example if few update were performed to the same row it will collect all the updates (in the order they arrived) and perform one update to the row.
(FYI here is a link to the related documentation section: https://help.qlik.com/en-US/replicate/November2024/Content/Global_Common/Content/SharedEMReplicate/C...)
Therefore, if you want to preserve the order of updates in a transaction you should work in transactional apply mode however, from performance perspective,  this is less efficient then batch apply mode.

Regards,

Orit

[MoeE]

Hi Orit,

Thanks for the help, it is appreciated.

If batch apply mode is enabled, is it possible that changes can arrive in the target not in order? ie, as explained in my original question, record A is updated before record B, but the change for record B arrives in the target table before record A? Thanks

Regards,

Mohammed

[Dana_Baldwin]

Hi @MoeE 

Yes, it is possible that record B will be updated before record A in your scenario, but not certain.

Are you concerned about foreign key constraints on the target?

By the way, if your target is Oracle, there is an option for batch mode called "preserve transactional integrity" but it is only supported with Oracle target.

Thanks,

Dana

[MoeE]

Hi Orit,

Furthermore, I tested running 3 updates to the same record in one transaction then running a commit. I was unable to recreate the behaviour where QR would bundle each of these updates and apply them as one update. I'm not sure why, maybe it requires a larger amount of operations in each transaction before it begins to optimise?

Regards,

Mohammed

[MoeE]

Hi Dana,

Thanks for the response. I'm assuming that this applies to change tables as well am I correct?

Regards,

Mohammed

[Dana_Baldwin]

Hi @MoeE 

I am not sure that choosing batch apply over transactional apply for the base table updates has any effect on the store changes tables as they are all inserts. Also, you can have a task that stores changes but does not apply them to the base tables.

Maybe someone else can clarify on this.

Thanks,

Dana

[Dana_Baldwin]

Hi @MoeE 

The stored changes should be in commit time whether they are inserted in batch mode or transactional