Ensuring applications obtain valid connections

 

If your application is accessing pooled connections, you can enable connection pretesting to help prevent it from obtaining connections that are no longer valid. Connection pretesting is a way to test connections from the free pool before giving them to the client.

When a backend resource, such as a database, fails, pooled connections that are not valid might exist in the free pool. This is likely when the purge policy is failingConnectionOnly, meaning only the failing connection is removed from the pool. Depending on the failure, the remaining connections in the pool might not be valid.

1. Enable and configure connection pretesting.

   1. Open..

      $WAS_HOME/properties/j2c.properties

   2. Create the following entry, based on your datasource...

      <advanced connection properties>

   3. Enable connection pretesting.

      Set <testConnection> to true.

   4. 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/DataSource>
      <testConnection>true</testConnection>
      <testConnectionRetryInterval>5</testConnectionRetryInterval>
   </advanced-connection-properties> 
   

2. Specify the SQL statement to use when connection pretesting is enabled.

   Configure a preTestSQLString custom property for the data source.

   1. Open the administrative console.

   2. Navigate to...

      Resources | JDBC_provider | Data Sources | data_source | Custom Properties

   3. Specify preTestSQLString as the property name.

   4. Specify an SQL statement as the property value.This SQL statement is used for connection pretesting, which helps to ensure that applications obtain valid data sources from connection pools. When connection pretesting is enabled, the SQL statement is executed to determine whether the connection is good. See Database connection settings that can be added to the administrative console for help configuring this property.

      Specify a valid SQL statement for the preTestSQLString custom property for best performance. The SQL statement should be one that is executed quickly and does not result in any exception thrown. If an exception is thrown, WAS run time needs to check whether the exception indicates the connection is bad or not. This additional checking can affect performance. One example of this SQL statement is "SELECT 1 from [TESTTABLE]", in which [TESTTABLE] is a valid table with only a few rows. A similarly appropriate selection for an Oracle data source is "SELECT USER FROM DUAL".

 

See Also

Warning: no string named [rdat_customprops] found.

 

---