How to set up WebSphere to test database connections prior to using them

Answer

Background
TeamWorks uses pooled JDBC database connections from the app server. When the connection to your database fails, pooled connections that are no longer valid might exist in the free pool. Connection pretesting is a way to test connections from the free pool before giving them to the client. You can enable the PreTest Connections feature in the WebSphere administrative console to help prevent TeamWorks 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.

Note that if connection pretesting is not enabled and TeamWorks attempts to use an invalid connection you will likely see an exception like this:
com.ibm.websphere.ce.cm.StaleConnectionException: Io exception: Broken pipe

To have WebSphere automatically check the validity of a connection when serving it up from the connection pool do the following:

For WebSphere 5.1:

Here is a link to IBM's web site with some additional information on WebSphere 5.1:

• Enable and configure connection pretesting.
• Open the j2c.properties file. The file is located in install_root/properties/j2c.properties.
• Using the example in the file, create an <advanced connection properties> entry corresponding to your datasource.
• Enable connection pretesting
• Set <testConnection> to true.
• Specify how often to retry, should the pretest fail.
• Set <testConnectionRetryInterval> to the number of seconds for the pretest connection thread to wait between attempts to create and pretest a connection. It will do so until it is successful, at which point the connection pool starts processing getConnection() requests and the pretest connection thread ends.
• If the pretest fails, new connection requests are rejected with a ResourceAllocationException indicating Failed preTestConnection. Pool requests are blocked until the test connection thread is successful.
• For example, this j2c.properties configuration enables connection pretesting to occur every 5 seconds until it is successful:
• <advanced-connection-properties connectionFactoryJNDIName=jdbc/TeamWorksDB>
• <testConnection>true</testConnection>
• <testConnectionRetryInterval>5</testConnectionRetryInterval>
• </advanced-connection-properties>
• Add one section like the above for each datasource in question... just substitute the correct JNDI name
• Specify the SQL statement to use when connection pretesting is enabled.

Configure a preTestSQLString custom property for the data source.

• Open the administrative console.
• Navigate to Resources > JDBC_provider > Data Sources > data_source > Custom Properties.
• Specify preTestSQLString as the property name.
• Specify an SQL statement as the property value.
• Note that you should use the LEAST expensive SQL possible to verify the connection (i.e."select 1 from dual" in oracle or "SQL select 1" in SQL Server.)
Note that "Specify the SQL statement..." is done for you by default in TeamWorks 5.5.x for the TeamWorks connection pools, but you must manually complete "Enable and configure connection" step.

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.