QueryTimeoutInterval CLI/ODBC configuration keyword

db2cli.ini Keyword Syntax:

QueryTimeoutInterval = 0 | 1 | 5 | positive integer

Default Setting:

In Version 9.7 Fix Pack 5 and earlier: 5 seconds

In Version 9.7 Fix Pack 6 and later fix packs: 1 second

Usage Notes®:

 

An application can use the SQLSetStmtAttr() function to set the SQL_ATTR_QUERY_TIMEOUT statement attributeor set the QueryTimeout keyword in the db2dsdriver.cfg file. This attribute indicates the number of seconds to wait for an SQL statement or XQuery expression 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 is completed.

Setting the QueryTimeoutInterval keyword value can have an adverse effect on implementation of the QueryTimeout keyword setting, where application may not encounter the QueryTimeout event until the next QueryTimeoutInterval elapses.

For instance, suppose the SQL_ATTR_QUERY_TIMEOUT statement attribute is set to 25 seconds (times out after waiting for 25 seconds) and the QueryTimeoutInterval keyword is set to 10 seconds (checks the query every 10 seconds). The query does not time out until 30 seconds (the first check after the 25-second limit). 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 the expired queries. Because checking for the expired queries is an asynchronous operation to the queries being executed, it is possible that a query might 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 are cases where the SQL_ATTR_QUERY_TIMEOUT is set to a value which is too low, and the query is consistently timed-out. If the application cannot be modified, the QueryTimeoutInterval can be set to 0. If the QueryTimeoutInterval is set to 0, the CLI driver does not start a separate thread to periodically query the status of each executing queries, and therefore waits for SQL statements to complete execution before returning to the application.

If the QueryTimeoutInterval keyword is set to 0, any attempt by the application to set the SQL_ATTR_QUERY_TIMEOUT statement attribute results in SQLSTATE 01S02 (Option Value Changed).

This option is valid only in the COMMON section of the db2cli.ini file, and therefore applies to all connections to DB2® databases.

Alternatively, the QueryTimeoutInterval keyword can be set to a value that is larger than the SQL_ATTR_QUERY_TIMEOUT statement attribute 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 keyword can be set to a value of 30 seconds or so to prevent this query from timing out after 15 seconds.