Hi All, We are using the WAS 6.1 app server(Cluster environment) and oracle database(10g). In the connection pool configuration we gave Minimum No of connection as 2 and Maximun No of connections as 30 with Connection time out parameter as 180. The problem is the connectivity between the appserver and the DB is getting disconnected some times and we are getting the following exception in the SystemErrlog. 2/7/09 22:08:33:877 GST 0000003a SystemErr R org.hibernate.exception.GenericJDBCException: Cannot open connection at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateC onverter.java:103) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43 ) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29 ) at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:420) at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:1 05) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1561) at org.hibernate.loader.hql.QueryLoader.iterate(QueryLoader.java:392) at org.hibernate.hql.ast.QueryTranslatorImpl.iterate(QueryTranslatorImpl.java:318) at org.hibernate.engine.query.HQLQueryPlan.performIterate(HQLQueryPlan.java:177) at org.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1156) at org.hibernate.impl.QueryImpl.iterate(QueryImpl.java:46) Caused by: com.ibm.websphere.ce.cm.StaleConnectionException: Io exception: The Network Adapter could not establish the connectionDSRA0010E: SQL State = null, Error Code = 17,002 at sun.reflect.GeneratedConstructorAccessor136.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructor AccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:522) at com.ibm.websphere.rsadapter.GenericDataStoreHelper.mapExceptionHelper(GenericDa taStoreHelper.java:523) at com.ibm.websphere.rsadapter.GenericDataStoreHelper.mapException(GenericDataStor eHelper.java:578) at com.ibm.ws.rsadapter.AdapterUtil.mapException(AdapterUtil.java:2041) at com.ibm.ws.rsadapter.spi.WSRdbDataSource.getPooledConnection(WSRdbDataSource.ja va:1189) at com.ibm.ws.rsadapter.spi.WSManagedConnectionFactoryImpl.createManagedConnection (WSManagedConnectionFactoryImpl.java:1055) at com.ibm.ejs.j2c.FreePool.createManagedConnectionWithMCWrapper(FreePool.java:181 1) at com.ibm.ejs.j2c.FreePool.createOrWaitForConnection(FreePool.java:1543) at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:2215) at com.ibm.ejs.j2c.ConnectionManager.allocateMCWrapper(ConnectionManager.java:896) at com.ibm.ejs.j2c.ConnectionManager.allocateConnection(ConnectionManager.java:596 ) at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java: 431) at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java: 400) at org.hibernate.connection.DatasourceConnectionProvider.getConnection(DatasourceC onnectionProvider.java:69) at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:417) ... 42 more Can any one tell me how to resolve this error. Thanks & Regards, Cva.

We also face this issue and going through IBM documentation they suggest to try and establish the connection again. So we have built 3 retries in our application framework for this specific exception. We have seen this exception happens when connection is idle for long time, and usually the 1st retry resolves this..

thank's rajiv for u r reply, There are different scenarios which this exception will occurs.Those are StaleConnectionException: This exception (com.ibm.websphere.ce.cm.StaleConnectionException) indicates that the connection currently being held is no longer valid. This can occur for numerous reasons, including the following: 1 The application tries to get a connection and fails, as when the database is not started. 2 A connection is no longer usable due to a database failure. When an application tries to use a connection it has previously obtained, the connection is no longer valid. In this case, all connections currently in use by an application could get this error when they try to use the connection. 3 The application using the connection has already called close() and then tries to use the connection again. 4 The connection has been orphaned because the application had not used it in at most two times the orphan timeout; then the application tries to use the orphaned connection. 5 The application tries to use a JDBC resource, such as a statement, obtained on a now-stale connection. U metioned that "we have built 3 retries in our application framework" .can u please explain me how u build those retries...........

In our db access code where we create preparedStatement / statement from connection and execute query , we are specifically catching the StaleConnectionException, if we get that exception, we get the fresh connection and again try to create statement / execute query from the fresh connection... this is in a loop with max iterations as 3 (configurable)

Thanks Rajiv. The issue had been solved..there is some problem in the network connectivity between appserver and dbserver which has been solved by the network team......now i am not getting the error....

Hello Sivakumar, do you mind posting your network problem/solution? I have the same issue here, when I shutdown my main database for a maintenance work and my secondary becames primary I need to restart the app server to make it see the new one! I just got this issue, I need to check a few more things, but if you could post your context/solution I definitely would appreciate. thanks! Claudio

Hi, we had similar problems. we connect to DB2 and had set min conn pool to 1 originally and on some of our long running processes we'd get null or missing connection. we met with IBM and they suggested setting min connections to 0. they thought that the db was timing out but WAS was keeping the connection open. so next use of connection failed. we set min connections to 0 and it fixed our problem. not sure if this is the same issue as yours but it is easy enough to try. good luck.