When a StaleConnectionException occurs, the application server's SystemOut.log or HPEL log will contain something like this:

Caused by: com.ibm.websphere.ce.cm.StaleConnectionException: IO Error: The Network Adapter could not establish the connection DSRA0010E: SQL State = 08006, Error Code = 17,002

Typically, a StaleConnectionException occurs when there is a request to connect to a database and:

• the connection has timed out
• the connection has been dropped by the database or firewall
• there is some issue with the database (e.g database is down, database failure, idle session on the database)

When setting up a data source, one of the settings is helper class. In this example, the data source is usng the generic helper class com.ibm.websphere.rsadapter.GenericDataSourceHelper. Each helper class contains error codes for fatal exceptions. When a fatal exception is detected, the fatal exception will be mapped to StaleConnectionException. This means that a StaleConnectionException can be caused by a number of different fatal exceptions.

In the WebSphere admin console, at Resources > JDBC > Data sources > your data source > Connection pool properties, minimum connection will be 0. This is the preferred setting. Let's say you set the minimum to 5. Once the connection pool starts to establish connections, then 5 connection will remain in the pool. This can produce a situation where the connection times out. Then, there is a request to use the timed out connection will would return StaleConnectionException.

By default, reap time will be less than unused timeout. This is also the preferred setting. Reap time is how often the maintenance thread will occur. You want the maintenance thread to detect an unused timeout, which is why you want reap time to be less than unused timeout.

The purge policy tells WebSphere what to do when a stale connection or fatal connection error is detected. 

With EntirePool, all database connections are destroyed, so connections that are needed will need to be reestablished to the database. There is a possibility that some valid connection may be mistakenly closed, but this is unlikely.

With FailingConnectionOnly, only database connections that have the StaleConnectionException are closed, which eliminates the possibility of closing a valid connection. There are some possible issues with this, such as subsequent connections be mistakenly flagged as stale instead of valid. For this reason, EntirePool is usually the preferred option.

Changes made to the purge policy may improve the response time of an application.

Handling stale exceptions in Java

Stale exceptions can also be handled in Java, which allows an application to quickly recover from a StaleConnectionException.

catch(java.sql.SQLException sqlX)
 {
   // If StaleConnectionException is detected
   if (com.ibm.websphere.rsadapter.WSCallHelper
       .getDataStoreHelper(ds)
       .isConnectionError(sqlX))
   {
     // Do something here to address the stale connection
   }
  }