Pretesting pooled connections to ensure validity
Before you begin
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.If your application uses pooled connections, one 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.
Procedure
- In the administrative console, click...
Resources | JDBC providers | provider Data Sources | data source | WAS data source 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.)
See Also
Stale connections
Related Tasks
Creating and configuring a data source using the administrative console