Setting Change Processing parameters

The Change Processing tab lets you define change processing settings for the source database. Normally, Replicate scans a database’s transaction logs for changes and then applies those changes to the target database. However, this method of change processing is not possible with Data Warehouse endpoint types since these endpoints do not generate transaction logs.

Prerequisites

Before you can define the settings in the Change Processing tab, you need to ensure that at least one special "Context" column exists in your source database tables. Context column(s) are basically columns in a table that enable Replicate to determine whether the data has changed. You can add Context columns specifically for the purpose of change processing (either using a script or manually) or you can use existing columns that contain suitable "Context" data.

Example

In the example below, the Context column cf has been added to the table. The cf column contains TIMESTAMPs that enable Replicate to determine whether a change occurred (by comparing the current TIMESTAMP with the TIMESTAMP stored in its repository).

By default, all changes are assumed to be INSERTs. If UPDATE and DELETE operations are also performed on the source tables, you can write an UPDATE and/or DELETE expression (described below) that will enable Replicate to identify the operation type.

Limitations

The following limitations apply when Change Processing is enabled:

• The "Start from timestamp" run option is not supported. For more information, see Advanced run options.
• If one of the Context columns is part of the Primary Key or Unique Index, then UPDATE and DELETE operations are not supported.
• Context columns cannot be LOB columns
• DDLs are not supported

• When inserting a record and then updating the same record, the task error handling settings should be set as follows:

  1. Open the <Task Name> Settings dialog box.
  2. Select the Error Handling|Apply Conflicts tab.
  3. Set a task-specific Apply Conflicts policy as described in Error Handling settings.
  4. From the No record found for applying an update drop-down list, select INSERT the missing target record.

  For more information on error handling, see Error handling.

Configuring Change Processing settings

Perform the following steps to configure change processing settings:

1. Select the endpoint's Change Processing tab.

2. In the Columns field, specify the names of the Context columns. The column names are case-sensitive and must be separated by commas.

   Example:

   context1,context2

3. Choose the sorting order of the Context columns as appropriate (Ascending or Descending). Note that if the order you select is not the same as the actual sorting order, an error will occur.
4. In the Check for changes every field, specify how often to check for changes.

5. Enter expressions that Replicate will use to identify UPDATE and DELETE operations. If you do not enter any expressions or if no match is found for an expression, any row whose context is higher (if the sorting order is Ascending) or lower (if the sorting order is Descending) than the previous context value will be considered an INSERT.

   Expressions must be written in the native syntax of the source database. All examples in this section are written using PostgreSQL syntax.

   • Update expression - Enter an expression for identifying UPDATE operations.

     Example (based on the example in Prerequisites above):

     case when oper='U' then 1 else 0 end

     Selecting the UPDATE the existing target record option in the Apply Conflicts tab, eliminates the need to provide an UPDATE expression.

   • Delete expression - Enter an expression for identifying DELETE operations.

     Example (based on the example in Prerequisites above):

     case when oper='D' then 1 else 0 end

     In addition to the DELETE expression, DELETE operations should be carried out as "Soft" deletes. This means that the row is not actually deleted from the table, but rather, marked as "deleted".

6. Select Override connection string parameters to append the connection string with parameters that are not exposed in the UI. As such parameters are normally not required, they should only be used after consulting with Qlik Support.

Additional settings and summary

In the Change Processing tab, you can set also set internal parameters, set more options, and view a summary of your settings.

Internal parameters

Internal parameters are parameters that are not exposed in the UI. You should only use them if instructed by Qlik Support.

To add internal Qlik Replicate parameters:

1. Click the Internal Parameters link.

   The Internal Parameters dialog box opens.

2. In the edit box, type the name of the parameter you need to add and then click it.
3. The parameter is added to the table below the search box with its default value.
4. Change the default value as required.
5. To reset the parameter value to its default, click the "Restore default value" icon at the end of the row.

More options

These options are not exposed in the UI as they are only relevant to specific versions or environments. Consequently, do not set these options unless explicitly instructed to do so by Qlik Support or product documentation.

To set an option, simply copy the option into the Add feature name field and click Add. Then set the value or enable the option according to the instructions you received.

Settings summary

You can view a summary of your settings by clicking the Setting Summary link. This is useful if you need to send a summary of your settings to Qlik Support.