IBM Purging the connection pool of a WebSphere Application Server data source using the data source MBean - United States was app server TECHNOTE (TROUBLESHOOTING)

PROBLEM(ABSTRACT)
 During the testing or troubleshooting of an enterprise application, it may become necessary to purge the connection pool of a WebSphere Application Server data source of existing connections. 

SYMPTOM
Changes to the application or environment may invalidate cached connections in the Connection Manager pool. Although these connections will eventually be discarded through the normal processing of the connection requests, it may be desirable to purge them all at once and allow the pool to refill with new, valid connections.


RESOLVING THE PROBLEM
WebSphere Application Server has an operation on the data source MBean that can be used to purge the connection pool. WebSphere Application Server MBean may be called via the wsadmin console, see the IBM Information Center topic "Scripting the application serving environment (wsadmin)" for more details. The operation name is: purgePoolContents. 

The purgePoolContents operation has two options: 

 1. 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.
       
       
    
    
 2. Immediate:  * No new transactions will be allowed to start on any connections obtained prior to the purgePoolContents call. Instead, a StaleConnectionException is thrown. 
     * No new handles will be handed out on any connections obtained prior to the purgePoolContents call. Instead, a StaleConnectionException is thrown. 
     * Existing in-flight transactions will be allowed to continue work. Any new activities on a purged Connection will cause a StaleConnectionException or an XAER_FAIL exception (in the case of XA) 
     * A close() call issued on any connection obtained prior to the purgePoolContents call will be done asynchronously (for example: no waiting for the jdbc driver to return) This is useful if the driver returns. 
     * Due to the fact that close() does not wait, the number of connections will be decremented immediately in WebSphere Application Server. This might cause the total number of connections in WebSphere Application Server to be temporarily out of sync with the database total number of connections. 
     * Requests for new connections (for example: not handles to existing old connections) will be honored.
    
    


Examples of operations that can be executed on the data source MBean using the wasdmin console:  1. List all data sources:
    Using Jacl:
    $AdminControl queryNames *:type=DataSource,*
    
    Using Jython:
    AdminControl.queryNames("*:type=DataSource,*")
    
    
 2. Initialize a variable to specify a data source (this example uses Default Datasource, but any data source name listed in the output of above command can be used):
    Using Jacl:
    set name "Default Datasource"
    set ds [$AdminControl queryNames *:type=DataSource,name=$name,*] 
    
    Using Jython:
    name = "Default Datasource"
    ds = AdminControl.queryNames("*:type=DataSource,name="+name+",*") 
    
    
 3. View the pool contents by invoking the showPoolContents operation of the data source MBean:
    Using Jacl:
    $AdminControl invoke $ds showPoolContents
    
    Using Jython:
    print AdminControl.invoke(ds, "showPoolContents")
    
    
 4. View contents of all pools by invoking the showAllPoolContents operation of the data source MBean:
    Using Jacl:
    $AdminControl invoke $ds showAllPoolContents
    
    
    Using Jython:
    print AdminControl.invoke(ds, "showAllPoolContents")
    
    
 5. Immediately purge the pool by invoking the purgePoolContents operation of the data source MBean with the immediate option:
    Using Jacl:
    $AdminControl invoke $ds purgePoolContents immediate
    
    
    Using Jython:
    AdminControl.invoke(ds, "purgePoolContents", "immediate")
    
    

If the purge operation is successful, no response will be observed. If the purge operation is not successful, the response will indicate a possible reason for failure. One common reason for failure is attempting to purge the contents of a connection pool which is not yet available (the connection pool is not created until a naming look-up of the associated datasource or connection factory occurs), resulting in an illegal state exception.  


Cross reference information Segment Product Component Platform Version Edition Application Servers Runtimes for Java Technology Java SDK Application Servers WebSphere Application Server for z/OS Java SDK z/OS 8.0, 7.0, 6.1
Why does our Websphere application server hang when we apply Microsoft patch on our database server? We use SQL Server database for one of our inhouse applications. As part of our regular maintenance we install Microsoft SQL patching once every month.Our DBA restarts the database after the patch install.All applications reconnect to the datbase automatically once the database is up but the application that is running on WAS fails to reconnect to the daabase and the appserver becomes unresponsive.Test connection to database is working fine though. And also webserver is reaching out max clients. So we are restarting appservers and webservers everytime Microsoft SQL patching is installed.
WebSphere Application Server has an operation on the data source MBean that can be used to purge the connection pool. WebSphere Application Server MBean may be called via the wsadmin console, see the IBM Information Center topic "Scripting the application serving environment (wsadmin)" for more details. The operation name is: purgePoolContents.