Re: Receiving SQL0440 error on ODBC connection

On 16-Mar-2015 11:22 -0500, Bob Cagle wrote:

We have UPS Worldship ODBC integration setup to import data from the
System i. UPS came out last week and upgraded their Worldship
software. Now we are getting SQL0440 ODBC errors. The text of the
error seems to point back to the System i: Routine SQLSTATISTICS in
SYSIBM not found with specified parameter.

The full context of that msg SQL0440 from the server joblog [the spooled joblog with LOG(4 0 *SECLVL) logging] might be more revealing than knowing only what is presented at the client.

Can anyone provide any input into this error? Why would the update
of client software break the ODBC?

A -440 could occur if the client code invokes a function or calls a procedure by that name [rather than using either the VIEW by that name or the ODBC API by that name], but with arguments that do not match the definition of the routine either in the number of arguments or the [compatible] data type of the argument. For example if N as the number of arguments were incorrect or the data type was not compatible for any arg#, in any of the possible invocations of a routine by that name: SYSIBM.SQLstatistics(arg1,...,argN))

The SYSROUTINES catalog can be queried to find any PROCEDURE or FUNCTION by that name, and the SYSPARMS catalog can be queried to see the data type of each of the parameter declarations for the routine(s) by that name.

If the client code is invoking the ODBC SQLStatistics() API and the error is as noted, then that would seem to be a problem with the middleware or a problem at the server, as an apparent defect.? One would expect that the source code would be disallowed from causing the request made to the client to be translated incorrectly. I suppose if instead of just the client code having been updated, such that the ODBC level of support had been updated to a level not yet supported by the server [e.g. server at ODBC 1.0 and client at ODBC X.Y where x>1] then that could be a possible origin as well, if either of the ODBC specs [unlikely] or the implementation at the server did not enable a backward compatibility.