Same change sequence number for a large number of records?

[MoeyE]

Hi,

I have had a customer with an interesting issue. The source (Oracle) had a large number of updates which didn't change anything but in the target (Messaging platform) they received these large numbers of records and noticed that a large amount of the records had the exact same change sequence number AR_H_CHANGE_SEQ. 180+. 

I tried to recreate this in my environment by creating a transaction that updates 10 records at once but doesn't change any data. However, the change sequence numbers generated in the target were all different. How is it possible that the change sequence numbers are all the same?

Regards,

Mohammed

[john_wang]

Hello Mohammed, @MoeyE 

Thanks for the info. Would you please share the detailed steps (includes sample update SQL statements for example if these updates are in a single transaction or in auto-commit mode)? I'd like to understand further about the scenario.

Regards,

John.

[MoeyE]

Hi John,

Yes. I setup a task that goes from Oracle source to Kafka target. Both on prem. The task only has one table. Then I ran this update on my Oracle table, both in manual commit mode. I also tried running it in autocommit mode. Both had the same result.

BEGIN
update "Address" set "PostalCode" = 1234 where "AddressID" = 4;
update "Address" set "PostalCode" = 1234 where "AddressID" = 4;
update "Address" set "PostalCode" = 1234 where "AddressID" = 4;
update "Address" set "PostalCode" = 1234 where "AddressID" = 4;
update "Address" set "PostalCode" = 1234 where "AddressID" = 4;
update "Address" set "PostalCode" = 1234 where "AddressID" = 4;
update "Address" set "PostalCode" = 1234 where "AddressID" = 4;
update "Address" set "PostalCode" = 1234 where "AddressID" = 4;
update "Address" set "PostalCode" = 1234 where "AddressID" = 4;
update "Address" set "PostalCode" = 1234 where "AddressID" = 4;
END;

commit;

The result in the Kafka target was that all the changes have their own change sequence number. 

Regards,

Mohammed

[DesmondWOO]

Hi @MoeyE ,

AR_H_CHANGE_SEQ should be different. Replicate contains logic that maintains the monotonicity of the sequence number, the AR_H_CHANGE_SEQ should be different.

Could your customer tell us how to reproduce this issue?

Regards,
Desmond

[john_wang]

Hello @MoeyE , @DesmondWOO ,

Personally I do not think it's easy to reproduce it, it's worth a support ticket for deeper investigation.

thanks,
John.

[Heinvandenheuvel]

While in the topic subject you call AR_H_CHANGE_SEQ a number it is really more a string of digits than a number.

It will not fit in a 64 bit int. Ask the customer for some samples and see if truncation has occurred?

[MoeyE]

Hi Hein,

Good suggestion. I just checked but their change sequence number has not been truncated. Thanks everyone for the suggestions, I have indeed opened a support ticket and I will try to update this community post when the cause is found.

Regards,

Mohammed