Re: [dbcp] db connection pooling under firewall

[Dirk Verbeeck]

Hi Philip

* Solution 1:
DBCP has the requested feature. It is not included in v1.0 so you need a 
nightly build.
The parameters you are looking for are:
(A) timeBetweenEvictionRunsMillis: The number of milliseconds to sleep 
between runs of the idle object evictor thread.
(B) numTestsPerEvictionRun: The number of objects to examine during each 
run of the idle object evictor thread (if any).
(C) minEvictableIdleTimeMillis : The minimum amount of time an object 
may sit idle in the pool before it is eligable for eviction by the idle 
object evictor (if any).

Every "A" milliseconds, "B" idle connections are tested, if they are 
idle for more then "C" milliseconds then they will be removed from the pool

Typical values are:
(A) timeBetweenEvictionRunsMillis: 60000   (default is disabled)
(B) numTestsPerEvictionRun: 3     (=default)
(C) minEvictableIdleTimeMillis : 1800000  (=1000 * 60 * 30)  (=default)
* Solution 2:
You can also prevent that your firewall from dropping the connection 
using a oracle technique.
SQL*Net has a parameter which defines time interval to send a probe 
message to identify if the client process is still alive
- SQLNET.EXPIRE_TIME, sqlnet.ora file on the server side. 
SQLNET.EXPIRE_TIME = <your_value>
Set it to something like 30 when your firewall drops connection after 60 
minutes.
See: 
http://download-west.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/params.htm#437583

We use (2) in our server environment and it works perfectly.

Of couse if there is a chance thay your network goes completely down 
then (1) will recover that case as well.
But in that case you will probably want to turn on the DBCP 
testOnBorrow/validationQuery feature.

Regards
Dirk
Phillip Qin wrote:

I know this is a very popular topic. But I have searched through tomcat and
commons mailing list and didn't find a satisfactoried answer regarding
connection pooling working in firewalled environment.
The symptom is:

There is a timeout setup in the firewall, checkpoint. When any stateful
connection idles for a period, say 60 minutes, firewall drops the
connection. After connection is dropped, I checked both oracle and linux
server.
- netstat tells me the connection is established;
- oracle console tells me the connection is established.
When my webapp tries to obtain a connection from the pool and this
connection happens to be the idle connection cut off by firewall, it hangs.
By looking into the oracle log, I can see that oracle throws "Io exception:
Connection timed out". Obviously, this long-await is because my webapps
thinks the connection is established and it tries to query the database.
My question is how to setup dbcp properly to keep connection alive? My
environment is
Tomcat 4.1.24 + Tomcat JNDI Data Source and Pooling (commons-dbcp)
Oracle 9i
Debian 3.0
Configuration in context is:

        <parameter>
                <name>driverClassName</name>
                <value>oracle.jdbc.driver.OracleDriver</value>
        </parameter>
        <parameter>
                <name>maxActive</name>
                <value>10</value>
        </parameter>
        <parameter>
                <name>maxIdle</name>
                <value>3</value>
        </parameter>
        <parameter>
                <name>maxWait</name>
                <value>15000</value>
        </parameter>
        <parameter>
                <name>removeAbandoned</name>
                <value>true</value>
        </parameter>
        <parameter>
                <name>validationQuery</name>
                <value>SELECT COUNT(*) FROM DUAL</value>
        </parameter>
Regards,



PQ

Going to war for peace is like having sex for virginity



 





---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]