LOB column not replicated to csv file

[ANANDKUMAR]

Hi,

We recently encountered an issue with our Replicate task, where the source is a "SQL Server" and the target is a "csv file." During the generation of the CSV file, we noticed that certain columns were missing. Upon further analysis, we identified that these missing columns were LOB columns, and the "LOB replication option" was not enabled in our replicate task.

To address this, we are consider enabling the "Replicate LOB columns" option. However, we have a few questions and need your guidance on the following:

1. How to check the LOB size in the source table?

   We would like to understand how we can determine the size of LOB columns in the source SQL Server table.

2. How to decide the "Replicate LOB columns" option?

   We need your inputs on determining whether we should go with "Allow unlimited LOB size" or "Limit LOB size to (KB)" .

3. If we select "Allow unlimited LOB size," how should we calculate and provide the chunk size?

   For the scenario where we choose to "Allow unlimited LOB size," we would like to know how to calculate and specify the chunk size.

4. If we select "Limit LOB size to (KB)," how can we calculate and provide the value?

   In the case of selecting "Limit LOB size to (KB)," we need guidance on how to calculate and set an appropriate value.

If possible, we are also interested in any SQL queries that can assist in gathering the necessary information.

Thanks,

[john_wang]

Hello @ANANDKUMAR ,

Welcome to Qlik Community forum and thanks for reaching out here!

---

To address this, we are consider enabling the "Replicate LOB columns" option. 

---

You are right, the option should be enabled otherwise the LOB columns will be ignored in the replication.

1. How to check the LOB size in the source table?

   You may query the column max LOB size in a table, for example :

   SELECT MAX(DATALENGTH(NOTES)) from dbo.MyTable;

   where "NOTES" is the LOB column name, the table name is "dbo.MyTable".

2. How to decide the "Replicate LOB columns" option?

   We need your inputs on determining whether we should go with "Allow unlimited LOB size" or "Limit LOB size to (KB)" .

   Both options work for you. However it depends on if target side database/file accepts unlimited LOB size column.
   if possible, please use the "Limit LOB size" option and input the max LOB size, the max size is about 100M (102400 KB).
   
   

3. If we select "Allow unlimited LOB size," how should we calculate and provide the chunk size?

   Default size should work for you.
   
   

4. If we select "Limit LOB size to (KB)," how can we calculate and provide the value?

   See above answer for (1) and (3). If the LOB size exceed the max size setting, the column value will be truncated to the given size. please set the size to a reasonable value to gain better performance.

Hope this helps,

John.

[ANANDKUMAR]

Thank you John for your answer.

we ran a query in our test environment to determine the maximum LOB size in a specific table, and the result was 8192. We anticipate a slightly higher value in our production environment.

Our primary questions are:

1. Could setting the "Limit LOB size" to 100M potentially cause performance issues during replication?
2. How can we determine the optimal "Limit LOB size" value, considering the observed maximum LOB size in the test environment and the anticipated variations in production?

Can you provide any guidance here.

[john_wang]

Hello @ANANDKUMAR ,

Thanks for the feedback.

we ran a query in our test environment to determine the maximum LOB size in a specific table, and the result was 8192.

---

It's 8KB, then you need NOT set the LOB size to 102400KB (100M). Set it to 8K or a bit larger is good enough.

---

1. Could setting the "Limit LOB size" to 100M potentially cause performance issues during replication?

---

Yes, however it's hard to tell how much impact it has. Test it may know much better.

---

2. How can we determine the optimal "Limit LOB size" value, considering the observed maximum LOB size in the test environment and the anticipated variations in production?

---

If the LOB size may exceed a given limitation or it's hard to know the max size, then set it to "Allow unlimited LOB size".  Consider the max LOB size in SQL Server up to 2G, it's better to set the value to a reasonable value.

Regards,

John.

[ANANDKUMAR]

Thanks for your answer.

In the above comment, you mentioned to set the LOB size to 8k. It means 8000 or 8kb? Could you please confirm.

[john_wang · Accepted solution]

Hello @ANANDKUMAR ,

IN Replicate console GUI the size is KB:

set it to 8 is meaning 8KB.

Hope this helps.

John.