WAS v8.5 > WebSphere applications > Data access resources > Data concepts > JDBC providers

Configure QueryTimeout

To limit the number of seconds a JDBC driver waits for a statement to execute, use interface java.sql.Statement.setQueryTimeout. This is the maximum amount of time the application waits for a SQL statement to complete before the request is interrupted. WAS allows a query timeout to be set on a data source, avoiding the need to make application changes to call java.sql.Statement.setQueryTimeout. 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:

webSphereDefaultQueryTimeout	Set a default query timeout. In seconds. Overridden during a JTA transaction if syncQueryTimeoutWithTransactionTimeout is enabled.
syncQueryTimeoutWithTransactionTimeout	Use 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 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.

1. From the dmgr console, go to...
   Resources | JDBC | Data Sources | data_source | WAS 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 your changes. The updates go into effect after the server is restarted.

Example

The following example illustrates the affect of setting the data source custom properties...

webSphereDefaultQueryTimeout = 20
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

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

Related

Configure resource adapters

+

Search Tips   |   Advanced Search