Configuring QueryTimeout
You 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.
Before you begin
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.About this task
webSphereDefaultQueryTimeout
establishes a default query timeout, which is the number of seconds that an SQL statement may execute before timing out. This default value is overridden during a Java™ Transaction API (JTA) transaction if thesyncQueryTimeoutWithTransactionTimeout
custom property is enabled.syncQueryTimeoutWithTransactionTimeout
uses the time remaining (if any) in a JTA transaction as the default query timeout for SQL statements.
- 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
Procedure
- Open the administrative console.
- Click
- Click Custom properties under Additional Properties.
- Click New.
- Enter
webSphereDefaultQueryTimeout
in the Name field. - 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.
- Click OK.
- Click New.
- Enter
syncQueryTimeoutWithTransactionTimeout
in the Name field. - 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.
- Click OK.
- Save your changes. The updates go into effect after the server is restarted.
Results
Example
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 preceding operation took 5 seconds, remaining time = 30 - 5 seconds
statement.executeUpdate(sqlcommand4); // query timeout of 25 seconds is used
// assume the preceding 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 preceding operation took 5 seconds
statement.executeUpdate(sqlcommand4); // query timeout of 20 seconds is used
// assume the preceding 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.