Replication Latency metrics

[Learner1]

Hello Team,

we have a qlik task replicating multiple DB2 tables to azureSQLDB tables. We have added 2 transformation columns  in qlik replicate at task level to derive source vs target commit timestamps to each row, something as described : Using Global Rules to get source and target commit... - Qlik Community - 2124726

Now, from the computed columns it seems that the replication latency is in milliseconds, but effectively stale data is visible in target tables even after 1-2 seconds. It seems that azure sqldb takes some time to actually complete the commit. So, I wanted to understand if qlik already computes the target commit timestamp before the actual commit applied and then which rule should be applied to get more realistic metric.

Thanks for your help.

[SachinB · Accepted solution]

Hello @Learner1 ,

Thank you for reaching out to the Qlik community!

AR_H_TIMESTAMP is the time associated with the change record and indicates when the change happened on the source. The AR_H_COMMIT_TIMESTAMP indicates when the change was committed on the source.

For reference, see Discussion: Difference Between AR_H_TIMESTAMP and AR_H_COMMIT_TIMESTAMP

If you would like to capture an instance when Qlik Replicate is making changes to target you can add a column via global transformation or at the table level:

1. Column Datatype = DATETIME(6)
2. Computation expression = datetime('now','localtime')

The expression, datetime('now','localtime') will capture the timestamp of records being inserted, deleted or updated in Replicate. We currently do not capture the time on the target system.

Link : https://community.qlik.com/t5/Official-Support-Articles/How-to-calculate-time-of-changes-within-Qlik...

Regards,

Sachin B

[Heinvandenheuvel]

There is no such thing as "target commit" timestamp. If there was, Replicate would have to calculate it before it was known to get it into the target. A conundrum! Best it could possibly do is to use the delay from prior commits, but it doesn't. 

Please carefully read the excellent reply by @SachinB 

Adding a localtime timestamp from the Replicate server during processing gives a good approximation as mostly, but not always, the bigger delays are during input gathering by design or by circumstance.

The very best you can do is to add a column on a frequently updated target table which defaults to the current time on the target DB server. Combined with the source commit time that represents the actual latency. There is really no need to do this for all tables. Just pick a busy table and it will give a solid indication for all tables.

Of course if you think a particular table is problematic, you could/should add such target column there as well.

Hein.

[Learner1]

Thanks a lot Both.