Clob replication limit

[gulshanr]

Hello all,

We are in need to replicate some table where we have atleast one column marked as CLOB and by definintion it stores upto 2 GB data on source. 

Source DB2 (Mainframe)

Destination:- Netezza 

I tried with Setting --> Target metadata --> Limit LOB size to 16 KB and it stopped working. It only works with 8KB but data in destination is being truncated. 

Any suggestion to improve or store such large data from source to Destination?

[sureshkumar · Accepted solution]

Hi Gulshan,

There is limitation at Netezza

"Note also that the size of a row in the IBM Netezza database cannot exceed 64KB. This should be taken into consideration when specifying the maximum LOB size in the Metadata tab." 

https://help.qlik.com/en-US/replicate/May2022/Content/Replicate/Main/IBM%20Netezza/limitations_netez... 

[gulshanr]

Suresh, I set setting to be 8 KB not 64KB, and it work but it tuncate data. Whenever I attempt to make it 16 KB task fail without starting.

[shashi_holla]

Hi @gulshanr 

Full LOB columns are not supported, so we cannot enable the LOB option on the task. But we can change the column datatype on the target to STRING(64000) or less and try.

Thank you,

[lyka · Accepted solution]

Hello,

The 64KB limit applies to the entire row and not for a single column, So if its a big and wide table then this maybe an issue since you may hit the 64kb limitation. If you can, remove some columns that are not required for Replicate.

If you want to rule out the 65kb limitation, run a task with just 1 table and replicate only the lob column. then set the limit lob size to accommodate the max length for the lob size.

Hope this helps!

Thanks

Lyka

[john_wang · Accepted solution]

Hi All, @gulshanr 

Agree with Lyka.

Netezza has a column level limitation, Whereas char and varchar can have a maximum character width of 64000 characters (Nchar/Nvarchar up to 16000). 

Netazza has a row size limitation as well, it's 65535 bytes. or called 64KB (it's mentioned in Qlik User Guide: ... size of a row... cannot exceed 64KB).

So in Replicate task setting , "Limit LOB size to (KB)" cannot set to 64KB (it's in fact 63485 bytes, Otherwise the single column maximum length may exceed 64000 bytes.), 62K is the max reasonable value for this parameter. 

Regarding the error in this case (@gulshanr said the "Limit LOB size to 16 KB" rather than 64KB), looks to me it's caused by other reasons. let's troubleshoot it by:

1- Check netezza log

      During the Full Load, Netezza load utility produced an error log file in Replicate Server. Please check the file to get the exact reason.

2- Bad data or NULL data

      The error probably caused by bad data, NULL data etc. Set internal parameters to TRUE in Target End Point:

    keepCSVFiles
    keepErrorFiles
    - set the max file size to small value eg 100mb

       Check the interim CSV files to get better understand of the reason. IF it's bad data, or NULL column value etc, use the transformation to solve the issue.

Hope this helps.

Best Regards,

John.

[gulshanr]

Thank you very much, we have opened a support case for this. I will work with QLik support to get better handle on this. In my view, its a defect.

fyi:- Table has only 3 column (datetime(6), string(1) and a clob field), created under individal task. If changing from 8 KB to 16 KN it fails, then there have to be some defect in the product. Task doesnt even start, so no point in checking at Netezza DB end.

[john_wang]

Hello @gulshanr ,

Thanks for your update.

I'm not meaning to check the Netezza DB end. In Netezza Client side (it's Replicate Server machine), while the Netezza load utility is called, a log file will be produced. In your env, the utility log file is 

H:\Attunity\Data\Data01\logs\GCMS_MSG_TXT.ADMIN.EPY_TEST_RPL_CE.nzlog

The log file contains some errors clue. Hope it helps to understand the root reason.

Best Regards,

John. 

[gulshanr]

John, there are two level of changes needed to get to 64K level, one does need to change task level limit i.e. setting it to be 64K or higher, along with setting destination CLOB field to varchar with range whatever you want to define. But point is task level override field level. 

Another important point being, even if you define field level to be 100K or so, clob field at netezza table will be still 64K only (Netezza limit).

Considering its netezza limit, there isnot much we Qlik can do.