You can set an application server 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.
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.
Note: 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.
About 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.
Procedure
- 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.)