+

Search Tips   |   Advanced Search

 

Pretesting pooled connections to ensure validity

 

You can set an appserver to test the validity of pooled connections before allocating them to clients. This test operation consists of running a simple SQL query to ensure that the connection makes a round-trip to and from the database server. When a database fails, pooled connections that are not valid might exist in the free pool. This scenario is likely to occur when you have a failingConnectionOnly purge policy, which mandates that only failing connections be removed from the pool. Whether the remaining connections in the pool are valid varies with the cause of the failure. Connection pretesting is a way to test connections from the free pool before giving them to the client.

This operation is totally unrelated from the testConnection operation, which tests the validity of data source configurations by getting, and then immediately closing, a connection.

 

Overview

If your application uses pooled connections, you can enable the PreTest Connections feature in the console to help prevent your application from obtaining connections that are no longer valid.

The feature is particularly useful for routine database outages. Because these outages are usually scheduled for periods of low use, connections to the database are likely to be in the free pool rather than in active use. Active connections are not pretested; pretesting impedes performance during normal operation. Pretesting ensures that users do not waste time trying to resume connections that became bad before the outage.

 

Procedure

  1. In the console, click Resources > JDBC providers.

  2. Select a provider and click Data Sources under Additional properties.

  3. Select a data source and click WAS data source properties under Additional properties.

  4. Select the PreTest Connections check box.

  5. Type a value for the PreTest Connection Retry Interval, which is measured in seconds. This property determines the frequency with which a new connection request is made after a pretest operation fails.

  6. Type a valid SQL statement for the PreTest SQL String. Use a reliable SQL command, with minimal performance impact; this statement is processed each time a connection is obtained from the free pool.

    For example, you might specify SELECT COUNT(*) from TESTTABLE. (For an Oracle database, use SELECT USER FROM DUAL.)


Stale connections

 

Related tasks


Configure a data source using the console