How to Resolve Stored Procedure Not Found

Answer

Symptom: You receive an SQLCODE440 when the stored procedure you want to execute doesn't match any stored procedure in the DB2® database in the z/OS® and OS/390® environment.

Cause: The SQLCODE440 occurs because DB2 cannot find the referenced function or stored procedure. The name could be missing from the database or misspelled. An unqualified reference is used and the user's current path does not contain the schema to which the desired function belongs. For a current explanation of SQLCODE440, see the DB2 Messages and Codes at the DB2 for z/OS and OS/390 library Web page.

• Verify that the stored procedure has been bound to the DB2 plan that is being used by the Net.Data. The DB2PLAN specification in the Net.Data configuration file DB2WWW.INI or the DB2PLAN variable of the Net.Data macro that is being executed defines the DB2 plan that is being used to execute the DB2 stored procedure. For details on how to Bind Net.Data, see the Net.Data Administration and Programming Guide at the DB2 for z/OS and OS/390 library Web page.

• Verify that the stored procedure exists in the DB2 database that your are accessing from Net.Data. The database that you are using is provided in the DB2SSID specification in the Net.Data configuration file DB2WWW.INI or set in the DB2SSID variable of the Net.Data macro that is being executed. For DB2 Version 6 and later, you can execute a query to locate all of the schema that is associated with the stored procedure. For example:

  WHERE ROUTINETYPE = 'P' AND NAME = 'procname'

• The authorization ID that is being used by Net.Data to execute the stored procedure is specified by the Webserver configuration. This could be a Webserver default ID, a surrogate ID that is shared by a selected group of Web clients, or a local security ID that is defined on the Webserver host, either in RACF or in a password file. If the Web browser client is validated prior to executing the Net.Data macro, consider placing the stored procedure with a schema of SYSPROC. Stored procedures defined with a schema of SYPROC are always available to Net.Data macros.

• Verify that the number of parameters and the type of parameters specified in the Net.Data macro match the type and number of parameters in the stored procedure. You can use Net.Data trace to verify the data that is being sent to the stored procedure. For instructions on how to run Net.Data trace, see the Net.Data Administration and Programming Guide at the DB2 for z/OS and OS/390 library Web page.