Working with Change Tables

In addition to replicating changes from source endpoint tables to corresponding tables in a target endpoint, Qlik Replicate can also replicate changes to corresponding Change Tables in the target endpoint. This process occurs simultaneously when applying changes to the target tables. Qlik Replicate lets you determine whether to replicate the changes to the target only, store changes in the Change Tables, or both. See Using the Change Table model below for more information.

The Change Tables have the same names as the tables that are being replicated, however a suffix is added to each table name. By default the suffix is __ct, however you can change the suffix name in the Qlik Replicate Console. For more information on changing the suffix added to the Change Table names, see Store Changes Settings.

In addition to the selected columns from the source table, the Change Table also includes special header columns that provide more information on the change the row represents such as the operation, the transaction and the timestamp. This lets you use SQL Query Language to carry out various analysis of the change events, such as fraud detection, trend analysis, triggering of business processes, and Disaster Recovery. For more information about reading the Change Tables, see Change Tables.

Handling truncate operations

TRUNCATE operations will not truncate the Change Table. Instead, an additional record will be added to the table with operation=TRUNCATE.This also means that, when replicating to a Hadoop target, the HDFS files corresponding to the Change Table will not be deleted.

Regarding the actual target table, if both the Apply Changes and Store Changes replication options are enabled, the target table will be truncated. This also means that, when replicating to a Hadoop target, the HDFS files corresponding to the Change Table will also be deleted.

To apply TRUNCATE operations to both the Change Table and the Target Table (for sources that support TRUNCATE):

1. In the task settings' Store Changes Settings tab, make sure that Apply to Change Table (the default) is selected from the DDL options drop-down list.
2. In the task settings' Apply Changes Settings tab, make sure that TRUNCATE target table (the default) is selected from the When source table is truncated drop-down list.

 

Using the Change Table model

When you work with Change Tables you can determine whether to store the changes in the Change Tables, apply the changes to the target tables, or both store and apply the changes. You determine this when you define the replication task. For more information on this setting, see Store Changes Settings.

In cases where you are both applying and storing the changes, the following is true:

• The target and Change Tables must be in the same endpoint, although they can have different schemas. For example, the Change Tables will contain the metadata headers.

  For further details about changing the schema, see Rename Change Table schema .

• Changes applied to the Change Table will be handled exactly the same as the changes performed in the corresponding transaction in the source database. Therefore, when using Transactional apply mode or Batch optimized apply mode with the Preserve transaction consistency option selected, the changes will be processed as a single transaction.

  The exception to this is when an error is encountered and Replicate switches to "one-by-one" apply mode in order to determine which of the Change operations is responsible for the error.

• The same data columns are both applied and stored with the exception of the change header columns, which are only added to the stored Change Tables.