Topic
  • 6 replies
  • Latest Post - ‏2013-05-27T12:34:51Z by kunz soni
timschneider
timschneider
12 Posts

Pinned topic Return of the LOB error in DB2 Express-C V9.7

‏2009-07-02T10:18:36Z |
We have a JEE application running on Tomcat which uses Hibernate to access a database on DB2 Express-C. The database includes some LOBs.

Using the driver jars from DB2 Express-C V9.1, accessing data in DB2 Express-C V9.1, the application ran fine using a JDBC connection with no extra parameters.

Using the driver jars from DB2 Express-C V9.5, accessing data in DB2 Express-C V9.5, and using a JDBC connection with no extra parameters, the application gave LOB errors like "Invalid operation: Lob is closed. ERRORCODE=-4470, SQLSTATE=null;".

So, following advice at https://www.hibernate.org/120.935.html we changed the JDBC connection URI to:

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

Using the driver jars from DB2 Express-C V9.5, accessing data in DB2 Express-C V9.5, and using the above extra connection parameters, the application ran fine.

HOWEVER ... using the driver jars from DB2 Express-C V9.7, accessing data in DB2 Express-C V9.7, and with or without the above extra connection parameters, the LOB errors returned.

The error looks like this ...

Message:com.ibm.db2.jcc.am.SqlException: jcc1012011936http://3.57.82 Invalid operation: Lob is closed. ERRORCODE=-4470, SQLSTATE=null; nested exception is com.ibm.db2.jcc.am.SqlException: jcc1012011936http://3.57.82 Invalid operation: Lob is closed. ERRORCODE=-4470, SQLSTATE=null

BUT ... using the driver jars from DB2 Express-C V9.5, accessing data in DB2 Express-C V9.7, and with the above extra connection parameters, the application ran fine again.

Is there something different or wrong in the driver jars in DB2 Express-C V9.7 compared with V9.5? Do we need to use different connection parameters? And should we expect any bad effect from using the driver jars from DB2 Express-C V9.5 in the meantime?

Thanks in anticipation,
Tim.
Updated on 2010-11-08T18:36:23Z at 2010-11-08T18:36:23Z by PrachiSahoo
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: Return of the LOB error in DB2 Express-C V9.7

    ‏2009-09-21T23:21:14Z  
    Hi, we are experiencing the same problem and are also running in the same environment (J2EE app running in Tomcat using Hibernate and DB2 9.7 for persistence).

    We're currently working with IBM, and the parameters are correct (bar the spelling error you have for progressiveLocators) and we have confirmed that there is a bug in the DB2 9.7 JDBC driver.

    Their suggestion is that full materialization of blobs can be enabled at the server level by setting the DB2_RESTRICT_DDF to true. This can be done on the command line by the following.

    $ db2set DB2_RESTRICT_DDF=true
  • timschneider
    timschneider
    12 Posts

    Re: Return of the LOB error in DB2 Express-C V9.7

    ‏2009-09-22T09:15:18Z  
    Hi, we are experiencing the same problem and are also running in the same environment (J2EE app running in Tomcat using Hibernate and DB2 9.7 for persistence).

    We're currently working with IBM, and the parameters are correct (bar the spelling error you have for progressiveLocators) and we have confirmed that there is a bug in the DB2 9.7 JDBC driver.

    Their suggestion is that full materialization of blobs can be enabled at the server level by setting the DB2_RESTRICT_DDF to true. This can be done on the command line by the following.

    $ db2set DB2_RESTRICT_DDF=true
    Thanks for confirming the error and spotting the typo. I had begun to wonder whether I was imagining the problem if noone else was finding it:-).

    Did you find that the suggested fix worked without any side effects? At the moment we are about to release a new version of our software so I am a little wary about changing the driver and server parameter at the last moment.

    Cheers, Tim.
  • timschneider
    timschneider
    12 Posts

    Re: Return of the LOB error in DB2 Express-C V9.7

    ‏2009-09-22T09:25:44Z  
    Thanks for confirming the error and spotting the typo. I had begun to wonder whether I was imagining the problem if noone else was finding it:-).

    Did you find that the suggested fix worked without any side effects? At the moment we are about to release a new version of our software so I am a little wary about changing the driver and server parameter at the last moment.

    Cheers, Tim.
    Actually the typo in "progresssiveLocators" is a bit of an interesting issue. Every single reference I looked at had the "sss" in the middle so that does seem to be the accepted name for the parameter.

    Has everyone been using the wrong parameter, or is it correctly misspelled:-)?

    Cheers, Tim.
  • coolwinds
    coolwinds
    1 Post

    Re: Return of the LOB error in DB2 Express-C V9.7

    ‏2009-12-09T21:57:00Z  
    Problems may occur when retrieving LOB or XML data when the data size is larger than 32K after migrating to DB2 servers that support the progressive streaming interface. You may want to disable this feature so that LOB / XML data is processed the way it was before the progressive streaming interface was implemented.

    Disabling progressive streaming with CLI/JDBC applications FYI.

    http://www-01.ibm.com/support/docview.wss?uid=swg21329664
  • PrachiSahoo
    PrachiSahoo
    1 Post

    Re: Return of the LOB error in DB2 Express-C V9.7

    ‏2010-11-08T18:36:23Z  
    I did the registry leve change $ db2set DB2_RESTRICT_DDF=true as my attachment was not working .. After setting this parameter, its working fine.
    But by setting this parameter are any other places is the hole application hampered?
  • kunz soni
    kunz soni
    1 Post

    Re: Return of the LOB error in DB2 Express-C V9.7

    ‏2013-05-27T12:34:51Z  

    Hi all

    I am facing the same problem getBytes operation failing on Blob object,same application was running on DB2v9.1 and Portal 6.1.

    We migrated the application to DB29.7 and Portal 8.But the error "Lob is closed" results. I have tried  the above query parameters and the setting the DB2 property too but no joy. Please help.

    Thanks in advance,

    Kunal Soni