title: DB2 cast a large CLOB (> 32KB) into text?
body: <p>I have a DB2 (9.5.1) table which is defined as follows:</p>
  
  <pre><code>CREATE TABLE MY_TABLE 
  ( 
     ID INTEGER DEFAULT 0 NOT NULL, 
     TEXT CLOB(104857600), 
     PRIMARY KEY (ID) 
  );
  </code></pre>
  
  <p>Now if I want to query the actual text string that is stored in the CLOB I do it this way:</p>
  
  <pre><code>select cast(t.TEXT as varchar(32000))
    from MY_TABLE t
    where t.ID = 1;
  </code></pre>
  
  <p>The problem is now that my text gets truncated, but for a <em>varchar</em> the maximum length is 32KB, so this query fails:</p>
  
  <pre><code>select cast(t.TEXT as varchar(33000))
    from MY_TABLE t
   where t.ID = 1;
  </code></pre>
  
  <p>Is there another possibility how I can retrieve the full contents of a CLOB as text output?</p>
  
  <p>Peter</p>