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

clob, db2, varchar

Solution

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

Problem

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

Original source