Change connection pool settings with the wsadmin tool

Use the wsadmin scripting tool to change connection pool settings.

The wsadmin tool runs scripts. Use the wsadmin tool to manage a WebSphere Application Server installation and configuration, application deployment, and server runtime operations. The product supports the Jacl and Jython scripting languages. To learn more about the wsadmin tool see the topic Starting the wsadmin scripting client.

To use the wsadmin tool to change connection pool settings:

Tasks

1. Launch a scripting command. There are several options for us to run scripting commands, ranging from running them interactively to running them in a profile.

   To change connection pool settings, we use the getAttribute and setAttribute commands, run against the various settings objects.

   For example, to change the connection timeout setting, the commands are:

   $AdminControl getAttribute $objectname connectionTimeout
   $AdminControl setAttribute $objectname connectionTimeout 200
   

   where:

   • $ is a Jacl operator for substituting a variable name with its value
   • getAttribute and setAttribute are the commands
   • connectionTimeout is the object whose value we are resetting

2. For Jacl code examples of each of the connection pool settings, see the topic Example: Changing connection pool settings with the wsadmin tool.

Examples

• Change connection pool settings with the wsadmin tool

  Using the wsadmin AdminControl object, we can script changes to connection pool settings. The wsadmin tool runs scripts in the Jacl and Jython languages only. We must start the wsadmin scripting client to perform any scripting task. See topic Starting the wsadmin scripting client. For more information about the AdminControl scripting object, see the topic Using the AdminControl object for scripted administration.

  • Connection timeout

    The connection timeout can be changed at any time while the pool is active. When we change the connection timeout value, the connection timeout for all connection requests that are waiting is changed to the new value minus the time that connection has already waited, and requests are returned to the wait state if there are no available connections.

    For example, if the connection timeout is changed to 300 seconds and a connection request has waited 100 seconds, the connection request waits for 200 more seconds if no connection becomes available.

    $AdminControl getAttribute $objectname connectionTimeout
    $AdminControl setAttribute $objectname connectionTimeout 200
    

    For more information about this setting, see the connection pool settings.

  • Maximum connections

    The maximum connections can be changed at any time unless the stuck connection support is active. If stuck connection support is active, an attempt to change the maximum connections is made. If the attempt fails, an IllegalState exception occurs. See the connection pool advanced settings for more information.

    If stuck connection support is not active, the maximum connections are changed to the new value. If the new value is greater than the current value, the number of connections increases to the new value and any requests waiting are notified. If the new value is less than the current value and Aged Timeout or Reap Time is used, the number of connections decreases to the new value depending on pool activity. If agedTimeout or Reap Time are not used, no automatic attempt made to reduce the total number of connections. To manually reduce the number of connections to the new maximum connections, use the bean function purgePoolContents.

    $AdminControl getAttribute $objectname maxConnections
    $AdminControl setAttribute $objectname maxConnections 200
    

    For more information about this setting, see the connection pool settings.

  • Minimum connections

    The minimum connections can be changed at any time.

    $AdminControl getAttribute $objectname minConnections
    $AdminControl setAttribute $objectname minConnections 200
    

    For more information about this setting, see the connection pool settings.

  • Reap time

    The reap time can be changed at any time. The reap time interval is changed to the new value at the next interval.

    $AdminControl getAttribute $objectname reapTime
    $AdminControl setAttribute $objectname reapTime 30
    

  • Unused timeout

    The unused timeout can be changed at any time.

    $AdminControl getAttribute $objectname unusedTimeout
    $AdminControl setAttribute $objectname unusedTimeout 900
    

    For more information about this setting, see the connection pool settings.

  • Aged timeout

    The aged timeout can be changed at any time.

    $AdminControl getAttribute $objectname agedTimeout
    $AdminControl setAttribute $objectname agedTimeout 900
    

    For more information about this setting, see the connection pool settings.

  • Purge policy

    The purge policy can be changed at any time.

    $AdminControl getAttribute $objectname purgePolicy
    $AdminControl setAttribute $objectname purgePolicy "Failing Connection Only"
    

    For more information about this setting, see the connection pool settings.

  • Surge protection support

    Surge connection support starts if surgeThreshold is > -1 and surgeCreationInterval is > 0. The surge protection properties can be changed at any time.

    $AdminControl getAttribute $objectname surgeCreationInterval
    $AdminControl setAttribute $objectname surgeCreationInterval 30
    $AdminControl getAttribute $objectname surgeThreshold
    $AdminControl setAttribute $objectname surgeThreshold 15
    

    For more information about this setting, see the connection pool settings.

  • Stuck connection support

    An attempt is made to change the stuckTime, stuckTimerTime or stuckThreshold properties. If the attempt fails, an IllegalState exception occurs. The pool cannot have any active requests or active connections during this request. For the stuck connection support to start, all three stuck property values must be greater than 0, and the maximum connections value must be > 0.

    If the connection pool is stuck, we cannot change the stuck or the maximum connection properties. If we are stuck, there are active connections.

    $AdminControl getAttribute $objectname stuckTime
    $AdminControl setAttribute $objectname stuckTime 30
    $AdminControl getAttribute $objectname stuckTimerTime
    $AdminControl setAttribute $objectname stuckTimerTime 15
    $AdminControl getAttribute $objectname stuckThreshold
    $AdminControl setAttribute $objectname stuckThreshold 10
    

    For more information about this setting, see the connection pool advanced settings.

  • Test connection support (This is only supported for a data source)

    The test connection support starts when the testConnection property is set to true, and the interval is > 0. The test connection properties can be changed at any time.

    $AdminControl getAttribute $objectname testConnection
    $AdminControl setAttribute $objectname testConnection 30
    $AdminControl getAttribute $objectname testConnectionInterval
    $AdminControl setAttribute $objectname testConnectionInterval 15
    

    For more information about this setting, see the topic Test connection service.

  • Connection pool partition support

    $AdminControl invoke $objectname freePoolDistributionTableSize
    $AdminControl invoke $objectname numberOfFreePoolPartitions
    $AdminControl invoke $objectname numberOfSharedPoolPartitions
    $AdminControl invoke $objectname gatherPoolStatisticalData
    $AdminControl invoke $objectname enablePoolStatisticalData
    

    For more information about this setting, see the connection pool settings.

  • Show pool information support

    The show pool operations can be changed at any time.

    $AdminControl invoke $objectname showAllPoolContents
    $AdminControl invoke $objectname showPoolContents
    $AdminControl invoke $objectname showAllocationHandleList
    

  • Purge connection support

    PurgePool can be changed at any time.

    $AdminControl invoke $objectname purgePoolContents normal
    $AdminControl invoke $objectname purgePoolContents immediate
    

    For both data sources and connection factories, if the normal option is selected, the purged pool behaves as follows after the purge call:

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

    For data sources based on the built-in WebSphere relational resource adapter, if the immediate option is selected, the purged pool behaves as follows after the purge call:

    • No new transactions are allowed to start on any connections obtained prior to the purgePoolContents call. Instead, a StaleConnectionException is thrown.
    • No new handles are allowed to be handed out on any connections obtained prior to the purgePoolContents call. Instead, a StaleConnectionException is thrown.
    • Existing in-flight transactions continue to work, but any new activities on the purged connection cause a StaleConnectionException or an XAER_FAIL exception.
    • close() calls issued on any connections obtained prior to the purgePoolContents() call are done asynchronously (no wait time).
    • Requests for new connections (not handles to existing old connections) are honored.

    • The number of connections are decremented immediately. This might cause the total number of connections in the application server to be temporarily out of sync with the total number of connections in the database.

    For data sources not based on the built-in WebSphere relational resource adapter and all connection factories, if the immediate option is selected, the purged pool behaves as follows after the purge call:

    • close() calls issued on any connections obtained prior to the purgePoolContents() call are done asynchronously (no wait time)
    • Requests for new connections (not handles to existing old connections) are honored.

    • The number of connections are decremented immediately. This might cause the total number of connections in the application server to be temporarily out of sync with the total number of connections in the backend resource.

  • Pool control support

    Pause and resume can be changed at any time.

    $AdminControl invoke $objectname pause
    $AdminControl invoke $objectname resume
    

