Character data, right truncation occurred exception while fetching DB2 CLOB data with Connect for JDBC for DB2

Nov 22, 2011
Knowledge

Title

URL Name

Character-data-right-truncation-occurred-exception-while-fetching-DB2-CLOB-data-with-Connect-for-JDBC-for-DB2

Article Number

000154867

Environment

Product: Connect for JDBC driver for DB2 Version: 4.1.x, 4.2.x OS: all java versions Database: DB2 Application: not application specific

Question/Problem Description

When fetching DB2 LOB data > 32 K using the Connect for JDBC Driver for DB2, an exception is thrown.

Steps to Reproduce

Clarifying Information

create a file tmptestclob.txt which contains the actual text of a CLOB > 32K characters
create a file data.txt which contains the below line

1, tmptestclob.txt

execute the below statements in the db2 tool

create table clobtable (ID int not null primary key, clobcolumn clob(1G))

import from data.txt of del modified by lobsinfile insert into clobtable

create a java application which tries to execute

rs = stmt.executeQuery("SELECT id, clobcolumn FROM clobtable WHERE id = 1");

Error Message

[DataDirect][DB2 JDBC Driver][DB2]Character data, right truncation occurred; for example, an update or insert value is a string that is too long for the column, or a datetime value cannot be assigned to a host variable, because it is too small.

Defect Number

Enhancement Number

Cause

This is a documented DB2 7/DRDA restriction.

References to Written Documentation:
DataDirect Connect for JDBC User’s Guide,
Chapter 4: "The DB2 Driver".

Retrieving and updating Blobs, Clobs, and DBClobs is supported by the DB2 driver with the following databases:

DB2 V8.x and higher for Linux/UNIX/Windows
DB2 for z/OS
DB2 V5R2 and higher for iSeries

The DB2 driver supports Clobs up to a maximum of 2 GB with these databases. The DB2

driver supports retrieving and updating Clobs up to a maximum of 32 KB with all other supported DB2 databases.

DRDA v2 does not support full size LOBs so the driver workarounds the limitation by casting these as VARCHAR or VARCHAR FOR BIT DATA. That is where the 32K limitation comes from.

Resolution

Use one of the below database versions when inserting/retrieving/updating Blobs, Clobs, DBClobs datatypes:

DB2 V8.x and higher for Linux/UNIX/Windows
DB2 for z/OS
DB2 V5R2 and higher for iSeries

Workaround

Use code (*) as below to retrieve the clob in parts of 32700 characters:

// get total length of clob
rs = stmt.executeQuery("SELECT length(clobcolumn) FROM clobtable WHERE id = " + clobID);

rs.next();

int mylength = rs.getInt(1); // contains the length of the CLOB column

// get the clob in chunks of 32700 characters and write it to a file
FileWriter fstream = new FileWriter("out.txt");

BufferedWriter out = new BufferedWriter(fstream);

for (int i = 1; i <= mylength ; i = i + 32700) {

rs = stmt.executeQuery("select substr(clobcolumn, " + i + ", 32700) from clobtable WHERE id = " + clobID);

rs.next();

out.write(rs.getString(1).trim());

}

out.close();

(*) code is simplified: no exception checking is done.

Notes

Keyword Phrase

Last Modified Date

9/14/2015 1:06 AM

Character data, right truncation occurred exception while fetching DB2 CLOB data with Connect for JDBC for DB2

Files(0)

Was this article helpful?

Was this article helpful?

Character data, right truncation occurred exception while fetching DB2 CLOB data with Connect for JDBC for DB2

Information

Related Articles