How to configure WebSphere data source parameters when a firewall exists between application server and data source

Resolving The Problem

To avoid dropped connections and StaleConnectionExceptions when a firewall is present between an application server and a database, set the following connection pool properties on the data source:

Minimum Connections = 0
Unused Timeout = half the firewall timeout value (in seconds)
Reap Time = 60 seconds
Purge Policy = EntirePool

The goal is for WebSphere Application Server to remove idle connections from the connection pool before the firewall timeout causes the connections to drop. The Unused Timeout is the number of seconds that a connection can be unused or idle in the pool before it is removed. By reducing the Unused Timeout to a value less than the firewall timeout, WebSphere Application Server will remove connections before the firewall. The Reap Time is the number of seconds between runs of the pool maintenance thread, which removes connections that have reached the Unused TImeout. Reducing the Reap Time from its default value of 180 seconds to 60 seconds will result in unused connections being removed more quickly.

It is also important to set Minimum Connections to 0. Any value above 0 means that WebSphere Application Server is required to keep that many connections in the pool even after the connections have reached the Unused Timeout. As a result, those connections will remain in the pool until they are dropped by the firewall.

The Purge Policy should remain at its default value of EntirePool. This means that when a connection in the pool is dropped and a StaleConnectionException does occur, all of the connections in the pool will be purged. This allows an application to recover more quickly from stale connections.

Finally, it is recommended that all applications that get connections from a WebSphere Application Server catch the StaleConnectionException and retry the getConnection() call when it occurs. By doing this, applications can seamlessly recover from stale connections without any impact to the end users of the application.