DB2 cast a large CLOB (> 32KB) into text?

Question

I have a DB2 (9.5.1) table which is defined as follows:

CREATE TABLE MY_TABLE 
( 
   ID INTEGER DEFAULT 0 NOT NULL, 
   TEXT CLOB(104857600), 
   PRIMARY KEY (ID) 
);

Now if I want to query the actual text string that is stored in the CLOB I do it this way:

select cast(t.TEXT as varchar(32000))
  from MY_TABLE t
  where t.ID = 1;

The problem is now that my text gets truncated, but for a varchar the maximum length is 32KB, so this query fails:

select cast(t.TEXT as varchar(33000))
  from MY_TABLE t
 where t.ID = 1;

Is there another possibility how I can retrieve the full contents of a CLOB as text output?

Peter

Answer 1

I found this elsewhere on the web and thought I would share seeing as it works around the 32k limit.

SELECT 
  XMLCAST (
    XMLPARSE (
      DOCUMENT CAST (
        MY_CLOB_DATA AS BLOB
      ) 
      PRESERVE WHITESPACE
    ) as XML
  ) 
FROM 
MY_TABLE
WHERE ID = 1

Answer 2

In similar situation, where I had to retrieve xml data, this worked for me

select my_id, cast(xmlserialize(my_column as clob(1m)) as varchar(20000)) from schema.my_table where my_id  =  463

earlier I was able to do this in squirrel sql without the CAST but latest version I had to use the cast

Answer 3

As far as I know there is no way to get around the 32k limit, if you use it in SQL selects like you described.

If you use JDBC to retrieve your data, instead of using getString() on the result set, you get a CLOB handle, and from that you can get a stream.

On the other hand, is it really a limit? Do you really use CLOBs in where clauses etc.? A RDBMS is optimized for smaller row sizes to be handled in transactions efficiently.

Generally speaking, stream the data. Consider redesigning your data model, if this CLOB contains data which can be split into multiple columns, and you need some of its data in your query (where ..., order by ... etc.).