Difference Between AR_H_TIMESTAMP and AR_H_COMMIT_TIMESTAMP

[Jet]

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!

[dima_etkin · Accepted solution]

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.

[Jet]

Got it. Thank you very much!

[Nimmi]

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

[john_wang]

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.

[Nimmi]

TY @john_wang for the detailed explanation.