ODBC--Call failed. AS400/Access query

Posted on 2009-02-11
Last Modified: 2013-12-06
I have a user that attempts to run a query in Access from linked AS400 tables and receives the following error. ODBC--Call failed. I ran the same query from my computer and it worked fine. At her computer, I ran some of the querys that make up the first query and found that one of the querys brings back the following error message; ODBC--Call failed. [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - SQL query exceeds specified time limit or storage limit. (#-666). If I attempt to open the table that this particular query uses, it opens fine without any errors. Any ideas? I tried to be a succint as possible. Please let me know if there are any questions. I have also attached a screenshot of the error message.
Question by:rxmijares
    LVL 34

    Accepted Solution

    Well, it is probably not a surprise that you get this message when a query runs for too long or produces a large amount of intermediate results.

    It would be helpful if you posted the failing query.  
    Also, determine how many records are being selected by this particular query.

    For example, if the query says:

    select a,b,c from table1 where a> 100 and b =3
    Run a summary query to get a record count:

    select count(*) from table 1 where a>100 and b=3
    There is a Query Timeout parameter that you can set either on the ODBC DSN that the query references:

    • Open ODBC Data Source Administrator on the workstation that is experiencing the error
    • In the Administrator window, select the DSN and click Configure
    • Go to Performance tab inside the iSeries Access for Windows window that appears
    • Click Advanced under the Performance options.
    • Uncheck "Allow Query Timeout"
    • Apply and OK out.
    Or in Access itself (open the query in design mode and select View --> Query Properties.  Increasing the timeout gradually may resolve the issue.

    Note that the instructions for both of these operations may vary a bit depending on your OS and Access version.  If you need version-specific instructions, post the relevant info here.

    If the issue is intermediate size, post backl and we can take a look at that issue.

    - Gary Patterson

    LVL 34

    Expert Comment

    by:Gary Patterson
    Here's more information on SQL errors (you'll have to search down for SQL0666), and various remedies.  Note that these options and remedies change a bit from release to release, so make sure you use the correct Information Center.  I picked V5R4 for this example:

    - Gary Patterson

    Author Closing Comment

    As soon as I removed the "Allow Query Timeout" selection, the query ran wtihout any errors.

    Expert Comment

    I had this same problem about two weeks ago.  I am not entirely sure which of the actions I took resolved it, but I think it was related to his Windows domain user ID rights on the network.  Because he did not have the capability of deleting the .ldb file from the location on the network where the database resided.  I however did have the proper rights.  When I ran it from my machine on the network it was fine, but from his machine logged on as him, it would error out.  I would try temporarily changing the network rights for this one user and see if it makes a difference.  The other two things I did for him was to run Windows update and to update his iSeries Access for Windows client.  In my case, the error message may have had a different error number, but it seems odd that it would not time out for you but it would for your user.  These messages are often not very well formed and can be misleading.
    LVL 34

    Expert Comment

    by:Gary Patterson
    I posted the wrong link above for the detailed error messages:

    The generic message "ODBC Call Failed" is not very meaningful, and is returned by Access as a catch-all whenever anything goes wrong on an ODBC call.  That's why it isn't very useful in diagnosing problems - lots and lots of underlying problems can cause this generic message.

    If the generic "ODBC call failed" mesage is in response to a lower-level message from the database server (as it is in this case), it can be a very good indicator of the problem (as it was in this case).

    The trick is to use the detailed error message (SQL0666 in this case) that is actually generated by the database server (AS/400 DB2 in this case - which in my experience generates better-than-average error information.)  If you look at the link above, and search for SQL0666, you'll find a pretty detailed description of the problem and resolution right in the IBM documentation:

    SQL0666    Message Text:

    SQL query exceeds specified time limit or storage limit.  

    Cause Text:  

    A database query was about to be started whose estimated run time of &1 exceeds the specified limit of &2 or whose estimated temporary storage usage of &3 exceeds the specified limit of &4. The query time limit and temporary storage limit are specified on the CHGQRYA CL command.  

    Recovery Text:  

    The following changes to the query could reduce the estimated elapsed time or estimated amount of temporary storage usage.

    • Change the query so that fewer records are returned by adding more restrictive record selection specifications.
    • Change the record selection of the query so that an existing access path can be used to process the records more quickly.
    • Create an access path with keys that match the record selection of the query using the SQL CREATE INDEX statement.
    • Change the ordering specification so neither a temporary access path needs to be built nor a sort needs to be performed.
    • Change the grouping field specification to match the left-most key fields of an existing access path. This allows an existing access path to be used.
    • Specify the OPTIMIZE FOR NN ROWS clause where NN represents the actual number of records which are to be retrieved before the query is ended. This clause will cause the query optimizer to assume that the query will not be run to completion and consequently reduce the estimates.
    • Change the output type of your query to produce the results on the display.
    • Specify a new value for the query time limit with the QRYTIMLMT parameter of the CHGQRYA CL command.
    • Specify a new value for the query temporary storage limit with the QRYSTGLMT parameter of the CHGQRYA CL command.
    • Examine the query debug messages contained in the job log for performance information and suggestions. These messages will precede this message in the job log and will provide additional information and suggestions.
      SQLCODE or SQLCODEs:  -666  
    SQLSTATE or SQLSTATEs:  57005

    Author Comment

    I forgot to mention some things that I had done before posting the question. The query builds a report using other queries. I tested those individual queries on the users machine and found that I received an error message on one particular query. From my machine, I changed the all of the query definitions to include a smaller record set. However, she would still get the same message. After you posted your fix above, I looked at the configuration on my machine and found that the "Allow Query Timeout" was not selected on my computer. This would explain why it worked on mine, and not hers.
    LVL 34

    Expert Comment

    by:Gary Patterson
    Thanks for the update.  The configuration difference sure explains the difference in behavior.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now