I've gone thru all the threads on this forum w.r.t StaleConnectionException. The following is the situation that i face in my project:
Problem description -
We get this exception as there is a firewall b/w the appserver(Websphere 5) and the database server (Oracle 9i). The firewall is timing out the connection due to inactivity across it. Websphere still has this connection in the pool as we have the minPoolSize = 1. We have "EntirePool" purge policy in case of stale connections.
Attempted solution -
I tried changing the minPoolSize = 0 to see if i can avoid the situation. Of course, the trade-off here is that i get a fresh connection for the first time i try to access the DB. I have reasonable unused, aged timeouts (30 min each). I have the reap time = 3min for the pool maintenance thread. With these settings i no longer see the stale connection exception.
One more thing is that these settings for Websphere are on a demo box. Hence there is no production sort of load on the machine. Hence i could afford to go for a min poolsize = 0.
My question is,
Is this the right approach? Are there better ways to address the situation that i have at hand.
[ February 17, 2005: Message edited by: Jayadev Pulaparty ] [ February 17, 2005: Message edited by: Jayadev Pulaparty ]
We see the same exception for connections to DB2 and Oracle, without a firewall in-between the application server/database. It is intermittent and we�re unable to re-create it, I would be interested to know if the config changes you mentioned were a successful workaround for you.
yeah.. we were getting this staleexception only in Prod , not in Dev ot test environments.
Someone suggested to change "Disable auto connection cleanup" in teh datasource settings.. but our webmaster was not intersted to chnage the standard settings...
Other solution we followd is
get the connection.. test this connection whether this is "valid" or "stale"
basically, you can get the connection, execute a dummy SQL( something like get today's date from DB) , and see whether you are getting the resullt set or not. If you get it, it means that its a valid connection, else, it will throw Staleconnection exception.. so catch it.. make it null and try for a new connection again.. do that validations again..when you get a valid connection, you can send it to the applicaiton, where actually you need to execute your SQLs, stored procs etc..
With a Reap Time of 3mn, you may get StaleConnectionException randomly, try decrease this value and check your performances. There is a workaround with Websphere 5.1, under:Resources -> Manage Resource Adapters ->WebSphere Relational Resource Adapter -> CMP Connection Factories ->Custom properties->preTestSQLString from the administrative console. Let Websphere test for the validity of a connection before returning it to a caller Regards Albert
This is what we did. We set the "agedTimeout = 30min" and made sure that this value is less than firewall timeout. Also, agedTimeout is going to override the minConPoolSize settings. Hence all aged connections are cleanedup from the pool by the reaping thread.
I don't like that guy. The tiny ad agrees with me.