ODBC--Call failed. AS400/Access query

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:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp

- Gary Patterson

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

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.

I posted the wrong link above for the detailed error messages:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/rzala/rzalaml.htm

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

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.

Thanks for the update.  The configuration difference sure explains the difference in behavior.