D About the Commit Sequence Number

This appendix contains information about the Oracle GoldenGate Commit Sequence Number (CSN).

When working with Oracle GoldenGate, you might need to refer to a Commit Sequence Number, or CSN. The CSN can be required to position Extract in the transaction stream, to reposition Replicat in the trail, or for other purposes. It is returned by some conversion functions and is included in reports and certain GGSCI output.

A CSN is a monotonically increasing identifier generated by Oracle GoldenGate that uniquely identifies a point in time when a transaction commits to the database. It purpose is to ensure transactional consistency and data integrity as transactions are replicated from source to target.

Each kind of database management system generates some kind of unique serial number of its own at the completion of each transaction, which uniquely identifies the commit of that transaction. For example, the Oracle RDBMS generates a System Change Number, which is a monotonically increasing sequence number assigned to every event by Oracle RDBMS. The CSN captures this same identifying information and represents it internally as a series of bytes, but the CSN is processed in a platform-independent manner. A comparison of any two CSN numbers, each of which is bound to a transaction-commit record in the same log stream, reliably indicates the order in which the two transactions completed.

The CSN is cross-checked with the transaction ID (displayed as XID in Oracle GoldenGate informational output). The XID-CSN combination uniquely identifies a transaction even in cases where there are multiple transactions that commit at the same time, and thus have the same CSN. For example, this can happen in an Oracle RAC environment, where there is parallelism and high transaction concurrency.

The CSN value is stored as a token in any trail record that identifies the commit of a transaction. This value can be retrieved with the @GETENV column conversion function and viewed with the Logdump utility.

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, such as the Sybase CSN.

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

LSN or LRI

Where:

  • For version 9.7 and earlier, LSN is the variable-length, decimal-based DB2 log sequence number.

    Example:

    1234567890
    
  • 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/MX

sequence_number|RBA

Where:

  • sequence_number is the 6-digit decimal NonStop TMF audit trail sequence number padded with leading zeroes.

  • RBA is the 10-digit decimal relative byte address within that file, padded with leading zeroes.

Together these specify the location in the TMF Master Audit Trail (MAT).

Example:

000042|0000068242

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

Sybase

time_high.time_low.page.row

Where:

  • time_high and time_low represent an instance ID for the log page. It is stored in the header of each database log page. time_high is 2-bytes and time_lowis 4-bytes, each padded with leading zeroes.

  • page is the database logical page number, padded with zeroes.

  • row is the row number, padded with zeroes.

Taken together, these components represent a unique location in the log stream. The valid range of a 2-byte integer for a timestamp-high is 0 - 65535. For a 4-byte integer for a timestamp-low, it is: 0 - 4294967295.

Example:

00001.0000067330.0000013478.00026

Teradata

sequence_ID

Where:

  • sequence_ID is a generic fixed-length printable sequence ID.

Example:

0x0800000000000000D700000021

TimesTen

There is no CSN for TimesTen, because extraction (capture) is not supported by Oracle GoldenGate for this database.