topic Re: Replicate header__timestamp Clarification in Qlik Replicate

Replicate header__timestamp Clarification

NewbieQlik — Tue, 26 Apr 2022 19:02:24 GMT

I am replicating a table from Oracle source onto snowflake target where I have enabled the store changes. As part of replicate task I have added a global transformation to get source oracle record commit timestamp using $AR_H_COMMIT_TIMESTAMP. When I tried to process few CDC changes where I have an INSERT followed by "UPDATE" and when that happened $AR_H_COMMIT_TIMESTAMP shows same value for both insert and update and for some reason header__timestamp shows 2022-04-20 20:30:04.000 for insert (EST time) and 2022-04-20 20:30:05.000 for update.

My understanding was replicate internally uses $AR_H_COMMIT_TIMESTAMP to populate header__timestamp column in __ct table, so in that case I should see the same value for both insert and update. Is this an expected behavior?

header__operation/header__timestamp/Source_Record_Commit_TS

INSERT/2022-04-20 20:30:04.000/2022-04-20 16:30:05.000
UPDATE/2022-04-20 20:30:05.000/2022-04-20 16:30:05.000

Re: Replicate header__timestamp Clarification

shashi_holla — Tue, 26 Apr 2022 19:23:31 GMT

AR_H_COMMIT_TIMESTAMP holds the datetime value when the record was committed at the source. Since insert and update were in succession and not at the same time, it's normal we see that one second difference between the two. if you notice the value for Source_Record_Commit_TS it's consistent with when the record was last committed. Hope this makes sense.

Thank you,

Re: Replicate header__timestamp Clarification

Heinvandenheuvel — Wed, 27 Apr 2022 19:36:30 GMT

Hello @NewbieQlik (why hide, why not at least provide a first name to address you properly !?)

You did not explicitly indicated whether the Insert and Update were part of a single transaction committed at the same time. Judging by the example they were.

>> My understanding was replicate internally uses $AR_H_COMMIT_TIMESTAMP to populate header__timestamp column in __ct table,

What led you to believe that? The documentation explicitly indicates: "[header__]timestamp - The original change UTC timestamp"

By contrast for "[header__]change_seq" it indicates: "The time part usually refers to the commit time of the transaction that includes the change record."

>> I have added a global transformation to get source oracle record commit timestamp using $AR_H_COMMIT_TIMESTAMP.

That may well be the best approach, based on the exact application requirements.

Hein.

Re: Replicate header__timestamp Clarification

NewbieQlik — Thu, 12 May 2022 01:48:59 GMT

Thanks for the clarification