ConnectionPooling Properties

-Connection Timeout:

Specify the interval, in seconds, after which a connection request times out and a ConnectionWaitTimeoutException is thrown. This can occur when the pool is at its maximum (Max Connections) and all of the connections are in use by other applications for the duration of the wait.

For example, if Connection Timeout is set to 300 and the maximum number of connections is reached, the Pool Manager waits for 300 seconds for an available physical connection. If a physical connection is not available within this time, the Pool Manager throws a ConnectionWaitTimeoutException.

-Max Connections

Specify the maximum number of physical connections that can be created in this pool. These are the physical connections to the back-end database. Once this number is reached, no new physical connections are created and the requester waits until a physical connection that is currently in use is returned to the pool, or a ConnectionWaitTimeoutException is thrown.

Tip: If Connection Timeout is set to 0, the Pool Manager waits as long as necessary until a connection is allocated.

For example, if Max Connections is set to 5, and there are 5 physical connections in use, the Pool Manager waits for the amount of time specified in Connection Timeout for a physical connection to become free. If after that time there are still no free connections, the Pool Manager throws a ConnectionWaitTimeoutException to the application.

-Min Connections

Specify the minimum number of physical connections to be maintained. Until this number is reached, the pool maintenance thread does not discard any physical connections. However, no attempt is made to bring the number of connections up to this number.

For example ,if Min Connections is set to 3, and one physical connection is created, that connection is not discarded by the Unused Timeout thread. By the same token, the thread does not automatically create two additional physical connections to reach the Min Connections setting.

-Reap Time

Specify the interval, in seconds, between runs of the pool maintenance thread.

For example, if Reap Time is set to 60, the pool maintenance thread runs every 60 seconds. The Reap Time interval affects the accuracy of the Unused Timeout and Aged Timeout settings. The smaller the interval you set, the greater the accuracy.

When the pool maintenance thread runs, it discards any connections that have been unused for longer than the time value specified in Unused Timeout, until it reaches the number of connections specified in Min Connections. The pool maintenance thread also discards any connections that remain active longer than the time value specified in Aged Timeout.

The Reap Time interval also affects performance. Smaller intervals mean that the pool maintenance thread runs more often and degrades performance.

-Unused Timeout

Specify the interval in seconds after which an unused or idle connection is discarded.

Tip: If the pool maintenance thread is enabled, set the Reap Time value less than the values of Unused Timeout and Aged Timeout.

Tip: Set the Unused Timeout value higher than the Reap Timeout value for optimal performance. Unused physical connections are only discarded if the current number of connections not in use exceeds the Min Connections setting.

For example, if the unused timeout value is set to 120, and the pool maintenance thread is enabled (Reap Time is not 0), any physical connection that remains unused for two minutes is discarded. Note that accuracy of this timeout, as well as performance, is affected by the Reap Time value. See Reap Time for more information.

-Aged Timeout

Specify the interval in seconds before a physical connection is discarded, regardless of recent usage activity.

Setting Aged Timeout to 0 allows active physical connections to remain in the pool indefinitely.

For example, if the Aged Timeout value is set to 1200, and the Reap Time value is not 0, any physical connection that remains in existence for 1200 seconds (20 minutes) is discarded from the pool. Note that accuracy of this timeout, as well as performance, is affected by the Reap Time value. See Reap Time for more information.

-Purge Policy

Specify how to purge connections when a stale connection or fatal connection error is detected.

Valid values are EntirePool and FailingConnectionOnly.

If you choose EntirePool, all physical connections in the pool are destroyed when a stale connection is detected.

If you choose FailingConnectionOnly, the pool attempts to destroy only the stale connection. The other connections remain in the pool. Final destruction of connections which are in use at the time of the error might be delayed. However, those connections are never returned to the pool.

Selecting the Advanced connection pool properties link in the Additional Properties section of the connection pool configuration page .

Tip: Set the Aged Timeout value higher than the Reap Timeout value for optimal performance.

These properties require advanced knowledge of how connection pooling works and how your system performs.

WebSphere Application Server data source properties 

These properties apply to the WebSphere Application Server connection, rather than to the database connection.

The WebSphere Application Server data source properties are accessed through the link under the Additional Properties section of the data source configuration page.

-Statement Cache Size

Specify the number of prepared statements that are cached per connection. A prepared statement is a precompiled SQL statement that is stored in a prepared statement object. This object is used to execute the given SQLstatement multiple times. The WebSphere Application Server data source optimizes the processing of prepared statements.

In general, the more statements your application has, the larger the cache should be. For example, if the application has 5 SQL statements, set the statement cache size to 5, so that each connection has 5 statements.

-Enable multithreaded access detection

If you enable this feature, the application server detects the existence of access by multiple threads.

-Enable database reauthentication

Connection pool searches do not include user name and password. If you enable this feature, a connection can still be retrieved from the pool, but you must extend the DataStoreHelper class to provide implementation of the doConnectionSetupPerTransaction() method where the reauthentication takes place.

Connection reauthentication can help improve performance by reducing the overhead of opening and closing connections, particularly for applications that always request connections with different user names and passwords.

-Manage cached handles

When you call the getConnection() method to access a database, you get a connection handle returned. The handle is not the physical connection, but a representation of a physical connection. The physical connection is managed by the connection manager. A cached handle is a connection handle that is held across transaction and method boundaries by an application.

This setting specifies whether cached handles should be tracked by the container. This can cause overhead and only should be used in specific situations. For more information about cached handles, see the Connection Handles topic in the Information Center.

-Log missing transaction context

The J2EE programming model indicates that connections should always have a transaction context. However, some applications do not have a context associated with them. This option tells the container to log that there is a missing transaction context in the activity log when the connection is obtained.

-Pretest connection

If you check this box, the application server tries to connect to this data source before it attempts to send data to or receive data from this source. If you select this property, you can specify how often, in seconds, the application server retries to make a connection if the initial attempt fails. The pretest SQL string is sent to the database to test the connection.