Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
MoeyE
Partner - Creator III
Partner - Creator III

How to force change table to update?

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

Labels (2)
1 Solution

Accepted Solutions
john_wang
Support
Support

Hello @MoeyE ,

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

8 Replies
john_wang
Support
Support

Hello Mohammed, @MoeyE 

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
MoeyE
Partner - Creator III
Partner - Creator III
Author

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

john_wang
Support
Support

Thank you for the update @MoeyE , 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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
john_wang
Support
Support

Hello @MoeyE ,

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
MoeyE
Partner - Creator III
Partner - Creator III
Author

Hi John,

Thanks for the help, much appreciated.

Regards,

Mohammed

john_wang
Support
Support

Thank you for your great support! @MoeyE 

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Heinvandenheuvel
Specialist III
Specialist III

>> 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.

john_wang
Support
Support

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

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

Best Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!