Data replication task settings

You can configure settings for the data replication task.

• Open the replication task and click Settings in the toolbar.

The Settings: <Task-Name> dialog opens. The available settings are described below.

General

In this tab, you can change the replication mode, the staging settings (when available), and the mode for applying changes to the target.

Replication mode

The following replication modes are available:

• Full load: Loads the data from the selected source tables to the target platform and creates the target tables if necessary. The full load occurs automatically when the task is started, but can also be performed manually should the need arise.

• Apply changes: Keeps the target tables up-to-date with any changes made to the source tables.

• Store changes: Stores the changes to the source tables in Change Tables (one per source table).

  For more information, see Store changes.

When working with Data Movement gateway, changes are captured from the source in near real-time. When working without Data Movement gateway (for example, with a Qlik Talend Cloud Starter subscription or when selecting None), changes are captured according to the scheduler settings. For more information, see Scheduling tasks when working without Data Movement gateway.

If you select Store changes or Apply changes and your source data contains tables that do not support CDC, or views, two data pipelines will be created. One pipeline for tables that support CDC, and another pipeline for all other tables and views that support Full load only.

Connecting to a staging area

When replicating to the data warehouses listed below, you need to set a staging area. Data is processed and prepared in the staging area before being transferred to the warehouse.

• Amazon Redshift
• Microsoft Fabric
• Databricks

Either select an existing staging area or click Create new to define a new staging area and follow the instructions in Connecting to cloud storage.

To edit the connection settings, click Edit. To test the connection (recommended), click Test connection.

For information on which staging areas are supported with which data warehouses, see the Supported as a staging area column inTarget platform use cases and supported versions.

Apply changes mode

Changes are applied to the target tables using one of the following methods:

• Batch optimized: This is the default. When this option is selected, changes are applied in batches. A preprocessing action occurs to group the transactions into batches in the most efficient way.
• Transactional: Select this option to apply each transaction individually, in the order it is committed. In this case, strict referential integrity is ensured for all tables.

Data uploading

Maximum file size

The maximum size a file can reach before it is closed. Smaller files might be uploaded faster (depending on the network) and improve performance when used in conjunction with the parallel execution option. However, it is generally considered bad practice to clutter the database with small files.

Metadata

Basic

• Target table schema: The schema on the target to which the source tables will be replicated if you do not want to use the source table schema (or if there is no schema in the source database).

  Information noteWhen replicating to an Oracle target, the default target table schema is "system". Note also that if you leave this field empty (in which case the source schema will be used), you must make sure that the source schema already exists on the target. Otherwise, the task will fail.
  Information noteThe maximum permitted length for the schema name is 128 characters.

LOB settings

The available LOB settings differ according to the selected replication target. As data warehouse targets do not support unlimited LOB column sizes, this option will not be available when replicating to a data warehouse.

LOB settings for targets that are not data warehouses

• Do not include LOB columns: Select this if you do not want source LOB columns to be replicated.

• Limit LOB column size to (KB): This is the default. Select this option if you only need to replicate small LOBs or if the target platform does not support unlimited LOB size. The maximum permitted value for this field is 102400 KB (100 MB).

  When replicating small LOBs, this option is more efficient than the Do not limit LOB column size option since the LOBs are replicated "inline" as opposed to via "lookup" from the source. During Change Processing, small LOBs are usually replicated via "lookup" from the source.

  The limit applies to the number of LOB bytes that is read from the source endpoint. For BLOB columns, the target BLOB size will be exactly according to the specified limit. For CLOB and NCLOB columns, the target LOB size could differ from the specified limit if the source and target LOB do not have the same character set. In this case, the data task will perform character set conversion, which might result in a discrepancy between the source and target LOB sizes.

  As the value of the Limit LOB size to is in bytes, the size should be calculated according to the following formulas:

  • BLOB: The length of the largest LOB.
  • NCLOB: The length of the longest TEXT in characters multiplied by two (as each character is handled as a double-byte). If the data includes 4-byte characters, multiply it by four.
  • CLOB: The length of the longest TEXT in characters (as each character is handled as a UTF8 character). If the data includes 4-byte characters, multiply it by two.
  Information note

  • Any LOBs larger than the specified size will be truncated.
  • During Change Processing from Oracle source, inline BLOBs are replicated inline.
  • Changes to this setting will only affect existing tables after they are reloaded.

