topic Re: Clob replication limit in Qlik Replicate

Clob replication limit

gulshanr — Tue, 23 Aug 2022 18:23:26 GMT

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?

Re: Clob replication limit

sureshkumar — Wed, 24 Aug 2022 05:29:36 GMT

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_netezza.htm#ar_netezzads_4262553552_1345164

Re: Clob replication limit

gulshanr — Wed, 24 Aug 2022 20:04:39 GMT

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.

Re: Clob replication limit

shashi_holla — Wed, 24 Aug 2022 20:14:52 GMT

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,

Re: Clob replication limit

lyka — Fri, 26 Aug 2022 17:03:52 GMT

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

Re: Clob replication limit

john_wang — Sat, 27 Aug 2022 04:33:39 GMT

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.

Re: Clob replication limit

gulshanr — Mon, 29 Aug 2022 14:04:43 GMT

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.

Re: Clob replication limit

john_wang — Mon, 29 Aug 2022 14:37:08 GMT

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.

Re: Clob replication limit

gulshanr — Tue, 30 Aug 2022 12:38:33 GMT

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.