selecting on CLOB column only returns 4K, not full value?

Steve Mestdagh

unread,

May 30, 2002, 4:40:47 PM5/30/02

to

Hi,
I'm trying to select a full CLOB value but am only getting 4K
returned. column size is 1MB. How do I get the whole CLOB value. I've
looked in the online db2 docs to no avail.

TIA,
steve

Knut Stolze

unread,

May 30, 2002, 5:15:15 PM5/30/02

to

How big is the CLOB value? Maybe it is only 4K long? (LOBs are varying
length.)

If you think it is longer, could you post some code?

--
Knut Stolze
DB2 Spatial Extender
IBM Silicon Valley Lab

Steve Mestdagh

unread,

May 31, 2002, 10:10:59 AM5/31/02

to

I should have mentioned I'm doing command line sql on an AIX box. The
CLOB column is of length: 1048576

sql: db2 "select content from documentmetdata" >file

thanks,
steve

Knut Stolze <sto...@us.ibm.com> wrote in message news:<slrnafd5l2...@stolze.svl.ibm.com>...

Steve Mestdagh

unread,

May 31, 2002, 10:53:47 AM5/31/02

to

I don't know where I got the 4K limit from. It's actually 8K. Must be
some binary influence thing :-) Also I neglected to add the where
clause in my sample sql statement. Not that it matters.
steve

Knut Stolze

unread,

May 31, 2002, 11:13:52 AM5/31/02

to

On 31 May 2002 07:10:59 -0700, Steve Mestdagh wrote:
> I should have mentioned I'm doing command line sql on an AIX box. The
> CLOB column is of length: 1048576
>
> sql: db2 "select content from documentmetdata" >file

How long are your LOB values? What does the following query return:

SELECT LENGTH(content)
FROM documentmetdata

Steve Mestdagh

unread,

May 31, 2002, 5:44:59 PM5/31/02

to

I also checked this.

db2 "select max(length(content)) from documentmetadata"

1
-----------
1048575

1 record(s) selected.

Additional info on column....

$ db2 "describe select * from documentmetadata"

SQLDA Information

sqldaid : SQLDA sqldabc: 2304 sqln: 52 sqld: 26

Column Information

sqltype sqllen sqlname.data
sqlname.length sqllonglen sqldatatype_name.data
sqldatatype_name.length
-------------------- ------ ------------------------------
-------------- ---------- ---------------------------
-----------------------
<snip/>
408 CLOB 0 CONTENT
7 1048576 SYSIBM .CLOB
13

Knut Stolze <sto...@us.ibm.com> wrote in message news:<slrnaff4s2...@stolze.svl.ibm.com>...

tempor...@hotmail.com

unread,

May 31, 2002, 6:54:35 PM5/31/02

to

That will not work. See the chapter on "Using Command Line SQL Statements" in the Command Reference
Guide. This chapter will tell you how to fetch a LOB into a file when retrieving from CLP.

When using SELECT on CLP (which is what you are doing), LOBs are truncated to the first 4K of the
object.

What you are interesed in, is looking at the additional clause on the FETCH statement for LOBs (in the
above mentioned chapter of the Command Reference Guide). You need to declare a cursor on CLP, open it,
and then just start fetching those lobs (they will go into a file).

Phil Castle

unread,

Jun 1, 2002, 6:10:56 AM6/1/02

to

We do a lot of LOB processing. Perhaps the easiest way of retrieving a
LOB value is to use the Export utility and the LOBSINFILE option. This
will export all your LOBs into files - which is argueably more useful
than having a bunch of text coming back to the CLP. There is no size
limit on the Export - we have exported LOBs up to 5MB without
truncation or other probs.

If you want a GUI tool, my Advanced Query Tool at www.querytool.com
has a lot of capabilities for manipulating LOBs - it wil display up to
the first 10K bytes of a LOB/CLOB in the GUI. For LOBs larger than
this it will export them to a file, which you can then view in
whatever Windows application is appropriate. It also allows you to
load LOB values from files.

I am currently working on a project which uses LOBs extensively, and
these capabilities make it very easy for dealing with LOBs.

Phil Castle.

mest...@yahoo.com (Steve Mestdagh) wrote in message news:<4891ab5e.02053...@posting.google.com>...