• Do not limit LOB column size: When this option is selected, LOB columns will be replicated, regardless of size.

  Information noteReplicating LOB columns may impact performance. This is especially true in the case of the large LOB columns which require the replication task to perform a lookup from the source table in order to retrieve the source LOB value.

  • Optimize when LOB size is less than: Select this option when you need to replicate both small and large LOBs, and most of the LOBs are small.

    Information note

    This option is supported with the following sources and targets only:

    • Sources: Oracle, Microsoft SQL server, MySQL, PostgreSQL, and IBM DB2 for LUW

    • Targets: Oracle, Microsoft SQL Server, MySQL, PostgreSQL.

    When this option is selected, during Full Load, the small LOBs will be replicated "inline" (which is more efficient), and the large LOBs will be replicated by performing a lookup from the source table. During Change Processing, however, both small and large LOBs will be replicated by performing a lookup from the source table.

    Information note

    When this option is selected, the replication task will check all of the LOB sizes to determine which ones to transfer "inline". LOBs larger than the specified size will be replicated using Full LOB mode.

    Therefore, if you know that most of the LOBs are larger than the specified setting, it is preferable to use the Replicate unlimited LOB columns option instead.

  • Chunk size (KB): Optionally, change the size of the LOB chunks to use when replicating the data to the target. The default chunk size should suffice in most cases, but if you encounter performance issues, adjusting the size may improve performance.

    Information note

    With some databases, data type validation occurs when the data is inserted or updated. In such cases, replication of structured data types (e.g. XML, JSON, GEOGRAPHY, etc.) may fail if the data is bigger than the specified chunk size.

Lob settings for supported data warehouse targets

• Include LOB columns and limit column size to (KB):

  You can choose to include LOB columns in the task, and set the maximum LOB size. LOBs that are larger than the maximum size will be truncated.

Advanced

Control table settings

• Control tables schema: Specify the target schema for the control tables if you do not want them to be created in the source schema (the default), or in the target schema.

  Information noteThe maximum permitted length for the schema name is 128 characters.
• Create target control tables in tablespace: ​ When the replication target is Oracle, specify the tablespace where you want the target control tables to be created. If you do not enter any information in this field, the tables will be created in the default tablespace in the target database.
• Create indexes for target control tables in tablespace: When the replication target is Oracle, specify the tablespace where you want the control table indexes to be created. If you do not enter any information in this field, the indexes will be created in the same tablespace as the control tables.
• Replication history timeslot (minutes): The length of each time slot in the Replication history control table. The default is 5 minutes.

Control table selection

Apply changes

These settings are only available when the Apply changes replication mode is enabled.

Basic

Apply DDL changes to target table

The following options determine whether DDL operations performed on the corresponding source table will also be performed on the target table.

Advanced

See Change processing tuning.

Store changes

These settings are only available when the Store changes replication mode is enabled.

Basic

DDL options

Select one of the following options to determine how to handle DDL operations on the source tables:

• Apply to change table: DDL operations to the source tables (such as a column being added) will be applied to the corresponding Change Tables only.
• Ignore: All DDL operations to source tables will be ignored.

Advanced

On update

Select Store before and after image to store both the pre-UPDATE data and the post-UPDATE data. When not selected, only the post-UPDATE data will be stored.

Change table creation

The following section describes the options that are available when storing changes in Change Tables.

• Suffix: Specify a string to use as the suffix for all Change Tables. The default value is __ct. The Change Table names are the name of the target table with the suffix appended. So for example, using the default value, the name of the Change Table will be HR__ct.
• Header column prefix: Specify a string to use as the prefix for all of the Change Table header columns. The default value is header__. For example, when using the default value, the header column stream_position will be called header__stream_position.

