Configure QueryTimeout

We can configure a query timeout on the data source of an application so that a Structured Query Language (SQL) statement will be interrupted if it fails to complete execution prior to the specified number of seconds.

The JDBC API provides a standard interface java.sql.Statement.setQueryTimeout to limit the number of seconds a JDBC driver waits for a statement to execute. This is used by an application to control the maximum amount of time the application waits for an SQL statement to complete before the request is interrupted. WebSphere Application Server allows a query timeout to be set on a data source, avoiding the need to make application changes to call the java.sql.Statement.setQueryTimeout directly. You may still programmatically establish a SQL query timeout in the application by invoking the java.sql.Statement.setQueryTimeout interface on every statement.

We can configure this query timeout using either of the following custom properties :

1. webSphereDefaultQueryTimeout establishes a default query timeout, which is the number of seconds an SQL statement may execute before timing out. This default value is overridden during a Java Transaction API (JTA) transaction if the syncQueryTimeoutWithTransactionTimeout custom property is enabled.

2. syncQueryTimeoutWithTransactionTimeout uses the time remaining (if any) in a JTA transaction as the default query timeout for SQL statements.

By default, query timeout is disabled. Based on the presence and value of the two data source custom properties, a timeout value is calculated as either:

• the time remaining in the current JTA transaction based on the transaction manager (TM) timeout setting - syncQueryTimeoutWithTransactionTimeout
• the absolute number of seconds specified by configuration - webSphereDefaultQueryTimeout

The calculated timeout is then used to set a query timeout value on each SQL statement executed by the application using the configured data source.

Tasks

1. Open the administrative console and go to...
   Resources | JDBC | Data Sources | data_source | WebSphere Application Server Data Source properties | Additional Properties | Custom properties | New

2. Enter webSphereDefaultQueryTimeout in the Name field.

3. Enter the number of seconds to use for the default query timeout in the Value field.

   The timeout value is in seconds. A value of 0 (zero) indicates no timeout.

4. Click OK.

5. Click New.

6. Enter syncQueryTimeoutWithTransactionTimeout in the Name field.

7. Enter true or false in the Value field.

   A value of true indicates to use the time remaining in a JTA transaction as the default query timeout.

8. Click OK.

9. Save changes.

   The updates go into effect after the server is restarted.

We have configured the query timeout on the data source of the application.

Example

The following example illustrates the affect of setting the data source custom properties webSphereDefaultQueryTimeout = 20 and syncQueryTimeoutWithTransactionTimeout = true. Note because both properties are set, the SQL statements executed outside of a JTA transaction (as demarcated by the calls to transaction.begin() and transaction.commit()) use the default timeout value established by webSphereDefaultQueryTimeout. Those within the JTA transaction use the time remaining before the expiration of the transaction timeout:

statement = connection.createStatement();
statement.executeUpdate(sqlcommand1); // query timeout of 20 seconds is used
statement.executeUpdate(sqlcommand2); // query timeout of 20 seconds is used
transaction.setTransactionTimeout(30);
transaction.begin();
try
{
    statement.executeUpdate(sqlcommand3); // query timeout of 30 seconds is used
    // assume the above operation took 5 seconds, remaining time = 30 - 5 seconds
    statement.executeUpdate(sqlcommand4); // query timeout of 25 seconds is used
    // assume the above operation took 10 seconds,, remaining time = 25 - 10 seconds
    statement.executeUpdate(sqlcommand5); // query timeout of 15 seconds is used
}
finally
{
    transaction.commit();
}
statement.executeUpdate(sqlcommand6); // query timeout of 20 seconds is used

The following example illustrates the affect of setting the data source custom properties webSphereDefaultQueryTimeout = 20 and syncQueryTimeoutWithTransactionTimeout = false. When only webSphereDefaultQueryTimeout is set, the default timeout value is used for all statements, regardless of whether they are executed within a JTA transaction or not:

statement = connection.createStatement();
statement.executeUpdate(sqlcommand1); // query timeout of 20 seconds is used
statement.executeUpdate(sqlcommand2); // query timeout of 20 seconds is used
transaction.setTransactionTimeout(30);
transaction.begin();
try
{
    statement.executeUpdate(sqlcommand3); // query timeout of 20 seconds is used
    // assume the above operation took 5 seconds
    statement.executeUpdate(sqlcommand4); // query timeout of 20 seconds is used
    // assume the above operation took 10 seconds
    statement.executeUpdate(sqlcommand5); // query timeout of 20 seconds is used
}
finally
{
    transaction.commit();
}
statement.executeUpdate(sqlcommand6); // query timeout of 20 seconds is used

You can override the query timeout for a statement at any time by invoking the java.sql.Statement.setQueryTimeout interface from the application code.

Configure resource adapters