Change Tables

A monotonically increasing change sequencer that is common to all change tables of a task. The change sequence has the following format:

YYYYMMDDHHmmSShhxxxxxxxxxxxxxxxxxxx

Where:

• YYYY is the four-digit year (such as 2012)
• MM is the two-digit month (range from 01-12)

• DD is the two-digit day (range from 01-31)

• HH is the hour in the day (range from 00-23)
• mm is the minute in the hour (range from 00-59)
• SS is the second in the minute (range from 00-59)
• hh is the hundredth of the second (range from 00-99)
• xxxxxxxxxxxxxxxxxxx is a 19-digit, zero prefixed change number (global per task)

The time part usually refers to the commit time of the transaction that includes the change record. Qlik Talend Data Integration contains logic that maintains the monotonicity of the sequence number so modifying or adjusting the endpoint time may result in multiple changes to seem that they are within the same timestamp but with increasing change number.

The xxx...xxx is usually the internal change number from the data record except that for BEFORE-IMAGE records it is the same as the change number of the matching UPDATE record (for example, if the change number of BEFORE-IMAGE is 1000 and that of the UPDATE is 1001, then both have 1001). This allows a simple left-outer-join between the table and itself where on the left we scan until the point in time but filter out operation=before-image, and on the right we join on the same change_seq with the change_oper being 'B' .

The operation type. This can be one of the following:

• I: INSERT
• D: DELETE
• U: UPDATE
• B: Before Image

The change mask indicates which data columns in the change table are associated with columns that changed in the source table.

The bit position in the change mask is based on the column ordinal in the change table. This means that if there are 5 header columns, they occupy bits 0 to 4 and the first data column is bit 5 in the change mask.

The change mask is a binary column (a byte array) representing the change mask in little-endian order:

Byte 0 bit7 bit6 bit5 bit4 bit3 bit2 bit1 bit0

Byte 1 bit15 bit14 bit13 bit12 bit11 bit10 bit9 bit8

In this example, bit#N indicates that the change table column of ordinal N relates to a column that changed in the source table. If update mask is 11000 and the column ordinal is 3 the column did not change.

The following describes the bit semantics:

• For INSERT records, all the inserted columns have the associated bits set.
• For DELETE records only primary-key (or unique index) columns have the associated bits set. This allows an applier to construct a DELETE statement without having to find the primary key fields from another source.
• For BEFORE-IMAGE records, all bits are clear (the change mask can be empty).
• For UPDATE records, each column whose value changed between the BEFORE-IMAGE and the UPDATE will have the associated bit set.

For space and processing efficiency, the actual number of bytes stored in the change-mask can be null-trimmed. This means that trailing zeros do not need to be stored. The handling logic should take this into consideration.

The source CDC stream position.

The operation associated with the change record. It can be one of the following:

• INSERT
• UPDATE
• DELETE
• BEFOREIMAGE

The ID of the transaction that the change record belongs to.

The value is a hex-string of the 128-bit transaction ID.

The original change UTC timestamp (the value may be approximate).