Hi all,
I'm trying to convert an enterprise application from using application-managed connection pooling to WAS connection pooling, and I've run into a stubborn issue.  I've defined two data sources -- the application uses two different databases on the same server -- and a connection pool in WAS, and 'test connection' works fine from the admin console.

I've also defined a pair of resource-ref entries for the data sources, and I get a prompt for the bindings for those when I deploy the application.  However, when I run the application, every attempt to use a connection results in an exception:

'java.sql.SQLRecoverableException: DSRA9110E: Statement is closed'

This is caught at the source and re-thrown as an application exception, so there's not a lot in the stack trace that would shed more light.  I've tried many different configuration options on the data sources, but I haven't been able to get past this.  Any ideas?

Have you tried setting Min Connection Pool size  to 0?

If yes, and if your database is Oracle, may be you need to apply patch on database side. Google will help.

Thanks.

Hi tousi,

Thanks for the suggestion; yes, I've set 'Minimum connections=0' on both data sources -- but the database is SQL Server, not Oracle.

I've also enabled tracing on the WAS server and tried running the application, and I can see in the log that it's getting a connection, and at least attempting to create a PreparedStatement.  It also looks like it's calling executeQuery, and returning from that.  I don't see anything going obviously wrong until it reports the same exception I've been seeing in the SystemOut log:

[5/19/14 20:28:20:077 UTC] 0000001b SystemOut     O   2014-05-19 20:28:20,075 [WebContainer : 1] ERROR CompositeUpsMediatorImpl  - Unable to Retrieve User

Session Service Result (SQL Exception)
java.sql.SQLRecoverableException: DSRA9110E: Statement is closed.
    at com.ibm.ws.rsadapter.jdbc.WSJdbcWrapper.createClosedException(WSJdbcWrapper.java:110)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.runtimeXIfNotClosed(WSJdbcStatement.java:1821)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.setString(WSJdbcPreparedStatement.java:2646)

However, I could easily be missing something that would be obvious to someone who is an expert in reading the trace logs.

Hi Sje

  Without see all the trace i can't advise better but this trace need to be reviewed

..."window.parent.tinyMCE.get('post_content').onLoad.dispatch();" contenteditable="true" />[5/19/14 20:28:20:077 UTC] 0000001b SystemOut     O   2014-05-19 20:28:20,075 [WebContainer : 1] ERROR CompositeUpsMediatorImpl  - Unable to Retrieve User

please review your J2C authentication alias.

  With test connection you check the connectivity with database not that the user used inside the pool is correct.

  Hope this helps.  Tell us if you need more support

regards

Hi Gabriel,

That message is a little misleading due to the way the line was broken when I pasted in the log excerpt.  The full message is actually "Unable to Retrieve User Session Service Result (SQL Exception)" -- and that comes from the application.  It's just the application's interpretation of the exception based on where it occurred.

Is there a way to upload the trace here somehow?  It's not huge -- about 4 MB -- so it would zip down to a very reasonable size.

Hi all,
I've made some progress with this, so I thought I would post an update. Apparently the original error was due to our use of PreparedStatements that are declared at the class level, and then initialized once at the method level before they're used for the first time -- caching them in the class, essentially.

After I went through and moved all of those declarations to the method level, the application started *trying* to work -- much of the process flow is now working nicely. However, I'm still getting failures I can't explain -- and they all appear to trace back to a 'statement is closed' or a 'connection is closed' error.  In several cases, the exceptions seem to stem from a ResultSet.next() call, as in this snippet:

                ResultSet rs = upsXmlQueryStmt.executeQuery();
                if (rs != null && rs.next()) {
                    upsXml = rs.getString(1);
                }

Any ideas?

Sample stack trace (this particular exception wasn't preceded by either 'Statement is closed' or 'Connection is closed'):

[6/2/14 17:22:50:562 UTC] 00000023 WebServiceExc E org.apache.axis2.jaxws.WebServiceExceptionLogger log A java.lang.NullPointerException throwable was caught.  The detail message is:     at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.next(WSJdbcResultSet.java:3116)
    at com.ibm.spa.os.db2.mediator.function.CurrentDocumentLocatorFunction.execute(CurrentDocumentLocatorFunction.java:63)
    at com.ibm.spa.os.db2.mediator.Db2QueryApiImpl.getCurrentDocument(Db2QueryApiImpl.java:363)
    at com.ibm.spa.bp.soi.SpaProviderRouterImpl.checkSearchResultProgress(SpaProviderRouterImpl.java:173)
    at com.ibm.spa.bp.soi.SpaProviderRouterImplDelegate.checkSearchResultProgress(SpaProviderRouterImplDelegate.java:34)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
    at java.lang.reflect.Method.invoke(Method.java:611)
    at org.apache.axis2.jaxws.server.dispatcher.JavaDispatcher.invokeTargetOperation(JavaDispatcher.java:100)
    at org.apache.axis2.jaxws.server.dispatcher.JavaBeanDispatcher.invoke(JavaBeanDispatcher.java:117)
    at org.apache.axis2.jaxws.server.EndpointController.invoke(EndpointController.java:111)
    at org.apache.axis2.jaxws.server.JAXWSMessageReceiver.receive(JAXWSMessageReceiver.java:161)
    at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:189)
    at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:275)
    at com.ibm.ws.websvcs.transport.http.WASAxis2Servlet.doPost(WASAxis2Servlet.java:1431)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:738)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1658)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:940)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:503)
    at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:181)
    at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:91)
    at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:875)
    at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1592)
    at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:186)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:453)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:515)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:306)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:277)
    at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminators(NewConnectionInitialReadCallback.java:214)
    at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:113)
    at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:165)
    at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
    at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
    at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
    at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
    at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
    at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
    at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1646)