1. Administering Oracle GoldenGate
  2. Using the Commit Sequence Number

D Using the Commit Sequence Number

This appendix contains information about using the Oracle GoldenGate Commit Sequence Number (CSN) with Oracle and non-Oracle databases.

All database platforms except Oracle, DB2 LUW, and DB2 z/OS have fixed-length CSNs, which are padded with leading zeroes as required to fill the fixed length. CSNs that contain multiple fields can be padded within each field. For more information on CSN, see Overview of CSN in Understanding Oracle GoldenGate

MySQL does not create a transaction ID as part of its event data, so Oracle GoldenGate considers a unique transaction identifier to be a combination of the following:

  • the log file number of the log file that contains the START TRANSACTION record for the transaction that is being identified

  • the record offset of that record

Table D-1 Oracle GoldenGate CSN Values Per Database

Database CSN Value

DB2 for i

sequence_number

Where:

  • sequence_number is the fixed-length, 20 digit, decimal-based DB2 for i system sequence number.

Example:

12345678901234567890

DB2 LUW

LRI

Where:

For version 10.1 and later, LRI is a period-separated pair of numbers for the DB2 log record identifier.

Example:

123455.34645

DB2 z/OS

 
RBA

where:

  • RBA is the 6-byte relative byte address of the commit record within the transaction log.

Example:

1274565892

MySQL

 
LogNum:LogPosition

Where:

  • LogNum is the the name of the log file that contains the START TRANSACTION record for the transaction that is being identified.

  • LogPosition is the event offset value of that record. Event offset values are stored in the record header section of a log record.

For example, if the log number is 12 and the log position is 121, the CSN is:

000012:000000000000121

Oracle

 
system_change_number

Where:

  • system_change number is the Oracle SCN value.

Example:

6488359

SQL Server

Can be any of these, depending on how the database returns it:

  • Colon separated hex string (8:8:4) padded with leading zeroes and 0X prefix

  • Colon separated decimal string (10:10:5) padded with leading zeroes

  • Colon separated hex string with 0X prefix and without leading zeroes

  • Colon separated decimal string without leading zeroes

  • Decimal string

Where:

  • The first value is the virtual log file number, the second is the segment number within the virtual log, and the third is the entry number.

Examples:

0X00000d7e:0000036b:01bd
0000003454:0000000875:00445
0Xd7e:36b:1bd
3454:875:445
3454000000087500445