Configuring QueryTimeout

You can configure a timeout on the data source of an application so that the transaction or statement timeout occurs if a query fails to return, because of a deadlock or blocked transactions.

Before you begin

Traditional Java Database Connectivity (JDBC) provides a standard interface, called java.sql.Statement.setQueryTimeout, to limit the number of seconds that a JDBC driver waits for a statement to execute. This can 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. With earlier versions of WebSphere® Application Server, the only way of setting a query timeout is by programmatically establishing an SQL query timeout in the application by invoking the java.sql.Statement.setQueryTimeout interface on every statement.

About this task

In Version 8, you can configure this query timeout using either of the following two custom properties at the data source level: By default, query time is disabled. Based on the presence and value of the two new data source custom properties, a timeout value is calculated of either: The calculated timeout is then used in conjunction with the JDBC API to set a query timeout value on each statement.

Procedure

  1. Open the administrative console.
  2. Go to the WebSphere Application Server Data Source properties panel for the data source.
    1. Click Resources > JDBC > Data Sources > data_source
    2. Click WebSphere Application Server Data Source properties.
  3. Click Custom properties under Additional Properties.
  4. Click New.
  5. Enter webSphereDefaultQueryTimeout in the Name field.
  6. 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.
  7. Click OK.
  8. Click New.
  9. Enter syncQueryTimeoutWithTransactionTimeout in the Name field.
  10. 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.
  11. Click OK.
  12. Save your changes. The updates go into effect after the server is restarted.

Results

You have configured the query timeout on the data source of your application.

Example

The following example illustrates using webSphereDefaultQueryTimeout = 20 and syncQueryTimeoutWithTransactionTimeout = true:
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 using webSphereDefaultQueryTimeout = 20 and syncQueryTimeoutWithTransactionTimeout = false:
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 your application code.



In this information ...


IBM Redbooks, demos, education, and more

(Index)

Use IBM Suggests to retrieve related content from ibm.com and beyond, identified for your convenience.

This feature requires Internet access.

Task topic Task topic    

Terms of Use | Feedback

Last updatedLast updated: Sep 19, 2011 3:08:41 PM CDT
http://www14.software.ibm.com/webapp/wsbroker/redirect?version=matt&product=was-nd-zos&topic=tdat_querytimeout
File name: tdat_querytimeout.html