Handling LOB columns
You can override the task's LOB settings for individual tables.
This option is only available for tasks defined with any combination of the following source and target endpoints: Oracle source, Oracle target, PostgreSQL source, PostgreSQL target, Microsoft SQL Server source, Microsoft SQL Server target, MySQL source, and MySQL target.
- 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:
| 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. |
|
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:
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, 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:
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.
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 the Qlik Replicate User Guide and Reference.