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
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 the syncQueryTimeoutWithTransactionTimeout 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
Results
Example
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 your application code.