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
The Java Database Connectivity (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.
About this task
You can configure this query timeout using either of the
following custom properties :
- 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.
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.
Procedure
- Open the administrative console.
- Go to the panel for
the data source.
- Click
- 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
You have configured the query timeout on the data source of
your 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 your application code.