Understanding AR_H_CHANGE_SEQ, Messaging endpoints, and Qlik Replicate's behaviour

[MoeyE]

Hi,

I am trying to understand the documentation for change sequence number. From my understanding, here's what should happen. I have a task that goes directly from Oracle to Kafka. If I insert a new record then I update this record directly after, then the change sequence number will be the same for both records when they appear in Kafka. 

https://help.qlik.com/en-US/replicate/November2023/Content/Global_Common/Content/SharedEMReplicate/C... 

The xxx...xxx is usually the internal change number from the data record except that for BEFORE-IMAGE records it is the same as the change number of the matching UPDATE record (for example, if the change number of BEFORE-IMAGE is 1000 and that of the UPDATE is 1001, then both have 1001). This allows a simple left-outer-join between the table and itself where on the left we scan until the point in time but filter out operation=before-image, and on the right we join on the same change_seq with the change_oper being 'B' .

However this is the behaviour I noticed when experimenting:

1. I insert my new record into the Oracle table. I note the offset and the change sequence number

2. I update the record. (PostalCode=5678). Notice that the sequence number of the update seen in Kafka is different from the change sequence number for the before record. It should be the same right?

3. Now here's the interesting thing, 5 minutes later I got a bunch of new records into Kafka but I was not doing any changes in the source table, neither was anyone else. Also what is interesting is that the change sequence number is the same from when I first updated my record . This looks like it verifies the behaviour in the documentation but not how I expected it to. Also every 5 minutes, records comes in again with the same change sequence number. Look at the data. Notice that the same change sequence number appears and that every 5 minutes it is reinserted. Is this normal behaviour? 

If I download the latest records withing the last 30 mins or so and search for one sequence number, it appears 12 times

I looked at the Replicate console at 10:32 AM, the last update was 30 minutes ago so I'm not sure why I keep getting messages in Kafka with the same change sequence numbers. Looks like the changes are in fact being replayed. 

I did this experimentation because a customer was actually getting the same issue and I couldn't recreate it till now. I checked the logs and no errors or issues.

Regards,

Mohammed

[MoeyE]

Hi,

Just an update. I increased target_apply logging to verbose and found that at the time that the updates come through, there is some message related to metadata. Perhaps this is likely what is happening. Metadata is being refreshed every 5 minutes, resulting in messages being sent to the target for the updated records.

Regards,

Mohammed

[DesmondWOO]

Hi @MoeyE ,

Thank you for reaching out to the Qlik Community.

To better address your issue, I recommend that you create a support ticket so we can gather more information and investigate further.

Regards,
Desmond 

[Kunal9889]

Hello Mohammed, hope you are doing well! We are also finding the similar behaviour with few tables. Could you please let me know whether you have raised a case with qlik? if so what is the outcome of it.

[MoeyE]

Hi Kunal,

In our case it was not resolved. I believe after an upgrade the issue disappeared. Can you describe your issue? Are you seeing the same change sequence number over and over? Which version and build of Replicate are you on? what are your source and target?

Regards,

Mohammed

[DesmondWOO]

Hi @MoeyE ,

Did you create the support ticket? If so, could you please provide the ticket number? I’d like to check if I can find more information regarding this issue.

Regards,
Desmond

[MoeyE]

Hi Desmond,

00291658 and 00175350.

Regards,

Mohammed

[Kunal9889]

Hello Mohammed, thanks for you response.

Yes we are also seeing the same sequence number for multiple records. We are on 2022.11 about to upgrade to 2023.11 on 9th nov, Source is Oracle 19c and target is hadoop kafka 2x.

Regards

[DesmondWOO]

Hi @MoeyE ,

I reviewed two cases from the same customer. Unfortunately, the customer was unable to provide detailed information for further investigation. At the time, they were using Replicate v2023.11.0.282.

Hi @Kunal9889 ,
If the problem persists after the upgrade, I recommend submitting a support ticket.

Regards,
Desmond

[john_wang]

Hello @MoeyE , @Kunal9889 ,

Replicate is producing messages asynchronously to Kafka. This means that we send batches of messages and later receive the delivery status (success/error). If there are exceptions Qlik Replicate will try to recovery, After recovery, Replicate should continue from the earliest message that failed delivery. I'm assuming this is the reason why you get duplicate event messages.

I'd like to suggest setting source_capture/target_apply to Verbose, recreate the issue then provide the Diag Packages and decrypted log files for analysis.

Regards,

John.