topic Re: Difference Between AR_H_TIMESTAMP and AR_H_COMMIT_TIMESTAMP in Qlik Replicate

Difference Between AR_H_TIMESTAMP and AR_H_COMMIT_TIMESTAMP

Jet — Tue, 09 Jun 2020 11:07:43 GMT

Hi! May I know the difference between AR_H_TIMESTAMP and AR_H_COMMIT_TIMESTAMP? I can’t seem to grasp the description on the user guide that I got. Thank you!

Re: Difference Between AR_H_TIMESTAMP and AR_H_COMMIT_TIMESTAMP

dima_etkin — Wed, 10 Jun 2020 13:47:21 GMT

Hi,

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. Both are represented in UTC.

Replicate manual describes them as following:

AR_H_TIMESTAMP Change timestamp Current timestamp DATETIME

(The UTC timestamp on the Qlik Replicate Server.) Note The time is accurate to the nearest second.

AR_H_COMMIT_TIMESTAMP Commit timestamp Current timestamp DATETIME

(The UTC timestamp on the Qlik Replicate Server.)

The comment “The UTC timestamp on the Qlik Replicate Server” refers to the fact that some databases return the timestamp in local time, and Qlik Replicate converts that to UTC using the time zone defined on the Replicate Server.

Re: Difference Between AR_H_TIMESTAMP and AR_H_COMMIT_TIMESTAMP

Jet — Thu, 11 Jun 2020 13:38:58 GMT

Got it. Thank you very much!

Re: Difference Between AR_H_TIMESTAMP and AR_H_COMMIT_TIMESTAMP

Nimmi — Fri, 10 Dec 2021 17:02:21 GMT

TY Dima! I am struggling with the difference between - "when the change happened on the source" and he "when the change was committed on the source."

Re: Difference Between AR_H_TIMESTAMP and AR_H_COMMIT_TIMESTAMP

john_wang — Sat, 11 Dec 2021 04:11:49 GMT

Hello @Nimmi , @Jet , @dima_etkin ,

Please allow me to add extra explanation here. There are several different scenarios.

1. In general, if you are executing very small transaction under AUTO-COMMIT mode, the "transaction timestamp" equals to the "commit timestamp". For example you update a few rows only (eg 1 row affected) under AUTO-COMMIT mode;

2. If the DML is executed under explicit transaction mode, and there is some gap between UPDATE and COMMIT, then they are in different timestamp. For example you execute UPDATE at 11:15:11 , after 1 minute you submit the explicit COMMIT at 11:16:23, then there is 1 minute gap between the "transaction timestamp" and "commit timestamp":

The above sample is based on Oracle.

Certainly, sometimes the "transaction timestamp" and "commit timestamp" are different even the COMMIT is executed immediate after UPDATE, or even in AUTO-COMMIT mode. For example in a single 'big' transaction, a huge number of rows are affected, the transaction takes 10 minutes to complete, then all the changed rows own the same "commit timestamp" but different "transaction timestamp" in each change event.

Hope this helps.

Regards,

John.

Re: Difference Between AR_H_TIMESTAMP and AR_H_COMMIT_TIMESTAMP

Nimmi — Mon, 13 Dec 2021 12:54:07 GMT

TY @john_wang for the detailed explanation.