Skip to main content

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.

Audit table structure
Column Name Type Description

task_name

varchar (128)

The name of the Qlik Replicate replication task.

change_seq

bigint

A monotonically increasing change sequencer. 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)

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 Replicate contains logic that maintains the monotonicity of the sequence number so modifying or adjusting the endpoint time may result in multiple changes appearing as if 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).

change_oper

varchar (1)

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

I: INSERT

D: DELETE

U: UPDATE

B: Before Image

stream_position

varchar (128)

The source CDC stream position.

schema_name

nvarchar (128)

The name of the source table schema.

table_name

nvarchar

The name of the source table.

operation

varchar (12)

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

  • INSERT
  • UPDATE
  • DELETE
  • BEFOREIMAGE

transaction_id

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

timestamp

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

change_record

Note:

The data is stored in JSON format.

nclob

The new data.

Note:

LOB columns with unlimited size are not supported in the change_record field. The other fields will be recorded but the LOB will have a NULL value.

bu_change_record

Note:

The data is stored in JSON format.

nclob

The data before the update.

Note:

LOB columns with unlimited size are not supported in the bu_change_record field. The other fields will be recorded but the LOB will have a NULL value.

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.