topic Re: How to force change table to update? in Qlik Replicate

How to force change table to update?

MoeE — Mon, 01 Jul 2024 06:24:26 GMT

Hi Support,

The change table's documentation states that when a record is updated but its values are not changed, then no entry will be created for this in the change table. Documentation Here.

However, a customer uses the change table for their downstream processes. On their tables they have added a timestamp column using Replicate, and are wondering if there is a way to have the change table reflect the latest update timestamp even if the value hasn't changed. If this is possible, please let me know it would be greatly appreciated.

Kind regards,

Mohammed

Re: How to force change table to update?

john_wang — Mon, 01 Jul 2024 06:31:51 GMT

Hello Mohammed, @MoeE

Thanks for reaching out to Qlik Community!

Would you please share the source and target DBs types? We'd like to confirm the behavior for you.

BTW, I think it means ALL COLUMNS instead of a single column in the source table changed. Let me know how many columns in the table and how many columns are changed?

thanks,

John.

Re: How to force change table to update?

MoeE — Mon, 01 Jul 2024 06:37:19 GMT

Hi,

The customer's environment is Oracle to Oracle. However, I did a test in my environment going from SQL Server to SQL Server, updating only a single column, and I was still able to recreate this behaviour. I will try a different source and target combination to see if this is also the case.

Regards,

Mohammed

Re: How to force change table to update?

john_wang — Mon, 01 Jul 2024 06:41:58 GMT

Thank you for the update @MoeE , please allow me some time, I will get back with my findings shortly.

From theory, Oracle records the row changes in redo log even if the before image duplicate to the the after image, certainly Replicate capture it too. It's maybe different in other DBs.

Re: How to force change table to update?

john_wang — Mon, 01 Jul 2024 08:10:21 GMT

Hello @MoeE ,

The behavior can be reproduced in Qlik Replicate 2024.5 GA release in an Oracle to Oracle task with change table enabled. Please raise a support ticket, our support team will confirm with R&D.

Before we get final conclusion from R&D, there is a workaround: we may filter out the row changes if all the columns before image equal to the after image. for example:

($AR_H_OPERATION != 'UPDATE') OR (($AR_H_OPERATION = 'UPDATE') AND (( $BI__c7 != $c7) OR ( $BI__c8 != $c8) OR ( $BI__c9 != $c9)))

where C7/C8/C9 are the columns which we want to filter out if these column before image values equal to after image values.

BTW, please take note that in GUI you still see the update being capture and applied to target however there is no new events written into __ct table.

Hope this helps.

John.

Re: How to force change table to update?

MoeE — Mon, 01 Jul 2024 23:12:21 GMT

Hi John,

Thanks for the help, much appreciated.

Regards,

Mohammed

Re: How to force change table to update?

john_wang — Tue, 02 Jul 2024 00:57:27 GMT

Thank you for your great support! @MoeE

Re: How to force change table to update?

Heinvandenheuvel — Tue, 02 Jul 2024 03:43:29 GMT

>> The behavior can be reproduced in Qlik Replicate 2024.5 GA release in an Oracle to Oracle task with change table enabled.

Well duh! Because that's how it is suppose to work. It's documented behaviour as the OP indicates.

>> Please raise a support ticket, our support team will confirm with R&D.

Don't bother. I questioned the behavior 10 years ago (Replicate V3?) and R&D confirmed. If I recall correctly, then at the time there was a major customer for that time for which the application often created those benign updates and they did not want that overhead (they should have fixed the app!)

fwiw,

Hein

I did not like it at that time, I don;t like it now. Best you can do is to submit a feature request to get an internal or external parameter to force change table entries for non-changing rows.

Re: How to force change table to update?

john_wang — Tue, 02 Jul 2024 04:13:21 GMT

Thank you so much @Heinvandenheuvel , glad to get your insights here!

@MoeE , please submit a FR if you need it.

Best Regards,

John.