Hi All,
We have a JDBC connection to DB2 with the following configuration
Transaction Type: LOCAL_TRANSACTION
DataSource Class : COM.ibm.db2.jdbc.DB2DataSource
Enable Connection Pooling true
Minimum Pool Size 1
Maximum Pool Size 10
Pool Increment Size 1
Block Timeout (msec) 1000
Expire Timeout (msec) 1000
Startup Retry Count 0
Startup Backoff Timeout (sec) 10
Our DBA’s are complaining that web-methods server is keeping connection ideal. It seems the adapters is not releasing the connection once it is done. rather creating new connection there by maximizing the connection to the DB.
I have checked the ART package to the stats and it showed just 1 connection being used and 1 is busy. At same time DBA informed there were 300 are there and ideal. Is there any fix or a solution for this.
Here are some settings that should improve the interaction to the DB.
Minimum Pool Size: 0 – in production, this should normally be 0. This avoids errors due to stale connections. Having this set to 1 is why “It seems the adapters is not releasing the connection once it is done.” You configured the pool to keep one at all times. Also, it can have up to 10 connections in the pool. If you don’t want a pool, disable pooling.
Maximum Connections: N – you’ll want to tweak this for your environment. 10 may be a good start, depending upon load, activity, etc.
Block Timeout (msec): 60000 – 1000 (1 second) is far too short. This controls how long a thread in IS will wait for connection from the pool when IS is busy.
Expire Timeout (msec): (set to 1 - 5 minutes less than the DB timeout setting) – 1000 (1 second) is far, far too short. This can cause session thrashing on the DB. You want IS to timeout the connection before the DB does so that IS doesn’t try to use a stale connection (which will cause errors in your integration).