2

Database: db2, Application Server: websphere 8

We have rest service that updates/retrieves values in/from database. This service is used at most twice a day. So time period between the rest calls is almost 24 hours.

For every first/second request, it is throwing staleConnection exception. Third retry goes through.

Did everything that is specified in below links.

How to handle stale connections?

http://pic.dhe.ibm.com/infocenter/wasinfo/v6r1/index.jsp?topic=/com.ibm.websphere.nd.multiplatform.doc/info/ae/ae/tdat_pretestconn.html

http://www.thejavacode.com/websphere-adapters-stale-connection-problem-t34.html

But still getting staleConnection exception. We are using Spring JDBC, so I don't see issue of not closing connections after use. In addition, I wrote retry logic within a single request to try getting valid connection for three times when it encounters connection exception.

When staleconnection exception is encountered first time, service waits three seconds and retries connection, but subsequent retries is throwing

ObjectClosedException: DSRA9110E: Connection is closed.

I doubt subsequent retries are trying to open existing stale connection. How to resolve this issue?

My apologies if I included wrong tags.

Community
  • 1
  • 1
pavanlapr
  • 279
  • 1
  • 5
  • 15

3 Answers3

3

The #1 solution is: Do not cause connections to go stale. There is nothing in the DB2 drivers or server to cause that to happen. 99% of stale connections are caused by broken firewalls that have TCP timeouts. Fix your firewall product's timeout settings for the used network route.

The #2 solution is:

  • Set the pretesting as per the link you provided. Make sure your SQL query is valid (for example SELECT current date FROM sysibm.sysdummy1)
  • Set the reap, unused, and aged timeouts at the pool settings to destroy unused connections after certain time. (If the amount drops below minimum for pool, new ones will be established immediately)
  • Set the purge policy to EntirePool. However notice that this will mean StaleConnectionException at one connection from pool will force the WAS to drop everything from the pool and create new connections to fulfill the minimum amount requirement. This might cause performance problems.
user918176
  • 1,770
  • 13
  • 34
  • I don't want to go with 2nd solution where Websphere has to pretest every connection. I believe this will create unnecessary onus on websphere. Instead of that, I came up with below solution, let me know your thoughts: I implemented ServletContextListener that will start a thread. This thread will perodically (15 min) queries simple table. This thread will be stopped when listener is destroyed. Below link has similar code: http://stackoverflow.com/questions/10961714/how-to-properly-stop-the-thread-in-java. For now, this solution is working and I don't see any staleconnections exceptions. – pavanlapr Mar 13 '13 at 17:58
  • @user1803687 That is one approach.. However if you set the reap, unused, and aged timeouts you get the same by filling in 3 numbers on the administrative console... – user918176 Mar 14 '13 at 15:19
1

I agree with answer (user918176), just wanted to add another simple setting that solves the problem, for sake of completeness; Setting minimum connection pool size to zero.

StaleConnectionException usually occurs after a period of inactivity (like first requests in the morning, of after lunch break), during which some network device terminates underlying TCP connection.

Setting minimum pool size to zero causes all old connections to be discarded after a rather short period (after unused timeout, which is by default 1800 seconds after a connection is last used). So after inactivity period there are simple no connections in the pool (which would otherwise be stale). This solution causes minimal configuration changes and minimal performance hit (only first requests after inactive period wait for db connection to be re-initialised instead of getting a ready-to-use connection).

Kurtcebe Eroglu
  • 1,934
  • 11
  • 15
0

Tune Minimum connection pool to 0, Default will be 1.

Saikiran
  • 140
  • 14