Did DB2 draw large CLOB text (> 32KB) into text?

Question

I have a DB2 table (9.5.1), 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 request the actual text string stored in the CLOB, I do it like this:

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

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

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

Is there any other possibility, how can I get the full contents of the CLOB as text output?

Peter

Answer 1

I found this elsewhere on the Internet and thought that I would share what I saw, since it works around the limit of 32 thousand ..

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

Answer 2

In a similar situation, when I had to extract the xml data, it worked for me

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

I used to be able to do this in squirrel sql without CAST, but in the latest version I had to use cast

Answer 3

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

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

On the other hand, is this really the limit? Are you really using CLOBs when clauses, etc.? RDBMS is optimized for smaller row sizes that are efficiently processed by transactions.

Generally speaking, data flow. Consider redesigning your data model if this CLOB contains data that can be divided into several columns and you need some of its data in your query (where ..., order from ..., etc.).