topic Re: Why would the TransactionId in the header not be unique when replicating data from an Oracle database? in Qlik Replicate

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

Laura_NJ — Tue, 24 Aug 2021 16:44:48 GMT

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?

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

john_wang — Wed, 25 Aug 2021 01:49:03 GMT

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/Customize%20Tasks/tasks_headColumns.htm

Hope this helps.

Regards,

John.

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

Laura_NJ — Wed, 25 Aug 2021 17:57:52 GMT

@john_wang Thank you for the explanation. This helps.

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

AndreasFenske — Mon, 31 Jan 2022 14:26:21 GMT

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_change_tables.htm

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

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

Anonymous — Tue, 01 Feb 2022 10:42:00 GMT

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

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

avidary_qlik — Thu, 10 Feb 2022 07:53:19 GMT

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