J2EE development of the road is full of traps, I believe that many people have a deep experience.
IBM stuff is just dumb and hard to use!!
There have been some recent issues with DB2 cloBs and BLOBs using JDBC:
I used a DB2 V9 installer that comes with a driver. There are many DB2 drivers, and this one is the most commonly used and standardized one.
I used the following driver name:
db2jcc.jar


db2jcc_license_cisuz.jar


db2jcc_license_cu.jar
Clob: No problem writing Clob, no problem reading Clob.
Blob problems: Both write normally no problem. However, when writing Data and Blob fields at the same time, there is a problem. (Encountered in one example, not enough to give a definite result).
The exception code thrown is as follows:


com.ibm.db2.jcc.c.SqlException


at com.ibm.db2.jcc.c.ec.<init>(ec.java:183)


at com.ibm.db2.jcc.b.d.b(d.java:1328)


at com.ibm.db2.jcc.c.s.a(s.java:748)


at com.ibm.db2.jcc.c.s.U(s.java:1393)


at com.ibm.db2.jcc.c.wf.getClob(wf.java:914)


.


Setting up the environment for testing:
CREATE
TABLE

ZFZVF.T_LOB (


NAME
VARCHAR

(24),


TXT CLOB(2M) LOGGED

NOT

COMPACT,


IMG BLOB(2M) LOGGED

NOT

COMPACT


);


DROP
TABLE

ZFZVF.NJ_GT;


COMMIT

;


CREATE
TABLE

ZFZVF.NJ_GT (


ID

BIGINT

.


ZTBS

BIGINT

.


NJBS

BIGINT

.


ND

VARCHAR

(4),


NJNR BLOB(2M) LOGGED

NOT

COMPACT,


SJC

TIMESTAMP


);


COMMIT

;


COMMENT

ON
TABLE

ZFZVF.NJ_GT

IS
‘Annual examination _ individual’

;


COMMENT

ON

ZFZVF.NJ_GT (


ID

IS
‘ID’

.


ZTBS

IS
‘the main BS’

.


NJBS

IS
‘annual BS’

.


ND

IS
‘annual’

.


NJNR

IS
‘Annual Inspection Contents’

.


SJC

IS
‘Timestamp’

);


COMMIT

;





Examples are as follows:

package

lob;





import

java.io.*;


import

java.sql.*;





/ * *


* DB2 Clob, Blob Bug exploration


* File:

TestLob4DB2

.java


* User: leizhimin


* Date: 2008-3-3 8:56:00


* /


public
class

