topic Re: Why does the change_seq not reflect the commit timestamp all the time? in Qlik Replicate

Why does the change_seq not reflect the commit timestamp all the time?

MoeE — Mon, 18 Aug 2025 23:56:02 GMT

Hi,

Edit:

Answer - So change_seq does indeed reflect the commit timestamp. I made a mistake during my testing as I used DBeaver(3rd party query tool) on an Oracle database. DBeaver automatically commits changes as opposed to Oracle SQL Developer, which created the impression of the change_seq using the change time rather than the commit time.

Original post:

I did some testing on the Audit table. On my source Oracle table I did the following:

1. I updated a record without committing it at 14:26:44

2. I committed the update at 14:27:21

3. I inspected the Audit table and the "change_seq" column showed 14:26:44.

I had the misconception that the change_seq column always showed the commit timestamp so I was confused as to why it showed the change timestamp. That was until I reread the documentation which said "The time part usually refers to the commit time of the transaction that includes the change record".

The keyword here is "usually". Can someone please let me know why sometimes it reflects the commit timestamp while at other times, it reflects the change timestamp (the timestamp at which the transaction to change the record was executed but not committed)

Help with this is appreciated. Thank you.

Kind regards,

Mohammed

Re: Why does the change_seq not reflect the commit timestamp all the time?

DesmondWOO — Tue, 12 Aug 2025 11:52:17 GMT

Hi @MoeE ,

I conducted a test from the Oracle to the SQL Server:

Insert at 19:38:23
Insert at 19:38:44
Commit at 19:39:00

The content of change_record (I inserted TS using CURRENT_TIMESTAMP)
{ "data" : [{ "ID" : "13"} ,{ "C1" : "DES"} ,{ "TS" : "2025-08-12 19:38:23.740000"} ] }
{ "data" : [{ "ID" : "14"} ,{ "C1" : "DES2"} ,{ "TS" : "2025-08-12 19:38:44.925000"} ] }

The change seq of insert records are
20250812113901000000000000000000005
20250812113901000000000000000000009

In my case, the change_seq was formed by commit timestamp. Could you verify again? My Qlik Replicate version is v2024.11.0.444.

Regards,
Desmond

Re: Why does the change_seq not reflect the commit timestamp all the time?

MoeE — Tue, 12 Aug 2025 23:19:04 GMT

Hi Desmond,

My Qlik Replicate version is v2024.11.0.273.

I just tried again with the same task.

insert 1 at 9:04:26

insert 2 at 9:05:33

commit 9:06:05

Resultant timestamp in the change_seq column:

Looks like again I received the change time rather than the commit* time. Are we meant to receive the commit time? If not, what are the scenarios that would dictate whether I would receive the change time vs the commit time? Thanks.

Regards,

Mohammed

Re: Why does the change_seq not reflect the commit timestamp all the time?

Heinvandenheuvel — Sat, 16 Aug 2025 03:00:22 GMT

I suspect the 'usual' refers to not all source databases behaving the same.

It is my recollection (could be wrong, and not going to try ;-), that specifically SQLserver does NOT have an actual time for a change, but 'just' an LSN. There is a timestamp on COMMITs. Replicate uses those (even from 'other' transactions) to approximate the change times in SQL server.

Maybe, just maybe, Desmonds example used an otherwise idle SQL server as source despite him indicating Oracle???

Google for : sql server cdc lsn timestamp precision

Hein

Re: Why does the change_seq not reflect the commit timestamp all the time?

MoeE — Mon, 18 Aug 2025 01:20:42 GMT

Hi Hein,

Thanks for your reply. It appears that both Desmond and I tested this using an Oracle source and got two different results. I'm on Oracle 19c. Is there anyone that would definitely know the reason for this behaviour? Perhaps @john_wang ?

@DesmondWOO, if you are able, can you please confirm whether your test occurred from an Oracle source as suggested by Hein? Thanks for your assistance, it is appreciated.

Thank you guys.

Regards,

Mohammed

Re: Why does the change_seq not reflect the commit timestamp all the time?

MoeE — Mon, 18 Aug 2025 02:24:40 GMT

Hi @DesmondWOO, @Heinvandenheuvel, @john_wang,

I've tested again and I have some interesting results. To summarise what I found - It looks like the change_seq column's timestamp depends on which machine the update query was run on. I'm confused as to why this makes a difference though.

I tested running an update, waiting a bit, then running a commit. I did this using 2 query tools on 2 different machines. I tested using 2 separate tasks each with their own distinct table.

Test 1 - Using Oracle SQL Developer on the Oracle server itself

I ran an update transaction at 12:01:05 PM without committing it.
I waited a bit, then ran the commit statement at 12:01:43 PM
I checked the audit table on the SQL Server target machine and the results were:

change_seq - 12:01:43 PM

timestamp - 12:01:05

In this scenario, the change_seq column has the commit timestamp.

Test 2 - Using DBeaver (3rd party query tool) from another machine in the same network

For this test, I connected to the Oracle database from another machine. It looks like when I do this, the change_seq value is always the change time rather than the commit time.

I ran the update statement without committing it at 11:52:13 PM
I ran the commit statement at 11:52:44 PM
I checked the results on the audit table

Change_seq - 11:52:13 PM
Timestamp - 11:52:13 PM

I ran this on 2 different tasks and got the same result. This is an interesting result. When the database is accessed from a query tool on another machine, and the statement is run from there, the result is that the timestamps in both the change_seq and timestamp columns represent the time at which the change was made. i.e, the change_seq column doesn’t reflect the time it was committed, but the time at which the change occurred

When the update and commit were run using a query tool on the Oracle server itself, the change_seq column represented the time at which the transaction was committed, while the timestamp column represented the time at which the change occurred.

Does anyone know why this is?

Regards,

Mohammed

Re: Why does the change_seq not reflect the commit timestamp all the time?

DesmondWOO — Mon, 18 Aug 2025 03:05:56 GMT

Hi @MoeE ,

Please check the DBeaver settings. By default, it is Auto-Commit.

Regards,
Desmond

Re: Why does the change_seq not reflect the commit timestamp all the time?

MoeE — Mon, 18 Aug 2025 05:59:12 GMT

Hi Desmond,

Yep, looks like you're right. Apologies for any confusion.

Regards,

Mohammed

Re: Why does the change_seq not reflect the commit timestamp all the time?

DesmondWOO — Mon, 18 Aug 2025 06:28:29 GMT

Hi @MoeE ,

All good! Let me know if you need anything else.

Regards,
Desmond

Re: Why does the change_seq not reflect the commit timestamp all the time?

MoeE — Tue, 19 Aug 2025 22:57:20 GMT

Hi Desmond,

Thanks for the assistance so far. This just leaves me with one question - Are you aware of why the documentation says "The time part usually refers to the commit time of the transaction that includes the change record" ?

Is there a time where it doesn't? or is the usually part because the time itself is obtained from the Qlik Replicate server machine where there time can differ slightly from the source machine?

Thanks.

Regards,

Mohammed