Admin Alert: Turning Off ODBC Query Timeout Limits

by Joe Hertvik

OS/400 ODBC processing is notorious for obscure error messages that offer little information about their causes or their solutions. One of the strangest is the SQL0666 error, which stops SQL query processing when the estimated query run time exceeds the system's query processing limit. While others have documented how to avoid this error by changing an ADO property, newer iSeries Access ODBC drivers allow you to disable query timeouts directly within your Data Sources (DSN).

For those who have not run into it, the SQL0666 error usually happens when you are using ODBC to process a large query from your PC. When it is determined that the query will process longer than OS/400's query timeout limit, the query goes belly up and returns something similar to the following error message:

[IBM][Client Access ODBC Driver (32-bit)][DB2/400 SQL]SQL0666
 - Estimated query processing time xxx exceeds limit yyy.

Where xxx and yyy represent the estimated amount of time it will take to process the query and the OS/400 query time limit, respectively. OS/400's query time limit is kept in the Query processing time limit system value, QQRYTIMLMT, which can be viewed or changed through the following Work with System Values command (WRKSYSVAL):

WRKSYSVAL SYSVAL(QQRYTIMLMT)

This system value is important for SQL processing because--when a query is submitted to OS/400--the query optimizer estimates a value for the elapsed number of seconds that the query is expected to run (as represented by xxx in the error message). This value is compared against the query time limit (as represented in QQRYTIMLMT and in the yyy value returned in the SQL0666 message). If the xxx value is greater than the yyy value, OS/400 will not allow the query to start.

In pre-V5R2M0 versions of iSeries Access for Windows' ODBC, query timeout value support can be disabled in applications that use ADO by setting the CommandTimeout property to 0. This technique is explained in greater detail in the articles listed in the Related Stories section.

In more recent OS/400 versions, QQRYTIMLMT is set to *NOMAX by default, but it could have been previously set to a lower value. So if you are receiving an SQL0666 error, the first thing you will want to check is what value QQRYTIMLMT is set to and whether or not it is less than your estimated query processing time. If it is, you may be able to eliminate the SQL0666 error by increasing the QQRYTIMLMT system value. If QQRYTIMLMT is set to *NOMAX or it is larger than the estimated query processing time value and you still receive this error, you can also eliminate the error by changing an ODBC value in your DSN.

In the ODBC driver that comes with iSeries Access for Windows V5R2 and above, IBM now provides you with a mechanism for turning off query timeout value support for applications that use a particular ODBC Data Source Name (DSN). Once your applications know that they don't have to adhere to the query timeout value, the error disappears. You can turn off query timeout limit processing in a DSN by performing the following steps:

• Open the ODBC Data Source Administrator on the client machine that is experiencing the SQL0666 error
• In the Administrator window, highlight the DSN that you want to change and click on the Configure button
• Click on the Performance tab inside the iSeries Access for Windows Setup dialogue that appears
• Click on the Advanced button under the Performance options. This will bring up the Advanced performance options window
• Turn off the checkmark in the Allow Query Timeout checkbox. Click on OK to exit this screen
• Back on the iSeries Access for Windows Setup screen, click on the Apply button followed by the OK button. This allows you to exit the screen and save your changes

Once this option is turned off, applications using this particular ODBC DSN will automatically disable support for query timeout value checking. But remember that while this option is handy for allowing longer running queries to automatically finish, it also removes a safeguard against run-away queries that will throttle PC and AS/400 performance. So use it only when it's needed and leave it on the rest of the time.

RELATED STORIES

Query Time Limits and ADO: the Devil's in the Details

DB2-ODBC Query Timeout Property: SQL0666 Estimated Query Processing Time, Listen Software Solutions