Hi All,

I have  question about JDBC pooling in WebSphere 7. I have a JDBC pool set up connecting to oracle database 11.2, I have min/max set at 1/150 and ReapTime 180 and Unused TimeOut at 1800 Seconds and AgedTimeOut to 0(zero), PurgePolicy - Entire Pool.

If I understand correctly these setting should run a pool maintneance thread every 3 minutes and clean up any connections that are inactive for 30 minutes. What we have been seeing is that there are too many inactive connections (around 200) on the Database side that are not being cleaned up, should the application/websphere clean those up on database side, intermittently we are seeing them cleaned up on database side but cannot tell what is perfoming that.

Also is there a log with in WebSphere that can show that the unused timeout cleaned up idle connections. One last, is there a way to terminate a database connection if its hung or takes long (say 5 minutes)

Hi,

  By default you haven't this information in logs.

  You can get information about datasources, enabling next traces:
 
    *=info:WAS.j2c=all:RRA=all

  Maybe you can enable Oracle JDBC Driver tracing
 
  How to enable Oracle JDBC Driver tracing with WebSphere Application Server
  http://www-01.ibm.com/support/docview.wss?uid=swg21598616

  Have you review PMI console to see your pool behavior? how the pool grows and decreases.
 
  You can enable more JDBC statics
  JDBC connection pool counters
  http://www.ibm.com/support/knowledgecenter/SSAW57_7.0.0/com.ibm.websphere.nd.doc/info/ae/ae/rprf_datacounter2.html
 
  You can see Pool contents
   http://www-01.ibm.com/support/docview.wss?uid=swg21220832
    
  From DB point of view you have timeouts to close connections or trigers to close connections based on more data (source, user,...)
 
  Hope this helps. Tell us if you need more support
 
Regards
 

Thanks Gabriel.

What does the purge option on the websphere console do  Datasources->managestate-> purge. ? Does it only purge  inactive connections, similar to AdminControl.invoke(ds, "purgePoolContents", "normal")  when done through wsadmin. Is there an option to achieve the  AdminControl.invoke(ds, "purgePoolContents", "immediate") from the console.

Hi,

  I'm not sure. But seems that the purge option is "Normal". Having one default option from Admin Console you can't invoque "inmediate". But from Jconsole sure that yes because is an MBean or you can develop your custom MBean
 
  Extending the WebSphere Application Server administrative system with custom MBeans
  http://www.ibm.com/support/knowledgecenter/en/SSEQTP_7.0.0/com.ibm.websphere.express.doc/info/exp/ae/tjmx_extend.html
 
  Following the knowledge center only says purge contents of the connection pool
 
  JCA lifecycle management
  https://www.ibm.com/support/knowledgecenter/SSEQTP_7.0.0/com.ibm.websphere.nd.doc/info/ae/ae/udat_jcalifecycle.html
 

  Purge
  Specifies to purge the contents of the connection pool for the data source or connection factory that is specified. Purging the pool will not affect ongoing transactions.

  In this technote says that Normal is default option

  Purging the connection pool of a WebSphere Application Server data source using the data source MBean
  http://www-01.ibm.com/support/docview.wss?uid=swg21220832
   

  Normal:

• This is the default option.
• Existing in-flight transactions will be allowed to continue work.
• Shared connection requests will be honored.
• Free connections are cleaned up and destroyed.
• In use connections (for example: connections in transactions) are cleaned up and destroyed when returned to the connection pool.
• A close() call issued on any connection obtained prior to the purgePoolContents will be done synchronously (for example: wait for the jdbc driver to return before proceeding).
• Requests for new connections (not handles to existing old connections) will be honored.

  
  to confirm only can suggest to enable Command assistance to get launched wsadmin command when purge is clicked
 
  Command assistance simplifies administrative scripting in WebSphere Application Server
  http://www.ibm.com/developerworks/websphere/library/techarticles/0812_rhodes/0812_rhodes.html
 
  Hope this helps. Tell us if you need more support
 
Regards