TestLob4DB2{


public
static
final

String url =

“jdbc:db2:

/ / 127.0.0.1:50000 / ZFZVF “;


public
static
final

String username =

“zfzvf”

;


public
static
final

String password =

“zfzvfdb2”

;


public
static
final

String driverClassName =

“com.ibm.db2.jcc.DB2Driver”

;





/ * *


* Get database Connection


*


* @return Database Connection


* /


public
static

Connection makeConnection() {


Connection conn =

null

;


try

{


Class.forName(driverClassName);


}

catch

(ClassNotFoundException e) {


e.printStackTrace();


}


try

{


conn = DriverManager.getConnection(url, username, password);


}

catch

(SQLException e) {


e.printStackTrace();


}


return

conn;


}





/ * *


* Test connection


* /


public
static
void

testConnection() {


Connection conn = makeConnection();


try

{


Statement stmt = conn.createStatement();


ResultSet rs = stmt.executeQuery(

“SELECT * FROM ZFZVF.DM_HYML”

);


while

(rs.next()) {


String s1 = rs.getString(1);


String s2 = rs.getString(2);


System.out.println(s1 + s2);


}


rs.close();


stmt.close();


}

catch

(SQLException e) {


e.printStackTrace();


}

finally

{


try

{


conn.close();


}

catch

(SQLException e) {


e.printStackTrace();


}


}


}





/ * *


* Insert file test


* /


public
static
void

testInsertlob() {


Connection conn = makeConnection();


try

{


conn.setAutoCommit(

false

);


File txtFile =

new

File(

“C:\\txt.txt”

);


File imgFile =

new

File(

“C:\\img.png”

);


int

txt_len = (

int

) txtFile.length();


int

img_len = (

int

) imgFile.length();


try

{


InputStream fis1 =

new

FileInputStream(txtFile);


InputStream fis2 =

new

FileInputStream(imgFile);


PreparedStatement pstmt = conn.prepareStatement(

“INSERT INTO ZFZVF.T_LOB(NAME,TXT,IMG) VALUES(‘G’,? ,?) ”

);


pstmt.setAsciiStream(1, fis1, txt_len);


pstmt.setBinaryStream(2, fis2, img_len);


pstmt.executeUpdate();


conn.commit();


}

catch

(FileNotFoundException e) {


e.printStackTrace();


}


}

catch

(SQLException e) {


e.printStackTrace();


}

finally

{


try

{


conn.close();


}

catch

(SQLException e) {


e.printStackTrace();


}


}


}





/ * *


* Querying DB2 Clob tests throws an exception


* /


public
static
void

testQueryLob() {


Connection conn = makeConnection();


try

{


conn.setAutoCommit(

true

);


PreparedStatement stmt = conn.prepareStatement(

“SELECT TXT,IMG FROM ZFZVF.T_LOB”

);


ResultSet rs = stmt.executeQuery();


while

(rs.next()) {


Clob clob = rs.getClob(

“TXT”

);


Blob blob = rs.getBlob(

“IMG”

);


InputStreamReader ir = (InputStreamReader) clob.getCharacterStream();


File fileOutput =

new

File(

“C:\\txt_1.txt”

);


FileOutputStream fo =

new

FileOutputStream(fileOutput);


int

c;


while

((c = ir.read()) != -1) {


fo.write(c);


break

;


}


fo.close();


}


}

catch

(SQLException e) {


e.printStackTrace();


}

catch

(IOException e) {


e.printStackTrace();


}

finally

{


try

{


conn.close();


}

catch

(SQLException e) {


e.printStackTrace();


}


}


}





/ * *


* Query DB2 Blob tests


* /


public
static
void

testBlobQuery() {


Connection conn = makeConnection();


try

{


conn.setAutoCommit(

true

);


PreparedStatement stmt = conn.prepareStatement(

“SELECT img FROM ZFZVF.T_LOB”

);


ResultSet rs = stmt.executeQuery();


int

i = 0;


while

(rs.next()) {


Blob blob = rs.getBlob(1);


InputStream inputStream = blob.getBinaryStream();


File fileOutput =

new

File(

“c:\\str_x”

+ i +

“.txt”

);


FileOutputStream fo =

new

FileOutputStream(fileOutput);


int

c;


while

((c = inputStream.read()) != -1)


fo.write(c);


fo.close();


System.out.println(

“Blob ”

+ i +

” retrieved!!”

);


i++;


}


}

catch

(SQLException e) {





}

catch

(FileNotFoundException e) {


e.printStackTrace();


}

catch

(IOException e) {


e.printStackTrace();


}

finally

{


try

{


conn.close();


}

catch

(SQLException e) {


e.printStackTrace();


}


}


}





/ * *


* Querying DB2 Clob tests throws an exception


* /


public
static
void

testClobQuery() {


Connection conn = makeConnection();


try

{


conn.setAutoCommit(

false

);


PreparedStatement stmt = conn.prepareStatement(

“SELECT TXT FROM ZFZVF.T_LOB”

);


ResultSet rs = stmt.executeQuery();


conn.commit();


while

(rs.next()) {


Clob clob = rs.getClob(1);


InputStream is = clob.getAsciiStream();


File fileOutput =

new

File(

“C:\\ttttt.txt”

);


FileOutputStream fo =

new

FileOutputStream(fileOutput);


int

c;


while

((c = is.read()) != -1)


fo.write(c);


fo.close();


break

;


}


}

catch

(SQLException e) {


e.printStackTrace();

//To change body of catch statement use File | Settings | File Templates.


}

catch

(FileNotFoundException e) {


e.printStackTrace();

//To change body of catch statement use File | Settings | File Templates.


}

catch

(IOException e) {


e.printStackTrace();

//To change body of catch statement use File | Settings | File Templates.


}

finally

{


try

{


conn.close();


}

catch

(SQLException e) {


e.printStackTrace();


}


}





}





public
static
void

main(String args[]) {


testConnection();


testInsertlob();


// testQueryLob();


testBlobQuery();


testClobQuery();





}


}

Running each test method one by one, you can see that an exception was thrown when operating on a Clob.
Because there are so many DB2 drivers, and the test driver is usually used for development, I didn’t try another driver. For this problem, I use bloBs myself to store the fields that the application Clob handles, that is, I define the original Clob as a Blob and operate with the Blob. I don’t want to go over the code.
Because of the heavy use of bloBs, there is a problem with inserting dates and bloBs simultaneously. Interested friends can try, recently very busy, also do not have so much energy to investigate. Hopefully IBM can fix these issues soon.