Skip to main content
Target metadata

ON THIS PAGE

Target metadata

Target table schema: (if empty, use the schema from the source table): This will automatically add the owner prefix for the target endpoint to all tables if no source schema is defined.

Information note

When replicating to a Hadoop target endpoint, the value specified in this field will be interpreted as a database name (as opposed to a schema name).

LOB handling options

For information on how to override these settings for individual tables, see Handling LOB columns.

Information note
  • During CDC or during Full Load when the Allow unlimited LOB size option is enabled, LOB data types are supported only in tables with a primary key or unique index.
  • When replicating from Microsoft SQL Server, inline LOBS will always be read directly from the logs (i.e. without lookup).

The following LOB handling options are available:

LOB handling options
Option Description

Replicate LOB columns

When this option is selected (the default), LOB columns will be replicated.

Note that replicating LOBs may impact performance. This is especially true in the case of the large LOBs which require Replicate to perform a lookup from the source table in order to retrieve the source LOB value.

Allow unlimited LOB size

Select this option - also known as Full LOB mode - to ensure that all LOBs are replicated without being truncated. This option should be selected when all (or nearly all) of the LOBs you wish to replicate are large (i.e. exceed 1 GB).

Information note

Note  If the task's Change Processing Mode is set to "Batch optimized apply" (the default), Replicate will switch to "Transactional apply" mode to apply tables with LOBs.

Optimize handling when LOB size is less than (KB)

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 endpoints only:

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

  • Targets: Oracle, Microsoft SQL Server, MySQL, PostgreSQL, and Sybase ASE.

When this option is selected, during Full Load, the small LOBs "inline" (which is more efficient), and the large LOBs 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, Replicate 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 better to use the Allow unlimited LOB size 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.

Limit LOB size to (KB)

Select this option if you only need to replicate small LOBs or if the target endpoint 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 Allow unlimited LOB 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.

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.
Information note

In some scenarios, tasks configured to replicate tables with multiple LOB columns may consume a large amount of memory. This is because Replicate allocates memory by multiplying the Limit LOB size to value by the Commit rate during full load value, the sum of which, it multiplies by the number of LOB columns being replicated. So, for example, if LOB size is limited to 5 MB and the default commit rate is used (10000 events), a task replicating 6 LOB columns will consume 30 GB of memory. Note that other factors such as the database type and version may also affect memory consumption.

Should you encounter memory consumption issues and suspect that a combination of the above factors may be the cause, stop the task and lower the value in the Commit rate during full load field. Then resume the task. Repeat this process until acceptable performance/memory levels are reached.

These instructions apply to Change Processing and Full Load tasks.

Information note

Changes to a column’s LOB size while a task is running will not be reflected in the Change Table, unless the target tables are created by Qlik Replicate. In such cases, the task must be configured to drop and create the Change Table (the default) and the target tables need to be reloaded (after the LOB size has changed).

For more information on the Change Table, see Store Changes Settings. For information on reloading target tables, see Working with tasks at runtime .