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.