Abstract
Informix Stored Procedure Language (SPL) has a smaller command set than other database products, but is more than adequate when making full use of built-in SQL functions. An SPL routine is called a PROCEDURE if no data is returned, or FUNCTION otherwise, as explained here. User Defined Routines (UDRs) can also be written in C or Java, whereas this article is only concerned with SPL UDRs.
There may be multiple levels of SPL routines called by another, and it can be difficult to know which has failed if an error results. This article provides a method of recording and retrieving the details of any failure, with only a tiny code addition at the top of each routine.
Content
Examples in this article were produced using the provided “stores_demo” database in a Developer Edition container from informix-dockerhub.
The objects provided in this article were created in a separate “oninit” database for clarity. You could use any existing database if you prefer.
The main component is this:
As you can see from the Document section, add these lines to the top of all your SPL UDRs:
- If an error occurs, before the session is aborted, details are appended by “sp_exception” to a file.
- Other more specific EXCEPTION blocks should be placed afterwards and will take precedence.
That output file – the “exceptions log” – can subsequently be accessed in SQL via the following:
That would only give the error number without any description, so we need to make that standard data available. Run this shell script first:
Load the results into a new reference table with:
We can then create a view that joins that with the exceptions log, including only data for the current instance:
The following can be used as a test:
That produces this message:
The details of the last error can be retrieved as follows:
In this case, that returns:
We found the following useful to get a concise aggregated summary from a production system:
We subsequently found that a number of common error numbers are missing from “errmsg.txt” and raised this APAR:
IT44696: Approximately 109 error message descriptions are missing from the finderr tool
For example, that defect makes it hard to interpret some errors captured via SQLTRACE.
We used this shell script to obtain the missing entries:
That produces both a load file and a more readable plain text file. The former can be added into our reference table with:
You may not have “fglmkmsg”, so the contents of the above load file are provided here:
That was produced on IDS 14.10.FC10. Interestingly, the last row was already present on 12.10.FC14E.
Conclusion
It can be difficult to know in which SPL UDR a failure has occurred. This article provides tools to record and review that information, together with descriptions of all error numbers.
Disclaimer
Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.
