Qlik Replicate: Update Transactions are entering to attrep_apply_exception instead target table in SQLMI .

[Kohila]

Hi Everyone,

We are facing the issue in one of our Qlik replicate task having SQL server as source and SQLMI as target, LR Task type with full load + CDC. All our update transaction of few tables are logged  into apply exception, where update is based on the where clause having  column %%physloc%% . Came to know this is a virtual column used in SQL server, Not sure this is causing the issue and supported in Qlik replicate.

Sample Query : 

Version of Qlik replicate - 2022.5.0.815

Did anyone came across the same kind of issue, that records are skipped to Apply_Exception because of this virtual column having in update clause? 

[Heinvandenheuvel]

[Edit - I misread the Original Post thinking that the application added column %%phyloc%% not realizing it is a standard SQLserver artifact. They following assumes there was the need for an application provided computed field in the source table]

Hmm, virtual columns is an Oracle terminology and for that Replicate documents:  'Virtual columns are not supported." 

SQLserver calls them computed fields/columns. For that the Replicate Userguide in the general intro under "8 Adding and managing source endpoints" writes: "Changes to computed (virtual) columns will not be captured"

Of course not - those columns do not exist, they leave no trace (in the transaction log). 

Replicate will create the computed columns will be created on target instantiating them as regular columns during create and filled during full-load - but never update as part of CDC. 

What one typically would want to do is pre-create the table on target, or have Replicate create is once [Advance run - Metadata Only (for missing tables)  and then modify it to redefine that column as computed by - using the same formula as on the source. [Actually - that can really be done any time after the table create - before target usage. I'd be tempted to also explicitly drop the column from the Replication process in the task/table design.]

Anyway, I tried sql - to - sql and it all worked for me, with a 'normal' column name and column name as [%%computed%%]. So you have something special going.  Maybe there is no PK? That computed field is not part of the PK is it now?

You may have to show your source table definition, but first please show a sample  STATEMENT from the attrep_apply_exception  table (lightly redacted if excessive)  and what does the ERROR column show for that?

Hein.

fwiw... the userguide also indicates "l Microsoft SQL Server 2012 Target: When the target table is created manually with a computed column, Full Load replication is not supported in BCP mode. Disabling the "Use BCP for loading tables" option in the Advanced tab will resolve this issue."

[john_wang]

Hello @Kohila , copy @Heinvandenheuvel ,

For SQL Server and SQL MI target, Replicate utilizes a virtual column %%physloc%% in WHERE sub-clause to make sure ONLY SINGLE ONE row (probably the first row which meets the conditions) is updated even there are multiple duplicate rows in target table instead of UPDATE multiple rows:

1- The table does not have a PK nor Unique Index

2- The regular UPDATE statement may impact multiple duplicate rows or even all the rows because it's impossible to distinguish these duplicate rows by using all the tables columns in the WHERE clause

3- Task is using default update syntax "FROM_AT_THE_END"

Typically the UPDATE statement looks like:

UPDATE [SCOTT].[KITNOPK] SET [ID]=2, [NAME]='test', [NOTES]='abc'

WHERE %%physloc%% =
( SELECT TOP 1 %%physloc%%

  FROM "SCOTT"."KITNOPK"

  WHERE [ID]=2 AND [NAME]='noname' AND [NOTES]='abc' )

 We may custom the behavior to disable the "TOP 1" constraint and let Replicate update all desired rows which meet the WHERE clause condition.

We'd like to suggest opening a support case with verbose logging to check RCA of the applying failure.

Hope this helps.

Regards,

John.

[Kohila]

Thanks @john_wang @Heinvandenheuvel  for your inputs.

I could see these tables have either PK or unique index in the target /source. We have raised an Qlik case and do follow up for the same.

Thank you,

Kohila

[Kohila]

Hi John, Can you please advise how to custom the behavior to disable the "TOP 1" constraint. I would like to try that option.

I have couple of questions to get clarify.

1. We are using MS-CDC  enable DB with microsoft sql server db as source endpoint, update events will get  in CDC  mode. Please confirm.

2.  I am able to execute  the update statements which are missed to apply in target  and entered in execption table and could retrieve the result for physloc and able to get one row affected. If replicate able to capture the update and why its failing to execute the update statement, where I can execute  the same update  in SSMS.   

These tables are having no unique and no primary keys in source and target, but they do have non-clustered index. 

[Kohila]

please elaborate what you mean by "3- Task is using default update syntax "FROM_AT_THE_END""

[Kohila · Accepted solution]

We finally resolved this issue , by changing the datatype of the target column from Datetime to Datetime2, since it behaves differently, had limitation  for SQLMI on update. 

[john_wang]

Hello @Kohila ,

Glad to hear that. I'm sorry I missed your 2 messages.

>> please elaborate what you mean by "3- Task is using default update syntax "FROM_AT_THE_END"

Replicate may use different update syntax, it's configurable, in general the default one is the best choice. Glad to know the problem was solved completely and the update syntax is irrelevant. 

thank you,

John.