ObjectClosedException: DSRA9110E: ResultSet is closed

When you use WebSphere Application Server with an IBM DB2 database, this exception could occur:
Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
To avoid such exceptions, you must add custom properties in WebSphere Application Server at the Application Center data source level.
Procedure

• Log in to the WebSphere Application Server administration console.
• Select Resources > JDBC > Data sources > Application Center DataSource name > Custom properties and click New.
• In the Name field, enter allowNextOnExhaustedResultSet.
• In the Value field, type 1.
• Change the type to java.lang.Integer.
• Click OK.
• Click New.
• In the Name field, enter resultSetHoldability.
• In the Value field, type 1.
• Change the type to java.lang.Integer.
• Click OK and save your changes.

The possible values of resultSetHoldability are:
1 (HOLD_CURSORS_OVER_COMMIT),
2 (CLOSE_CURSORS_AT_COMMIT).

DB2, SQLState: 56038, Error Code -4743, -4700

Functions that this release of DB2 introduces cannot be used before new function mode has been enabled. An attempt was made to execute one of these functions. In addition, support for extending the length of a VARCHAR (supported in V7) is restricted in V8 until new function mode has been enabled.


This error you might get when your project was compiled, configured and deployed using webshpere version 7 and you are trying to compile and deploy in websphere 8.5

To solve the issue, create and map new jdbc connection from ear deployment descriptor.

Ear project->Deployment descriptor->deployment tab-> remove all the existing jdbc configuration and configure the jdbc and jndi mapping again with DB2.

DB Connection issue, StaleConnectionException: null DSRA0010E: SQL State = 56038, Error Code = -4,743

StackTrace:

aused by: com.ibm.websphere.ce.cm.StaleConnectionException: null DSRA0010E: SQL State = 56038, Error Code = -4,743

       at com.ibm.ws.rsadapter.spi.ServerFunction.handleStaleStatement(ServerFunction.java:695)

       at com.ibm.ws.rsadapter.AdapterUtil.mapException(AdapterUtil.java:2279)

       at com.ibm.ws.rsadapter.AdapterUtil.translateSQLException(AdapterUtil.java:1584)

       at com.ibm.ws.rsadapter.spi.WSRdbDataSource.getConnection(WSRdbDataSource.java:2374)

       at com.ibm.ws.rsadapter.spi.WSManagedConnectionFactoryImpl.getConnection(WSManagedConnectionFactoryImpl.java:1782)

       at com.ibm.ws.rsadapter.spi.WSManagedConnectionFactoryImpl.createManagedConnection(WSManagedConnectionFactoryImpl.java:1551)

       at com.ibm.ws.rsadapter.spi.WSManagedConnectionFactoryImpl.createManagedConnection(WSManagedConnectionFactoryImpl.java:1107)

       at com.ibm.ejs.j2c.FreePool.createManagedConnectionWithMCWrapper(FreePool.java:2160)

       at com.ibm.ejs.j2c.FreePool.createOrWaitForConnection(FreePool.java:1838)

       at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:3809)

       at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:3085)

       at com.ibm.ejs.j2c.ConnectionManager.allocateMCWrapper(ConnectionManager.java:1548)

       at com.ibm.ejs.j2c.ConnectionManager.allocateConnection(ConnectionManager.java:1031)

       at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:644)

       at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:611)

       at com.workbrain.tool.jdbc.safe.SafeDataSource.getConnection(SafeDataSource.java:52)

       at com.workbrain2.platform.tool.jdbc.proxy.ProxyDataSource.getConnection(ProxyDataSource.java:96)

       at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)

       at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)

       ... 138 more

---- Begin backtrace for Nested Throwables

