Pretesting pooled connections to ensure validity

Before you beginWhen 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.

Why and when to perform this task If your application uses pooled connections, you can enable the PreTest Connections feature in the administrative 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.

In the administrative console, click Resources > JDBC providers.
Select a provider and click Data Sources under Additional properties.
Select a data source and click WebSphere Application Server data source properties under Additional properties.
Select the PreTest Connections check box.
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.
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.)

Related concepts
Stale connections