Set a network timeout to avoid possible long or indefinite waiting for a response from the database in the event of a network or database failure.

The query timeout limits execution time of a query; the network timeout controls the underlying communication channel between the JDBC driver and the database.

Full support for timeouts requires the network timeout to be configured on the JDBC driver. This feature is JDBC driver dependent.

For example, when a query timeout is detected and the JDBC driver sends a notification to the database, a communication timeout may occur. This will not be detected (or the detection can take arbitrary time) unless the network timeout is explicitly configured.

Warning

Not configuring the network timeout causes either:

  • a default value (between minutes and hours) to be used, usually provided by operating system, or

  • indefinite blocking of query execution, waiting for a database answer.

Below are details for configuring the network timeout for some common database drivers.

Database, JDBC driver Where to configure Property name Unit Example

Oracle, Thin driver, v10.1.0.5+

DbQueryDataSourceProfile ConnectionProperties

oracle.jdbc.ReadTimeout

ms

oracle.jdbc.ReadTimeout: 60000

Oracle, Thin driver, older

DbQueryDataSourceProfile ConnectionProperties

oracle.net.READ_TIMEOUT

ms

oracle.net.READ_TIMEOUT: 60000

Oracle, OCI driver

Not supported.

Oracle TimesTen

DSN configuration (sys.odbc.ini or .odbc.ini)

TTC_Timeout PLSQL_TIMEOUT [1]

s

[TT_1122]
…​
TTC_Timeout=60
PLSQL_TIMEOUT=55

MySQL, v3.0.1+

DbQueryDataSourceProfile ConnectionProperties [2]

socketTimeout

ms

socketTimeout: 60000

PostgreSQL, v8.4+

DbQueryDataSourceProfile ConnectionProperties [2]

socketTimeout

s

socketTimeout: 60
Tip Always check the vendor’s documentation for the right property name, place of configuration, and any other restrictions or guidelines.

As a general guideline: the value for network timeout should be greater than maximum time of a query or transaction. The resource adaptor internally sets the query timeout for the JDBC driver (more precisely, for each issued query statement) equal to 1.5 times DbQueryDataSourceProfile QueryTimeout. The network timeout should not be less than 1.5 times the QueryTimeout, as otherwise the JDBC driver may end a query prematurely with a failure.

1 If PL/SQL is used, set the PLSQL_TIMEOUT value to a value that is at least 5 seconds less than TTC_Timeout. If DSN configuration already specifies SQLQueryTimeout value, then its value takes precedence over the value set by the resource adaptor before executing a query (also update and execute). This should be considered when setting the network timeout (TTC_Timeout parameter). The TTC_Timeout parameter is only valid for TimesTen Client type connections.
2 Setting the network timeout by means of URL parameters is also possible. However, it is discouraged as it makes the configuration easily lost when the URL is modified.
Previous page Next page