Technical Blog Post
Abstract
Weekly Tips from DB2 Experts: Common Stored Procedure Errors and what they mean
Body
There are 3 error messages that commonly occur related to Stored Procedures and Functions (collectively referred to as routines). These are SQL0440, SQL1131 and SQL0443.
A SQL0440 returns an error indicating the routine does not exist. Usually when this error occurs the routine does exist but is being called incorrectly. Routines can be overloaded so two or more routines can be created using the same schema and name if they use either a different number of input or return variables (3 input as opposed to 2) or different data types (decimal as opposed to string) for their input or return variables. If DB2 cannot match the number and type of variables used in the call to the routine name it will return this error, even if the routine exists.
A SQL1131 returns when the thread or process running the routine fails. This will occur if the routine crashed while running or if the thread or process failed during cleanup. If the thread or process failed during cleanup the message will only appear in the db2diag.log. The message does not effect database processing and has the same impact on the database as if an application running remotely crashed while accessing it. Retrying the routine may be successful.
If the SQL1131 is not being reported by an application, it is likely that the process or thread failure is occurring after the routine exits. Some routines can run successfully but damage the thread or process so it is no longer safe to put the thread or process in the pool. In this case the SQL1131 is logged only in the db2diag.log and the process or thread is destroyed. A new process or thread or one from the pool will be used the next time the routine is called so in this case the SQL1131 may not even be noticed until the log is checked.
The SQL1131 message is also returned if the process that runs routines for DB2 (called the db2fmp or db2fmp32 depending on routine bitwidth) could not be started. In this case the message returned or logged in the db2diag.log will include a SQL1042 (unexpected system error) and further investigation will be required. This message is usually the result of a configuration or permissions issue. This message does not impact the database, but the routine called cannot be run and subsequent calls to routines will likely fail.
A SQL0443 error indicates a running routine encountered an issue and failed. This message is similar to the SQL1131 except that in this case the process or thread running the routine did not fail and only the routine failed. This error usually occurs for a specific routine and is often the result of some code issue in the routine itself.
Routine errors are often marked as Severe in the db2diag.log. This is so that the errors will be logged at all diaglevels and does not reflect the impact the error has on database processing. The routine errors mentioned in this blog almost always have no impact on the database.
The Error messages described in this Blog are documented at the following URLs.
SQL0440
SQL1131
SQL0443
For additional information on Stored Procedure errors you can refer to the following technote.
UID
ibm13287001