Hi All,
In my project from 7.1 IS we are connecting to DB using JDBC adapter and selects data.
But after the operation is completed the connection is not getting closed automatically and DB team is able to see inactive session in DB from wM .
The connection parameters are:
Transaction Type NO_TRANSACTION
DataSource Class oracle.jdbc.pool.OracleDataSource
networkProtocol TCP
Other Properties driverType=thin;connectionProperties={includeSynonyms=true}
Connection Management Properties
Enable Connection Pooling true
Minimum Pool Size 0
Maximum Pool Size 10
Pool Increment Size 1
Block Timeout (msec) 1000
Expire Timeout (msec) 1000
Startup Retry Count 3
Startup Backoff Timeout (sec) 10

I tried with LOCAL_TRANSACTION also but same issue.

Do we need to explicitly drop the connection object when using adapter service? Or need to change any IS parameters for this?

Thanks in advance!!
ANS

This sounds like normal behavior for connections from a JDBC pool in IS or any application server. One of the goals of a pool is to maintain connections to the resource (DB in this case) so that the overhead of creating new connections is avoided on each use of the resource.

You can configure the minimum and maximum settings in the pool using the IS Administrator.

Mark

Thank you Mark…

Is it possible configure the adapter so that the connection object will be dropped after an accepted period of inactivity(say 1 hour) ? In previous case DB team is able to find the session even after few hours.

ANS

Again, presence of inactive sessions from an app server JDBC pool is normal and not an error condition. That’s how connection pools work.

If you study the JDBC Adapter User’s Guide you can probably determine how best to address this non-error. Perhaps by setting minimum connections to 0 or something similar.

Mark

Here the the “Minimum Pool Size” is already set to 0 and the Expire Timeout is 1000(msec). So the connection pool should remove the inactive connection based on Expire Timeout until the number of connections reaches “Minimum Pool Size” , which is 0. In this case the connction is not getting removed from the pool.How can we make it removed from the pool?

Thank you…
ANS[FONT=Arial][SIZE=3][FONT=Arial][SIZE=3]
[/size][/font][/SIZE][/FONT]

ANS
The JDBC Adapter is behaving as expected as Mark C. said. Are you DBA’s giving you a hard time about this? The inactive session(s) does not cause issues on the Database side.

Yes it is, just not necessarily going to show itself as a dropped session on the DB side. You are not going to end up with thousands of inactive sessions in Oracle if that is the fear. Perhaps doing some serious volume load tests to help ease the natural ever present trepidation that DBA’s have.

Thank you for the advice griffima and Mark …

ANS

Hi All,
I have the same issue with DB2 database, i have the min conn pool as 0 and max 5.
connections are not getting closed. They were reaching max on DB2 end. In order to close these connections we are bouncing the server every time. we see this issue on 6.5, 712, 8.2. i’m on JDBC fix 30. How can we fix this issue.

Thanks in Advace.

Sandy

SandeepKumar - If your jdbcServices are hanging, it may be they are “long running” or failed sql’s, and do not have a queryTimeout specified.

I had a similar situation today where a db failed/disappeared and IS hung due to maxed out thread pools. Not sure why it maxed IS threads, but viewing system threads revealed all associated jdbcServices executing at time of db failure had “hung”, essentially killing IS requiring bouncing.

6-5_jdbc_adapter_user_guide - page 50:
Forcing a Timeout During Long-Running SQL Operations in Services and Notifications
In the JDBC Adapter services or notifications, some of the SQL operations may take a long time to execute. You can force these services or notifications to time out after a specific amount of time. You specify the number of seconds with the watt.adapter.JDBC.QueryTimeout property.

The watt.adapter.JDBC.QueryTimeout is the global server setting for queryTimeout, however each jdbcService can have an individual setting for this… In a jdbcService look for the “Query Time Out” field, if it’s set to “-1” it’s using the global value, or you can override the global by specifying a seconds value here.

This should mean you no longer have to min pool size to 0.

Good info about the global setting.

Recommendations from my perspective:

• Set the global value as a safety net but don’t rely solely on it.

• Set the timeout value for ALL ART services when possible. It is the rare case that a service should wait forever (timeout of 0).

• Min pool size should be 0. IME, there is no need for the pool to hold on to idle connections. The reason for having a pool of idle connections is to be able to quickly handle a ramp up in traffic. For the majority of integrations, the additional time used to establish connections is usually immaterial. Dealing with the errors resulting from stale connections is more troublesome than slowing down unattended integrations by a few seconds.

Yep agreed on all things man, with very enthusiastic thumbs-up.

I must admit, in the eagerness for end-of-year leave my system still has min connections = 1, but there definitely is a re-assuring level of absolute security with min = 0… Guaranteeing yourself no call-ins on much needed leave, yeah, I’m up for some of that, especially with an execution-overhead payoff of bugger-all. Definitely agree with safety nets, and min = 0 is a big one.

Cheers reaman