Today while working on a project requirement, we got following SQL Exception:-
java.sql.SQLException: Failed preTestConnection. Pool requests blocked for jdbc/XXYYSZDB until the test connection thread is successful.
Since, our application uses pooled JDBC database connections from the app server hence when the connection to our database fails, pooled connections that are no longer valid might exist in the free pool.
Therefore, connection pretesting is a way to test connections from the free pool before giving them to the client. We can enable the PreTest Connections feature in the WebSphere administrative console to prevent 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.
For doing configuration in WebSphere 6.0.x, following are the steps:-
(1) In the administrative console, click Resources > JDBC providers.
(2) Select a provider and click Data Sources under Additional properties.
(3) Select a data source and click WebSphere Application Server 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, “select 1 from dual” in oracle or “SQL select 1” in SQL Server.