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:

Copy to Clipboard


As you can see from the Document section, add these lines to the top of all your SPL UDRs:

Copy to Clipboard
  • 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:

Copy to Clipboard


That would only give the error number without any description, so we need to make that standard data available. Run this shell script first:

Copy to Clipboard


Load the results into a new reference table with:

Copy to Clipboard


We can then create a view that joins that with the exceptions log, including only data for the current instance:

Copy to Clipboard


The following can be used as a test:

Copy to Clipboard


That produces this message:

Copy to Clipboard


The details of the last error can be retrieved as follows:

Copy to Clipboard


In this case, that returns:

Copy to Clipboard


We found the following useful to get a concise aggregated summary from a production system:

Copy to Clipboard


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:

Copy to Clipboard


That produces both a load file and a more readable plain text file. The former can be added into our reference table with:

Copy to Clipboard


You may not have “fglmkmsg”, so the contents of the above load file are provided here:

Copy to Clipboard


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.

Author