For more information on Change Tables, see Using Change Tables.

If change table exists when full load starts: Select one of the following to determine how to load the Change Tables when Full Load replication starts:

• Drop and create change table: The table is dropped and a new table is created in its place.

• Delete old changes and store new changes in existing change table: Data is truncated and added without affecting the table metadata.

  Information noteNot supported when Microsoft Fabric is the target platform.
• Keep old changes and store new changes in existing change table: Data and metadata of the existing Change table are not affected.

Table header columns

The Change Table header columns provide information about Change Processing operations such as the type of operation (e.g. INSERT), the commit time, and so on. If you do not need all of this information, you can configure the moving task to create the Change Tables with selected header columns (or none at all), thereby reducing their footprint in the target database..

For a description of the header columns, see Change Tables.

Error handling

Basic

Apply conflicts

Duplicate key when applying INSERT: Select what action to take when there is a conflict with an INSERT operation.

• Ignore: The task continues and the error is ignored.

   

• UPDATE the existing target record: The target record with the same primary key as the INSERTED source record is updated.

• Log record to the exceptions table (default): The task continues and the error is written to the exceptions table.

• Suspend table: The task continues but data from the table with the error record is moved into an error state and its data is not replicated.

• Stop task: The task is stopped and manual intervention is required.

No record found for applying an UPDATE: Select what action to take when there is a conflict with an UPDATE operation.

• Ignore: The task continues and the error is ignored.

• INSERT the missing target record: The missing target record will be inserted into the target table. When the source endpoint is Oracle, selecting this option requires supplemental logging to be enabled for all the source table columns.

• Log record to the exceptions table (default): The task continues and the error is written to the exceptions table.

• Suspend table: The task continues but data from the table with the error record is moved into an error state and its data is not replicated.

• Stop task: The task is stopped and manual intervention is required.

Advanced

Data errors

For data truncation errors: Select what you want to happen when a truncation occurs in one or more specific records. You can select one of the following from the list:

• Ignore: The task continues and the error is ignored.
• Log record to the exceptions table (default): The task continues and the error is written to the exceptions table.
• Suspend table: The task continues, but data from the table with the error record is moved into an error state and its data is not replicated
• Stop task: The task is stopped and manual intervention is required.

For other data errors: Select what you want to happen when an error occurs in one or more specific records. You can select one of the following from the list:

• Ignore: The task continues and the error is ignored.
• Log record to the exceptions table (default): The task continues and the error is written to the exceptions table.
• Suspend table: The task continues, but data from the table with the error record is moved into an error state and its data is not replicated
• Stop task: The task is stopped and manual intervention is required.

Escalate error handling when other data errors reach (per table): Select this check box to escalate error handling when the number of non-truncation data errors (per table) reaches the specified amount. Valid values are 1-10,000.

Escalation action: Choose what should happen when error handling is escalated. Note that the available actions are dependent on the action selected from the For other data errors drop-down list described above.

• Suspend table (default): The task continues, but data from the table with the error record is moved into an error state and its data is not moved.

  Information note

  The behavior differs according to the Change Processing Mode:

  • In Transactional apply mode, the last changes will not be moved.

  • In Batch optimized apply mode, a situation is possible where data will not be moved at all or only partially moved.

• Stop task: The task is stopped and manual intervention is required.
• Log record to the exceptions table: The task continues and the record is written to the exceptions table.

Table errors

When encountering a table error: Select one of the following from the drop-down list:

• Suspend table (default): The task continues but data from the table with the error record is moved into an error state and its data is not replicated
• Stop task: The task is stopped and manual intervention is required.

Escalate error handling when table errors reach (per table): Select this check box to escalate error handling when the number of table errors (per table) reaches the specified amount. Valid values are 1-10,000.

Escalation action: The escalation policy for table errors is set to Stop task and cannot be changed.

Apply conflicts

No record found for applying a DELETE: Select what action is taken when there is a conflict with a DELETE operation.

• Ignore: The task continues and the error is ignored.
• Log record to the exceptions table: The task continues and the record is written to the exceptions table.
• Suspend table: The task continues but data from the table with the error record is moved into an error state and its data is not replicated.
• Stop task: The task is stopped and manual intervention is required.

Escalate error handling when apply conflicts reach (per table): Select this check box to escalate error handling when the number of apply conflicts (per table) reaches the specified amount. Valid values are 1-10,000.

Escalation action: Choose what should happen when error handling is escalated:

• Log record to the exceptions table (default): The task continues and the error is written to the exceptions table.

• Suspend table: The task continues, but data from the table with the error record is moved into an error state and its data is not replicated.

  Information note

  The behavior differs according to the Change Processing Mode:

  • In Transactional apply mode, the last changes will not be replicated

  • In Batch optimized apply mode, a situation is possible where there will be no replication of data or data replication will be partial.

• Stop task: The task is stopped and manual intervention is required.

Environmental errors

• Maximum retry count: Select this option and then specify the maximum number of attempts to retry a task when a recoverable environmental error occurs. After the task has been retried the specified number of times, the task is stopped and manual intervention is required.

  To never retry a task, clear the check box or specify "0".

  To retry a task an infinite number of times, specify "-1"

  • Interval between retry attempts (seconds): Use the counter to select or type the number of seconds that the system waits between attempts to retry a task.

    Valid values are 0-2,000.

• Increase retry interval for long outages: Select this check box to increase the retry interval for long outages. When this option is enabled, the interval between each retry attempt is doubled, until the Maximum retry interval is reached (and continues retrying according to the specified maximum interval).
  • Maximum retry interval (seconds): Use the counter to select or type the number of seconds to wait between attempts to retry a task when the Increase retry interval for long outages option is enabled. Valid values are 0-2,000.

Change processing tuning

Transaction offload tuning

• Offload transaction in progress to disk if:

  The replication task usually keeps transaction data in memory until it is fully committed to the source and/or target. However, transactions that are larger than the allocated memory or that are not committed within the specified time limit will be offloaded to disk.

  • Transaction memory size exceeds (MB): The maximum size that all transactions can occupy in memory before being offloaded to disk. The default value is 1024.
  • Transaction duration exceeds (seconds): The maximum time that each transaction can stay in memory before being offloaded to disk. The duration is calculated from the time that the replication task started capturing the transaction. The default value is 60.

Batch tuning

The following settings are only available when the Apply mode is set to Batch optimized.

• Apply batched changes in intervals:

• Larger than: The minimum amount of time to wait between each application of batch changes. The default value is 1.

  Increasing the Longer than value decreases the frequency with which changes are applied to the target while increasing the size of the batches. This can improve performance when applying changes to target databases that are optimized for processing large batches.

• Less than: The maximum amount of time to wait between each application of batch changes (before declaring a timeout). In other words, the maximum acceptable latency. The default value is 30. This value determines the maximum amount of time to wait before applying the changes, after the Longer than value has been reached.

• Force apply a batch when processing memory exceeds (MB): The maximum amount of memory to use for pre-processing in Batch optimized apply mode. The default value is 500.

  For maximum batch size, set this value to the highest amount of memory you can allocate to the replication task. This can improve performance when applying changes to target databases that are optimized for processing large batches.

Interval

• Read changes every (Minutes)

  Set the interval between reading changes from the source in minutes. The valid range is 1 to 1440.

  Information note

  This option is only available when:

  • Using Data Movement gateway
  • Landing data from SaaS application sources
  • The task is defined with the Change data capture (CDC) update method

Miscellaneous tuning

Statements cache size (number of statements)

The maximum number of prepared statements to store on the server for later execution (when applying changes to the target). The default is 50. The maximum is 200.

