Document Actions

Connection Broken - QueryTimeoutInterval

Problem Description

Client connection broken when the query take a long time to complete.

Resolution

Configure QueryTimeoutInterval to 0 on db2cli.ini

Documentation

QueryTimeoutInterval CLI/ODBC configuration keyword

Keyword Description:: Delay (in seconds) between checks for a query timeout.
db2cli.ini Keyword Syntax:: QueryTimeoutInterval = 0 | 5 | positive integer
Default Setting:: 5 seconds
Usage Notes:

An application can use the SQLSetStmtAttr() function to set the SQL_ATTR_QUERY_TIMEOUT statement attribute. This attribute indicates the number of seconds to wait for an SQL statement to complete executing before attempting to cancel the execution and returning to the application.

The QueryTimeoutInterval configuration keyword is used to indicate how long the CLI driver should wait between checks to see if the query has completed.

For instance, suppose SQL_ATTR_QUERY_TIMEOUT is set to 25 seconds (timeout after waiting for 25 seconds), and QueryTimeoutInterval is set to 10 seconds (check the query every 10 seconds). The query will not time out until 30 seconds (the first check AFTER the 25 second limit).

Note:

DB2 CLI implements query timeout by starting a thread that periodically queries the status of each executing query. The QueryTimeoutInterval value specifies how long the query timeout thread waits between checks for expired queries. Because this is an asynchronous operation to the queries being executed, it is possible that a given query may not be timed out until SQL_ATTR_QUERY_TIMEOUT + QueryTimeoutInterval seconds. In the example above, the best-case timeout would be at 26 seconds, and the worst-case timeout would be at 35 seconds.

There may be cases where the SQL_ATTR_QUERY_TIMEOUT is set to a value which is too low, and the query should NOT be timed-out. If the application cannot be modified (that is, a third party ODBC application), then the QueryTimeoutInterval can be set to 0, and the CLI driver will ignore the SQL_ATTR_QUERY_TIMEOUT setting, and therefore wait for SQL statements to complete execution before returning to the application.

Note:

7 7

If QueryTimeoutInterval is set to 0, any attempt by the application to set 7 SQL_ATTR_QUERY_TIMEOUT will result in SQLSTATE 01S02 (Option Value Changed).

(This option is contained in the Common section of the initialization file and therefore applies to all connections to DB2.)

Alternatively, QueryTimeoutInterval can be set to a value that is larger than the SQL_ATTR_QUERY_TIMEOUT setting, thus preventing timeouts from occurring at the specified interval. For example, if the application sets a 15 second SQL_ATTR_QUERY_TIMEOUT value, but the server requires at least 30 seconds to execute the query, the QueryTimeoutInterval can be set to a value of 30 seconds or so to prevent this query from timing out after 15 seconds.

DBA to DBA

Personal tools