We're encountering an issue with working with CLOB's. We're doing a basic insert into a table that has a CLOB column. Sometimes the CLOB is fairly tame in size (10k-15k), and once in a while they're fairly large (30k-60k).

When the CLOB is greater than 32K, the insert fails. Found some documentation which mentions that a LOB can't be larger than 32K, and if it is, it needs to be handled via assignment of host variables . I've seen host variables before (:xyz), but haven't actually worked with them.

How can one use host variables within an SQL stored procedure?

Thanks!

I APOLOGIZE.....THIS IS A REPEAT OF ANOTHER POST. ACCIDENTALLY SUBMITTED TWICE.

Hi Ansonee,

Please check the tablespace definition of CLOB tablespace. If it is segmented and the SEGSIZE parm (page size) was only 32K, you might experience the problem.

Also, the max length of the VARCHAR datatype is usually dictated by the SEGSIZE (page size) value.

So, I recommend you either to have to insert additional rows for data beyond 32k or try dropping the tablespace and re-create the same with SEGSIZE 64K and see your 64k is absorbed in a single row easily.

Please let me know how you are doing with this..

I've been diggin through the documentation and found references to EXTENTSIZE and PAGE SIZ. You can create a tablespace with EXTENTSIZE of 64K, but I saw that the largest you can make a page is 32K - also holds true for bufferpools.

Is this what you were referring to: EXTENTSIZE? We're running DB2 Version 8, FixPak 4 on AIX. It looks like SEGSIZE is an option only available z/OS, OS/390....

Thank You!!

Yes. I am referring to page size. In db2 varchar size is always dictated by page size. I am positive that your page size is only 32k


--------------------------------------------------------------------------

If our max page size then is only 32k, then I'm assuming there's no way to work around the issue of CLOB's larger than 32k? What other options are available besides just doing a plain insert?

We'd rather do it the nice and simple way if at all possible.