Store task recovery data in target database

Select this option to store task-specific recovery information in the target database. When this option is selected, The replication task creates a table named attrep_txn_state in the target database. This table contains transaction data that can be used to recover a task in the event that the files in the Data Movement gateway Data folder are corrupted or if the storage device containing the Data folder has failed.

Apply changes using SQL MERGE

When this option is not selected, the replication task will execute separate bulk INSERT, UPDATE and DELETE statements for each of the different change types in the Net Changes table.

While this method is highly efficient, enabling the Apply changes using SQL MERGE option is even more efficient when working with endpoints that support this option.

This is due to the following reasons:

• It reduces the number of SQL statements run per table from three to one. Most UPDATE operations in large, immutable, file-based cloud databases (such as Google Cloud BigQuery), involve rewriting of affected files. With such operations, the reduction of per-table SQL statements from three to one is very significant.
• The target database only needs to scan the Net Changes table once, significantly reducing I/O.

Optimize inserts

When Apply changes using SQL MERGE is selected together with the Optimize inserts option and the changes consist of INSERTs only, the replication task will perform INSERTs instead of using SQL MERGE. Note that while this will usually improve performance and thereby reduce costs, it might also result in duplicate records in the target database.

Transactional apply

The following settings are only available in when the Apply mode is set to Transactional.

• Minimum number of changes per transaction: The minimum number of changes to include in each transaction. The default value is 1000.

  Information noteThe replication task applies the changes to the target either when the number of changes is equal to or greater than the Minimum number of changes per transaction value OR when the batch timeout value is reached (see below) - whichever occurs first. Because the frequency of changes applied to the target is controlled by these two parameters, changes to the source records may not immediately be reflected in the target records.
• Maximum time to batch transactions before applying (seconds): The maximum time to collect transactions in batches before declaring a timeout. The default value is 1.

Schema evolution

Select how to handle the following types of DDL changes in the schema. When you have changed schema evolution settings, you must prepare the task again. The table below describes which actions are available for the supported DDL changes.

Character substitution

You can substitute or delete source characters in the target database and/or you can substitute or delete source characters that are not supported by a selected character set.

Substituting or deleting source characters

Use the Substitute or delete source characters table to define replacements for specific source characters. This may be useful, for example, when the Unicode representation of a character is different on the source and target platforms. For example, on Linux, the minus character in the Shift_JIS character set is represented as U+2212, but on Windows it is represented as U+FF0D.

Substituting or deleting source characters unsupported by the selected character set

Use the Unsupported source characters by character set table to define a single replacement character for all characters not supported by the selected character set.

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.

Scheduling tasks when working without Data Movement gateway

Data Movement gateway is not supported with a Qlik Talend Cloud Starter subscription and optional with other subscription tiers. When working without Data Movement gateway, near real-time CDC (Change data capture) is not supported. You keep the target data up-to-date by setting a scheduling interval. The schedule determines how often the target datasets will be updated with changes to the source datasets. Whereas the schedule determines the update frequency, the dataset type determines the update method. If the source datasets support CDC, only the changes to the source data will be replicated and applied to the corresponding target tables. If the source datasets do not support CDC (for example, Views), changes will be applied by reloading of all the source data to the corresponding target tables. If some of the source datasets support CDC and some do not, two separate sub-tasks will be created (assuming the Apply changes or Store changes replication options are selected): one for reloading the datasets that do not support CDC, and the other for capturing the changes to datasets that do support CDC. In this case, to ensure data consistency, it is strongly recommended to set the same schedule for both sub-tasks.

For information about minimum scheduling intervals according to data source type and subscription tier, see Minimum allowed scheduling intervals.

To change the scheduling:

1. Open you project and then do one of the following:

   • In tasks view, click on a replication task and select Scheduling.
   • In pipeline view, click on a replication task and select Scheduling.
   • Open the replication task and click the Scheduling toolbar button.
2. Change the scheduling settings as needed and then click OK.