• Example: Accessing MBean connection factory and data sources using wsadmin

  Use wsadmin commands to access connection factory and data source MBeans. MBeans can retrieve or update properties for a connection factory or data source.

  To get a list of J2C connection factory or data source Mbean object names, from the wsadmin command-line use one of the following administrative control commands:

  $AdminControl queryNames *:type=J2CConnectionFactory,*
  $AdminControl queryNames *:type=DataSource,*
  

  Example output from DataSource query:

  wsadmin>$AdminControl queryNames *:type=DataSource,*
  "WebSphere:name=Default Datasource,process=server1,platform=dynamicproxy,node=
  system1Node01,JDBCProvider=Derby JDBC Provider,j2eeType=JDBCDataSource,J2EESe
  rver=server1,Server=server1,version=6.0.0.0,type=DataSource,mbeanIdentifier=cell
  s/system1Node01Cell/nodes/system1Node01/servers/server1/resources.xml#DataSource
  _1094760149902,JDBCResource=Derby JDBC Provider,cell=system1Node01Cell"
  

  By using the J2C connection factory or data source MBean object name, we can access the MBean. The name follows the webSphere:name= expression. In the following example, for the first set, the default data source name is set on variable name. For the second set, the object name is set on variable objectName.

  Example using the Default DataSource:

  - wsadmin>set name [list Default Datasource] 
  
  Default Datasource
  
  - wsadmin>set objectName [$AdminControl queryNames *:name=$name,*]
  
  
  "WebSphere:name=Default Datasource,process=server1,platform=dynamicproxy,node=
  system1Node01,JDBCProvider=Derby JDBC Provider,j2eeType=JDBCDataSource,J2EESe
  rver=server1,Server=server1,version=6.0.0.0,type=DataSource,mbeanIdentifier=cell
  s/system1Node01Cell/nodes/system1Node01/servers/server1/resources.xml#DataSource
  _1094760149902,JDBCResource=Derby JDBC Provider,cell=system1Node01Cell"
  

  Now we can use the objectName for getting help on attributes and operations available for this bean.

  $Help attributes $objectName
  $Help operations $objectName
  

  To get or set an attribute or to use an operation, use one of the following commands:

  $AdminControl getAttribute $objectName "attribute name"
  $AdminControl setAttribute $objectName "attribute name" value 
  $AdminControl invoke $objectName "operation"
  

  Attribute, operation examples:

  Get and set an attribute maxConnections. If we get no value, a null value, or 
  a java.lang.IllegalStateException, the connection factory or data source for this 
  Bean has not been created. The connection factory or data source is created at first 
  JNDI lookup. For this example, the Default DataSource must be used before get, 
  set and invoke will work.
  
  wsadmin>$AdminControl getAttribute $objectName maxConnections
  10
  
  wsadmin>$AdminControl setAttribute $objectName maxConnections 20
  
  wsadmin>$AdminControl getAttribute $objectName maxConnections
  20
  
  Using invoke
  
  wsadmin>$AdminControl invoke $objectName showPoolContents 
  
  PoolManager name:DefaultDatasource
  PoolManager object:746354514
  Total number of connections: 3 (max/min 20/1, reap/unused/aged 180/1800/0, 
  connectiontimeout/purge 1800/EntirePool)
                                 (testConnection/inteval false/0, stuck timer/time
  /threshold 0/0/0, surge time/connections 0/-1)
  Shared Connection information (shared partitions 200)
    No shared connections
  
  Free Connection information (free distribution table/partitions 5/1)
    (2)(0)MCWrapper id 5c6af75b  Managed connection WSRdbManagedConnectionImpl@4b5
  a775b  State:STATE_ACTIVE_FREE
    (2)(0)MCWrapper id 3394375a  Managed connection WSRdbManagedConnectionImpl@328
  5f75a  State:STATE_ACTIVE_FREE
    (2)(0)MCWrapper id 4795b75a  Managed connection WSRdbManagedConnectionImpl@46a
  4b75a  State:STATE_ACTIVE_FREE
  
    Total number of connections in free pool: 3
  UnShared Connection information
    No unshared connections
  

Start the wsadmin scripting client

wsadmin scripting tool