Why would the TransactionId in the header not be unique when replicating data from an Oracle database?

[Laura_NJ]

When replicating data from Oracle can we expect the transactionId to be unique.   We discovered 2 unrelated transactions that occurred on different days but had the same  transactionId.  What would cause this?

[john_wang · Accepted solution]

Hello @Laura_NJ ,

Oracle xid consists of three parts. The full xid is unique for the entire instance existence. However in Replicate, We use only two parts. The short one is unique for the current time, and this is what is enough for Replicate purpose.

So far transaction_id in header part is not unique. The information can be found in User Guide:

The transaction ID is not guaranteed to be
unique and therefore should not be used to
create reports or any other operation that
relies upon its uniqueness.

https://help.qlik.com/en-US/replicate/May2021/Content/Global_Common/Content/SharedEMReplicate/Custom...

Hope this helps.

Regards,

John.

[Laura_NJ]

@john_wang Thank you for the explanation.  This helps.

[AndreasFenske]

Hi @john_wang ,

we have the same problem. How would it be possible to identify a transaction uniquely with the Qlik CDC meta data?

We thought about a concatenation of "transactionId" + "LEFT(changeSequence, 16)" (first 16 places). The change sequence looks like "20220121103723620000000000132464897" and the first 16 places represent a timestamp field in the Format YYYYMMDDHHmmSShh:

https://help.qlik.com/en-US/replicate/November2021/Content/Replicate/Main/Change%20Tables/read_chang... 

Would this concatenation help us to identifiy the unique transactions?

Best Regards,
Andreas

PS: The transactionId + timestamp wouldn't help, since the "timestamp" value can differ within a transaction

[Anonymous]

Hi Andreas,
Your suggestion to use a concatenation of "transactionId" + "LEFT(changeSequence, 16)"

is very good as it will create a Unique Non-Reproducible ID (UNRID).
This will help you identify a unique transaction when replicating from Oracle to Kafka.

Thank you
Avidar

[avidary_qlik]

Hi Andreas,

 in some powerful OS eg AS400, we may get several thouthands changes in one millisecond

For more precise Unique Non-Reproducible ID (UNRID)

you can use the concatenation of "transactionId" and the full  "changeSequence,"

 "transactionId" + "changeSequence,"

This will create a more robust Unique Non-Reproducible ID (UNRID).
and this will create unique transactions when replicating from Oracle to Kafka.

Thank you
Avidar