Lob is closed. ERRORCODE=-4470, SQLSTATE=null

Question

I am using IBM websphere commerce and db2, have following piece of code

Clob clobVar = null;
if (result.elementAt(3) != null)
    clobVar = (Clob) result.elementAt(3);

if (clobVar == null) {
    infoTable.put("EInfo", "");
} else {
    stringTemp = clobVar.getSubString(1, (int) clobVar.length());
    infoTable.put("EInfo", stringTemp); 
}

Code works fine till

clobVar = (Clob) result.elementAt(3);

but as soon as execution comes to

stringTemp = clobVar.getSubString(1, (int) clobVar.length());

System throws an exception

[jcc][10120][11936][4.3.111] Invalid operation: Lob is closed. ERRORCODE=-4470, SQLSTATE=null

What I am doing wrong?

How to resolve this issue?

Leduc

Answer 1

This issue can be solved by adding progressiveStreaming=2; argument to the connection url

The fully specified Connection URL was to be given as below:

jdbc:db2://localhost:50000/SAMPLE:progressiveStreaming=2;

Incase you have exception on that parameter add the following to it:

jdbc:db2://localhost:50000/SAMPLE:driverType=4;fullyMaterializeLobData=true;fullyMaterializeInputStreams=true;progressiveStreaming=2;progresssiveLocators=2;

It is preferred to use db2jcc4.jar

Moluccas

You saved me! The first trick worked. (Please don't ommit the ; at the end) Thanks a lot :) – Monocotyledon

Answer 2

Since it is too long for a comment and I was researching for some time, here is some explanation why progressiveStreaming=2; works:

It is a special parameter for the DB2 JDBC driver that changes the way the driver handles Large Objects (LOBs).

If the parameter is included in the JDBC URL, it tells the DB2 JDBC driver to stream LOB data "progressively". This means that the driver will not load the LOB data into memory all at once, but will instead load it in small chunks or "chunks" as you access the data.

In most cases, it is possible to access LOB data even after the ResultSet is closed. This is because the JDBC driver can still access the underlying LOB data in the database even after the ResultSet is closed.

Here is an example of how you could use the parameter in your JDBC URL:

String url = "jdbc:db2://localhost:50000/MYDB:progressiveStreaming=2;";

Please note that the progressiveStreaming=2; parameter only works with certain versions of the DB2 JDBC driver. Make sure that you are using a supported driver version.

Tad

Answer 3

If everything has worked earlier with same code...but the issue came up after db2 db change, then try below configuration..
db2set DB2_RESTRICT_DDF=TRUE

It worked for me..

Talkingto