What is the difference between AR_H_TIMESTAMP, AR_H_COMMIT_TIMESTAMP and AR_H_DB_COMMIT_TIMESTAMP column values?

[khchoy]

What is the difference between AR_H_TIMESTAMP, AR_H_COMMIT_TIMESTAMP and AR_H_DB_COMMIT_TIMESTAMP column values? Is AR_H_DB_COMMIT_TIMESTAMP the only way to find out the commit time of the source? When using AR_H_COMMIT_TIMESTAMP, there are cases where the time is later than AR_H_TIMESTAMP. Is there any way to solve this?

[john_wang · Accepted solution]

Hello @khchoy ,

Thanks for reaching out to Qlik Community!

The columns detailed description can be found in User Guide. And I'm adding 

 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.

John.

[khchoy]

Hi John,

Thanks for answer. 

In your answer, "transaction timestamp" means "AR_H_TIMESTAMP" and "commit timestamp" means "AR_H_COMMIT_TIMESTAMP", right? Then, does it mean that "transaction timestamp" is always earlier or equal to the time value of "commit timestamp"?

Regards,

KwangHo

[john_wang]

Hello KwangHo, @khchoy 

Definitely you are correct.

If you hit exceptions, please open support ticket, or provide the reproduction steps, our support team would be more than happy to help you on it.

Best Regards,

John.