java.sql.SQLException: null DSRA0010E: SQL State = 56038, Error Code = -4,743

       at com.ibm.db2.jcc.am.gd.a(gd.java:752)

       at com.ibm.db2.jcc.am.gd.a(gd.java:66)

       at com.ibm.db2.jcc.am.gd.a(gd.java:135)

       at com.ibm.db2.jcc.am.so.c(so.java:2763)

       at com.ibm.db2.jcc.am.so.d(so.java:2751)

       at com.ibm.db2.jcc.am.so.a(so.java:2200)

       at com.ibm.db2.jcc.am.so.a(so.java:2176)

       at com.ibm.db2.jcc.t4.ab.h(ab.java:136)

       at com.ibm.db2.jcc.t4.ab.b(ab.java:41)

       at com.ibm.db2.jcc.t4.o.a(o.java:32)

       at com.ibm.db2.jcc.t4.tb.i(tb.java:145)

       at com.ibm.db2.jcc.am.so.ib(so.java:2169)

       at com.ibm.db2.jcc.am.so.a(so.java:3248)

       at com.ibm.db2.jcc.am.so.a(so.java:691)

       at com.ibm.db2.jcc.am.Connection.getJccSpecialRegisterProperties(Connection.java:8123)

       at com.ibm.db2.jcc.am.hf.getJccSpecialRegisterProperties(hf.java:1044)

       at com.ibm.ws.rsadapter.dbutils.impl.DB2UniversalUtilityImpl.getJccSpecialRegisterProperties(DB2UniversalUtilityImpl.java:386)

       at com.ibm.ws.rsadapter.spi.InternalDB2UniversalDataStoreHelper.addDefaultHPExtendedProperties(InternalDB2UniversalDataStoreHelper.java:2046)

       at com.ibm.ws.rsadapter.spi.WSRdbDataSource.getConnection(WSRdbDataSource.java:2322)

       at com.ibm.ws.rsadapter.spi.WSManagedConnectionFactoryImpl.getConnection(WSManagedConnectionFactoryImpl.java:1782)

       at com.ibm.ws.rsadapter.spi.WSManagedConnectionFactoryImpl.createManagedConnection(WSManagedConnectionFactoryImpl.java:1551)

       at com.ibm.ws.rsadapter.spi.WSManagedConnectionFactoryImpl.createManagedConnection(WSManagedConnectionFactoryImpl.java:1107)

       at com.ibm.ejs.j2c.FreePool.createManagedConnectionWithMCWrapper(FreePool.java:2160)

       at com.ibm.ejs.j2c.FreePool.createOrWaitForConnection(FreePool.java:1838)

       at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:3809)

       at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:3085)

       at com.ibm.ejs.j2c.ConnectionManager.allocateMCWrapper(ConnectionManager.java:1548)

       at com.ibm.ejs.j2c.ConnectionManager.allocateConnection(ConnectionManager.java:1031)

       at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:644)

       at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:611)

       at com.workbrain.tool.jdbc.safe.SafeDataSource.getConnection(SafeDataSource.java:52)

       at com.workbrain2.platform.tool.jdbc.proxy.ProxyDataSource.getConnection(ProxyDataSource.java:96)

       at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)

       at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)

       at com.workbrain2.platform.util.WbDataSourceUtil.getConnection(WbDataSourceUtil.java:74)

       at com.workbrain2.platform.svc.DbServer.impl.DbServerImpl.init(DbServerImpl.java:63)

       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:95)

       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)

       at java.lang.reflect.Method.invoke(Method.java:620)

       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1702)

       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1641)

       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1570)

       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:539)

       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:476)

       at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:303)

       at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)

       at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:299)

       at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:194)

       at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:755)

       at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:757)

       at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:480)

       at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:139)

       at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:93)

       at com.workbrain2.platform.core.context.WbClassPathXmlApplicationContext.<init>(WbClassPathXmlApplicationContext.java:39)

       at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

       at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:86)

       at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:58)

       at java.lang.reflect.Constructor.newInstance(Constructor.java:542)

       at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:147)

Solutions: Change the value for the property validateNewConnection to : true. Stop and restart the server.

DB2 Some useful tips

Check view Definition/Contents:

select name, cast(statement as  varchar(8000)) from SYSIBM.SYSVIEWS where name={VIEW_NAME} for read only;

DB2 Dummy table: sysibm.sysdummy1

DB2 optimize query to control result set:

- with ur for read only
- fetch first n rows only
- optimize for 1 row