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'm sure that lob's can be greater than 1gb and much more.
the host vars u mean are lob var (kind of call by value)
or lob locator vars (kind call by reference). if u define a table
containing a lob column i think u need to create an auxilliary table too.
the first part of a lob 'object' is stored in the base table and the rest is stored in the auxilliary table. but be carefull with logging and large lob's !!! i'm not sure but i think i read about an capability to store lob outside db2 and a reference to the external location within a db2 table.

hope this will help

regards

marc gaines

In DB2 for OS/390, yes, there are different structures which house an auxiliary index and also house the actual LOB. In LOB tables, an additional column is created which contains a pointer to the row in the auxiliary table. Here's what IBM says my problem is:

SQL0102N The string constant beginning with
"'SUkqANiYAAAmoCA3+QyQ1Bzj5DJCFmQybZrkMgGdQVsDKw45 DUcgcwLwQyQbvynFODI+I" is too long.

Explanation:

One of the following has occurred:
The string constant beginning with "<string>" has a length
greater than 32672 bytes. Character strings with lengths greater
than 32672 bytes or graphic strings with lengths greater than 16336
characters can be specified only through assignment from host
variables. Note that other servers in the DB2 family of products
may specify a different size limit for character strings. Consult
the documentation for the appropriate DB2 product for details.

sqlcode : -102

sqlstate : 54002

The column this is being inserted into is a CLOB(1M) - plenty of room for a 52K CLOB...

I'm out of ideas here...