Lob Handling

[Pranita123]

Hello Community,

we are using Microsoft SQL Server as source and PostgreSQL as target where one of table is not having Primary key but includes a LOB column for that we are getting Below Warning

"Column ''yzx" was removed from table definition 'dbo.xyz': the column data type is LOB, and the table has no primary key or unique index"

Any Help or guidance on addressing this issue would be greatly appreciated.

Thank you,

Pranita

[sureshkumar]

Hello @Pranita123 

This is a documented limitation:
When replicating a table that has no Primary Key or Unique Index, LOB columns will not be replicated.

https://help.qlik.com/en-US/replicate/May2024/Content/Replicate/Main/Introduction/Limitations.htm#ar...

Regards,

Suresh

[RajyaLakshmi]

Hi @Pranita123 

Thanks for reaching us in Qlik community.

When replicating a table that has no Primary Key or Unique Index, LOB columns will not be replicated.

This is a limitation. Please refer the below user guide for details:

https://help.qlik.com/en-US/replicate/May2024/Content/Replicate/Main/Introduction/Limitations.htm#ar...

If you want to replicate then there is an option. You will have to setup a new task for the table with LOB and not PK and do only full load. You can schedule it or run the task again to replicate changes from source, if there were any changed to catch after the initial full load completes.

Hope this helps.

Best Regards,
Rajya Lakshmi

[DesmondWOO]

Hi @Pranita123 ,

Could you tell me following information?

1) Replicate version
2) Source Endpoint: Microsoft SQL Server or Microsoft SQL Server (MS-CDC)
3) Datatype of 'yzx' column: TEXT, VARBINARY, ....
4) Task setting (Target Metadata): Allow unlimited LOB or Limit LOB? Settings?

Regards,
Desmond

[Heinvandenheuvel]

And just in case you wanted to know WHY this limitation exist....

Replicate engineers in their infinite wisdom made the unilateral decision that is it is 'best' (choose your definition of best for what) to postpone dealing with lob data until the last moment. They skip it while reading the change log. For several scenarios that is a wise and prudent choice. LOBs by their very nature can be very big and consume many resources. It could be that the whole change event is filtered out. It could be that there are multiple changes to the lob and only the final version is relevant.

With that in mindm, it was decided to NOT collect lob data right-away but postpone until the change is being applied. At  that point (in the TARGET_APPLY code)  the lob data is quickly (or not so quickly) collected by a SELECT LOB (,LOB2, LOB3, ...) FROM <table> WHERE <PK> = <pk-value>

If there is no PK, then this method cannot be applied effectively and thus the limitation.

hth,

Hein.

[ArunPrasad]

Hello @DesmondWOO,

I am currently facing a similar issue where I am unable to see two columns which are having data type as Text () at Source table (under Table settings). Below are the information and could you help on how to enable these columns at Source table ? May I also know if there are any other options available ?

1) Replicate version: November 2023 (2023.11.0.480)

2) Source Endpoint: Microsoft SQL Server or Microsoft SQL Server (MS-CDC):  Microsoft SQL Server
3) Datatype of 'yzx' column: TEXT, VARBINARY, : text
4) Task setting (Target Metadata): Allow unlimited LOB or Limit LOB? Settings?

Replicate LOB columns is checked with "Limit LOB size to (KB)" set to 64 
 

[DesmondWOO]

Hi @ArunPrasad ,

If your source endpoint is Microsoft SQL Server, MS-REPLICATION is used for the CDC. Due to SQL Server limitation, MS-REPLICATION requires source tables to have a primary key; otherwise, table cannot be added in to the publication.

Regards,
Desmond