Does Change Processing Mode have an effect on how changes are applied to the change tables?

[MoeE]

Hi,

I hope whoever is reading this is having a good day. My question is basically summed up in the title. Does Change Processing Mode have an effect on how changes are applied to the change tables?

My second question is, would the change table's header values behave differently depending on which mode it is in? For example stream_position or header_change_seq, or any other column

I am unable to currently test this myself however my initial idea is that no, change processing mode shouldn't affect how the values behave in the change table. This is because of the nature of all the columns in the change table, it shouldn't matter which mode is chosen.

Also, i don't think it makes sense that transactional apply or batch apply would slow down or speed up the rate at which changes are applied to the change table. 

Can someone please confirm? thank you!

Regards,

Mohammed

[DesmondWOO]

Hi @MoeE ,

If the change processing mode is set to 'Transactional apply', Replicate applies each change to the target without using a net changes table.

Regarding the header columns, there should be no difference between 'Batch optimized apply' and 'Transactional apply'.

Regards,
Desmond

[MoeE]

Hi Desmond, 

Thanks for the response. To clarify, for the change tables, does choosing transactional apply or batch apply affect the rate at which changes are applied to the change table? For example, will QR apply changes one by one to the change table if transactional apply is selected?

Help with this is greatly appreciated. Thank you.

Regards,

Mohammed

[Heinvandenheuvel]

PSA - There is an earlier discussion in: 

https://community.qlik.com/t5/Qlik-Replicate/Usage-of-batch-apply-mode-and-transaction-apply-mode/td...

[MoeE]

Hi @Heinvandenheuvel, @john_wang,

As suggested by Hein, I've tested this.

Experiment - I ran one update statement on the source that affected 10 different records on the source table. These were the results.

With Transactional Apply mode enabled, Qlik Replicate ran the following statement on our target database:

(@P1 varchar(35),@P2 varchar(1),@P3 varbinary(128),@P4 varchar(128),@P5 varchar(12),@P6 varchar(32),@P7 datetime2,@P8 varchar(5),@P9 varchar(40),@P10 varchar(30),@P11 varchar(30),@P12 varchar(60),@P13 varchar(15),@P14 varchar(15),@P15 varchar(10),@P16 varchar(15),@P17 varchar(24),@P18 varchar(24))INSERT INTO [TAC_REQUEST_TESTING_14th_APRIL].[CUSTOMERS__ct]([header__change_seq],[header__change_oper],[header__change_mask],[header__stream_position],[header__operation],[header__transaction_id],[header__timestamp],[CUSTOMERID],[COMPANYNAME],[CONTACTNAME],[CONTACTTITLE],[ADDRESS],[CITY],[REGION],[POSTALCODE],[COUNTRY],[PHONE],[FAX]) values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18)

With Batch Apply mode enabled, Qlik Replicate ran this statement instead. It looks like it uses the "attrep_changes" table this time as is typically used when applying changes to the standard replication tables: 

(@P1 int,@P2 int)INSERT  INTO [TAC_REQUEST_TESTING_14th_APRIL].[CUSTOMERS__ct] ([header__change_mask],[header__stream_position],[ADDRESS],[COMPANYNAME],[header__timestamp],[header__change_seq],[header__transaction_id],[CONTACTNAME],[CONTACTTITLE],[PHONE],[FAX],[CITY],[REGION],[COUNTRY],[header__operation],[POSTALCODE],[CUSTOMERID],[header__change_oper]) SELECT CONVERT (  varbinary(128) , [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col1] , 1 ) , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col2] as  varchar(128)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col3] as  varchar(60)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col4] as  varchar(40)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col5] as  datetime2(6)) , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col6] as  varchar(35)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col7] as  varchar(32)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col8] as  varchar(30)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col9] as  varchar(30)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col10] as  varchar(24)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col11] as  varchar(24)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col12] as  varchar(15)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col13] as  varchar(15)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col14] as  varchar(15)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col15] as  varchar(12)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col16] as  varchar(10)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col17] as  varchar(5)) COLLATE Latin1_General_CI_AS , CAST ( [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[col18] as  varchar(1)) COLLATE Latin1_General_CI_AS  FROM [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F] WHERE [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[seq] >= @P1 and [TAC_Testing_January_2025].[attrep_changes35CAC4806C9DBB9F].[seq] <= @P2

From these results, I assume that when QR is in Batch apply mode, it will batch all the changes (not in order, no transactional integrity) and insert them into the change table in the target (table_name__ct) the same way is it does with a "standard" replication table. It does this by storing all the changes in the "attrep_changes" table then applying them.

With Transactional apply mode, I am assuming this also applies the changes in the order in which they occurred on the source and maintains transactional integrity. However, I can't exactly confirm this as I  expected to see 10 separate statements on the target database (one for each change) rather than just one. Any idea as to what exactly is going on here? Thank you.

Regards,

Mohammed

[john_wang]

Hello Mohammed, @MoeE , copy @Heinvandenheuvel 

Thanks for following up.

The SQLs you mentioned are getting from backend SQL Server. In Qlik Replicate, the __ct table operation SQL likes:

Execute: 'INSERT INTO [SCOTT].[TESTNUM__ct]([header__change_seq],[header__change_oper],[header__change_mask],[header__stream_position],[header__operation],[header__transaction_id],[header__timestamp],[ID],[N1],[N2],[N5],[N12]) values (?,?,?,?,?,?,?,?,?,?,?,?)'

You will get 20 such prepared SQLs (10 for BeforeImage and 10 for AfterImage) and they are in the same transaction sequence as the source side DMLs.

Regards,

John.