Using an Audit table
When defining a replication task, you can choose to store changes to all tables in a single Audit table (located in the target endpoint). The changes can then be pushed from a single stream to any queue (JMS for example).
The following table describes the structure of the Audit table.
Column Name | Type | Description |
---|---|---|
|
varchar (128) |
The name of the replication task. |
|
bigint |
A monotonically increasing change sequencer that is common to all change tables of a task. The change sequence has the following format: YYYYMMDDHHmmSShhxxxxxxxxxxxxxxxxxxx Where:
The time part usually refers to the commit time of the transaction that includes the change record. Replicate 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 |
|
varchar (1) |
The operation type. This can be one of the following:
|
|
varchar (128) |
The source CDC stream position. |
|
nvarchar (128) |
The name of the source table schema. |
|
nvarchar |
The name of the source table. |
|
varchar (12) |
The operation associated with the change record. It can be one of the following:
|
|
varchar (32) |
The ID of the transaction that the change record belongs to. The value is a hex-string of the 128-bit transaction ID. |
|
timestamp |
The original change timestamp (the value may be approximate). |
Information note
The data is stored in JSON format. |
nclob |
The new data. Information note
LOB columns with unlimited size are not supported in the |
Information note
The data is stored in JSON format. |
nclob |
The data before the update. Information note
LOB columns with unlimited size are not supported in the |
Partition start | Partition name, partition start time (UTC), and estimated partition end time (UTC) | |
Partition end | Partition name, partition start time (UTC), partition end time (UTC), and a list of changed tables (identified by table name and schema name). |
For more information on storing changes in an audit table, see Store Changes Settings.