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