• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Admin Alert: Turning Off ODBC Query Timeout Limits

    July 13, 2005 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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    DRV Tech

    Get More Out of Your IBM i

    With soaring costs, operational data is more critical than ever. IBM shops need faster, easier ways to distribute IBM applications-based data to users more efficiently, no matter where they are.

    The Problem:

    For Users, IBM Data Can Be Difficult to Get To

    IBM Applications generate reports as spooled files, originally designed to be printed. Often those reports are packed together with so much data it makes them difficult to read. Add to that hardcopy is a pain to distribute. User-friendly formats like Excel and PDF are better, offering sorting, searching, and easy portability but getting IBM reports into these formats can be tricky without the right tools.

    The Solution:

    IBM i Reports can easily be converted to easy to read and share formats like Excel and PDF and Delivered by Email

    Converting IBM i, iSeries, and AS400 reports into Excel and PDF is now a lot easier with SpoolFlex software by DRV Tech.  If you or your users are still doing this manually, think how much time is wasted dragging and reformatting to make a report readable. How much time would be saved if they were automatically formatted correctly and delivered to one or multiple recipients.

    SpoolFlex converts spooled files to Excel and PDF, automatically emailing them, and saving copies to network shared folders. SpoolFlex converts complex reports to Excel, removing unwanted headers, splitting large reports out for individual recipients, and delivering to users whether they are at the office or working from home.

    Watch our 2-minute video and see DRV’s powerful SpoolFlex software can solve your file conversion challenges.

    Watch Video

    DRV Tech

    www.drvtech.com

    866.378.3366

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    System Objects Updates Delphi/400 Development Tools GST Says 25 Percent of iSeries Tape Restores Fail

    Leave a Reply Cancel reply

Volume 5, Number 27 -- July 13, 2005
THIS ISSUE
SPONSORED BY:

WorksRight Software
Profound Logic Software
COMMON

Table of Contents

  • CHGPF Quirk
  • What Program Uses That File?
  • Admin Alert: Turning Off ODBC Query Timeout Limits

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • The Power11 Transistor Count Discrepancies Explained – Sort Of
  • Is Your IBM i HA/DR Actually Tested – Or Just Installed?
  • Big Blue Delivers IBM i Customer Requests In ACS Update
  • New DbToo SDK Hooks RPG And Db2 For i To External Services
  • IBM i PTF Guide, Volume 27, Number 33
  • Tool Aims To Streamline Git Integration For Old School IBM i Devs
  • IBM To Add Full System Replication And FlashCopy To PowerHA
  • Guru: Decoding Base64 ASCII
  • The Price Tweaking Continues For Power Systems
  • IBM i PTF Guide, Volume 27, Numbers 31 And 32

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle