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:
DEFINE sql_error INT;
DEFINE isam_error INT;
DEFINE error_data VARCHAR(255);
ON EXCEPTION SET sql_error, isam_error, error_data
CALL oninit:sp_exception ('procname', sql_error, isam_error, error_data);
RAISE EXCEPTION sql_error, isam_error, error_data;
END EXCEPTION;
- 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:
CREATE EXTERNAL TABLE sp_exception_ext
(
date_time DATETIME YEAR TO SECOND,
instance VARCHAR(128),
session INT,
username VARCHAR(32),
client VARCHAR(255),
process INT,
program VARCHAR(255),
dbname VARCHAR(128),
procname VARCHAR(128),
error_data VARCHAR(255),
sql_error INT,
isam_error INT
)
USING
(
DATAFILES('DISK:/tmp/sp_exception.unl'),
ESCAPE OFF
);
That would only give the error number without any description, so we need to make that standard data available. Run this shell script first:
# errmsg.sh
# Creates an Informix error number and description load file
# Extracts unique first lines from the "finderr" tool repository
# Doug Lawry, Oninit Consulting, September 2023
sort -n $INFORMIXDIR/msg/en_us/0333/errmsg.txt |
awk '
BEGIN {FS="\t"}
$1==last || $1=="" || $2=="" {next}
/^[0-9-]/ {
gsub("[|\\\\]","\\\\&")
print $1 "|" $2
last=$1
}
' > errmsg.unl
Load the results into a new reference table with:
CREATE TABLE errmsg
(
error_code INT PRIMARY KEY,
error_text VARCHAR(255)
);
LOAD FROM 'errmsg.unl' INSERT INTO errmsg;
We can then create a view that joins that with the exceptions log, including only data for the current instance:
CREATE VIEW sp_exception_view
(
date_time,
session,
username,
client,
process,
program,
dbname,
procname,
error_data,
sql_error,
isam_error
)
AS
SELECT
date_time,
session,
username,
client,
CASE
WHEN process > 0
THEN process
END,
CASE
WHEN program MATCHES 'Thread\[* path:*\]'
-- Include only the driver path for JDBC
THEN SUBSTR (
REPLACE(program,']',''),
CHARINDEX(' path:', program) + 6
)
ELSE program
END,
dbname,
procname,
error_data,
DECODE(sql_error, 0, NULL, sql_error)
|| ': ' ||
REPLACE (
NVL(b.error_text,'Unknown error'), '%s',
NVL(a.error_data,'%s')
),
DECODE(isam_error, 0, NULL, isam_error)
|| ': ' ||
REPLACE (
NVL(c.error_text,'Unknown error'), '%s',
NVL(a.error_data,'%s')
)
FROM sp_exception_ext AS a
LEFT OUTER JOIN errmsg AS b ON b.error_code = sql_error
LEFT OUTER JOIN errmsg AS c ON c.error_code = isam_error
WHERE instance = DBSERVERNAME;
GRANT SELECT ON sp_exception_view TO PUBLIC;
The following can be used as a test:
CREATE PROCEDURE sp_exception_test();
-- Example usage and test of "sp_exception"
-- Doug Lawry, Oninit Consulting, September 2023
DEFINE sql_error INT;
DEFINE isam_error INT;
DEFINE error_data VARCHAR(255);
ON EXCEPTION SET sql_error, isam_error, error_data
CALL oninit:sp_exception ('sp_exception_test', sql_error, isam_error, error_data);
RAISE EXCEPTION sql_error, isam_error, error_data;
END EXCEPTION;
SELECT * FROM no_such_table INTO TEMP temp_table;
END PROCEDURE;
EXECUTE PROCEDURE sp_exception_test();
That produces this message:
SQL Error (-206): The specified table (no_such_table) is not in the database.
The details of the last error can be retrieved as follows:
SELECT FIRST 1 *
FROM oninit:sp_exception_view
ORDER BY 1 DESC;
In this case, that returns:
date_time 2023-10-16 15:48:19
session 185
username informix
client ifx
process 5905
program /opt/ibm/IDS/14.10.FC10/bin/dbaccess
dbname oninit
procname sp_exception_test
error_data no_such_table
sql_error -206: The specified table <table-name> is not in the database.
isam_error -111: ISAM error: no record found.
We found the following useful to get a concise aggregated summary from a production system:
SELECT
MIN(date_time) AS min_date_time,
MAX(date_time) AS max_date_time,
COUNT(*) AS errors,
SUBSTRING_INDEX(sql_error, ':', 1) AS sql_error,
SUBSTRING_INDEX(isam_error, ':', 1) AS isam_error,
username,
client,
program,
procname,
error_data
FROM sp_exception_view
WHERE dbname = 'rx_hub'
GROUP BY 4, 5, 6, 7, 8, 9, 10
ORDER BY 1;
SELECT UNIQUE sql_error AS legend
FROM sp_exception_view
UNION ALL
SELECT UNIQUE isam_error
FROM sp_exception_view
WHERE isam_error IS NOT NULL
ORDER BY 1;
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:
# missing_errmsg.sh
# List missing Informix error code descriptions
# Doug Lawry, Oninit Consulting, October 2023
set -e # abort on exit
SCRIPT=$(basename $0 .sh)
MSGDIR=$INFORMIXDIR/msg/en_us/0333
# Four Js equivalent of "mkmessage" has a reverse option
# which can be used beforehand to decompile SQL messages:
# fglmkmsg -r $MSGDIR/sql.iem > sqlmsg.txt
# Save error numbers with first line of text in unload format:
awk '
/^\.-[0-9]/ {
NUM = substr($0,2,9)
getline
print NUM "|" $0
}
' sqlmsg.txt | sort -n > sqlmsg.unl
sort -n $MSGDIR/errmsg.txt |
awk '
BEGIN {FS="\t"}
$1==last || $1=="" || $2=="" {next}
/^-[0-9]/ {
gsub("[|\\\\]","\\\\&")
print $1 "|" $2
last=$1
}
' > errmsg.unl
# Compare the two sets:
dbaccess sysmaster > $SCRIPT.log 2>&1 <<EOF
CREATE TEMP TABLE sqlmsg (code INT PRIMARY KEY, text VARCHAR(90));
CREATE TEMP TABLE errmsg (code INT PRIMARY KEY, text VARCHAR(90));
LOAD FROM 'sqlmsg.unl' INSERT INTO sqlmsg;
LOAD FROM 'errmsg.unl' INSERT INTO errmsg;
UNLOAD TO '$SCRIPT.unl'
SELECT s.*
FROM sqlmsg AS s
LEFT JOIN errmsg AS e
ON e.code = s.code
WHERE e.code IS NULL
ORDER BY 1;
EOF
sed -e 's/|/\t/' -e 's/|//' $SCRIPT.unl > $SCRIPT.txt
That produces both a load file and a more readable plain text file. The former can be added into our reference table with:
LOAD FROM 'missing_errmsg.unl' INSERT INTO errmsg;
You may not have “fglmkmsg”, so the contents of the above load file are provided here:
-26816|Use of REPLCHECK must be consistent across the table hierarchy.|
-26467|The CREATE TRUSTED CONTEXT statement cannot be processed. You must specify|
-26039|Use of VERCOLS must be consistent across the table hierarchy.|
-26038|Cannot perform this operation on a table with version columns.|
-26035|Illegal usage of version columns.|
-26034|Cannot create temp table with VERCOLS|
-26033|Cannot create temp table with VERCOLS|
-25748|Incorrect server or cursor name format.|
-25741|The column type for (%s) does not match with the local definition.|
-25740|Access to remote User Defined Types in this version is not allowed.|
-25739|The column definition for (%s) does not match the local definition.|
-25735|Error writing to the remote file.|
-25734|File descriptor is invalid.|
-25732|Error closing the remote file.|
-25709|Server received an invalid id number for locating a remote server endpoint.|
-19818|Cannot open file for statement cache debug|
-19817|Invalid syntax for STMT_CACHE_DEBUG environment variable.|
-9972|ROW Type expected.|
-9968|Reference data type must reference a named row type (%s).|
-9966|Cannot create table with ref unless table is a typed table.|
-9964|Duplicate blob storage specification for column (%s)|
-9963|Cannot explicitly cast to sendrecv type.|
-9962|Non-collection host variable on right hand side of IN clause.|
-9961|Cannot drop inherited object (%s).|
-9952|Aggregate within a ROW in a SELECT projection list is not allowed.|
-9951|One of the names in dotted sequence is not right.|
-9942|Function (%s) needs cast to more specific type.|
-9941|Expecting subtype for constructor (%s).|
-9940|Bad constructor type information.|
-9929|Failure while getting unique constraints violations from table (%s).|
-9927|Cannot use distinct of TEXT/BLOB as a parameter type.|
-9925|Must use default type parameter for distinct of %s type.|
-9924|Return type of %s function does not match the expected type.|
-9921|Cannot locate source type xid (%d).|
-9914|Cannot set the start serial value when creating named row types.|
-9913|Collection derived table columns cannot be referenced in the 'where' clause of|
-9903|Not owner of domain.|
-9902|Domain (%s) not found.|
-9901|Domains are not supported.|
-9890|User defined function (%s) used in fragment expression does not match with any|
-9888|Can not unidle a shared user defined routine sequence.|
-9879|Access method does not support clustered index.|
-9877|NULL without cast not allowed for row/collection constructed type.|
-9876|Opclass (%s) is not defined for this access method.|
-9866|Cannot create external partition number.|
-9850|Compare routine (%s) cannot be a variant function.|
-9847|[Internal] Error while trying to set up start or end key for index read [%s]|
-9837|[Internal] Could not commute expression.|
-9834|[Internal] Could not find routine env (%d) for functional key.|
-9822|Can not delete smart-large-object.|
-9821|Space (%s) is not an sbspace.|
-9817|Functional key allowed only for Btree index.|
-9788|Only OP_UDR is allowed as part of iterator derived table.|
-9787|SLV's not allowed in the from clause.|
-9783|Statement local variable (%s) must be identified as a select number in the|
-9756|Modifiers COSTFUNC and PERCALL_COST cannot be used in the same routine.|
-9755|Modifiers SELCONST and SELFUNC cannot be used in the same routine.|
-9749|External functions are not yet supported in an iterator (cursory procedure)|
-9747|Passing arguments in C style not yet supported when using parameter defaults.|
-9746|(EV1 only) passing arguments in C style not supported for BUILTIN routines.|
-9745|Passing arguments in C style not yet supported for SPL routines.|
-9742|Implicit system cast failed.|
-9655|Cannot rename a column in a typed table.|
-9652|Grant/Revoke UNDER on untyped table is not allowed.|
-9644|Type (%s) does not have Equal function.|
-9622|Collection variable (%s) cannot be defined as Global.|
-9619|Variable (%s) is not of collection type.|
-9604|Index not allowed on collections.|
-9603|Illegal attempt to use collection host variable.|
-9600|Internal error.|
-9497|Scroll cursor is not a supported feature on the server JDBC yet.|
-9421|Cannot use LOCKSSFU on queries resulting in table scan.|
-8329|Write failed. %lld rows unloaded (check ulimit or disk space).|
-8325|Distinct clause is not allowed on a sequence.|
-8323|Remote sequence is not supported currently.|
-5904|Truncate Table cannot be used inside a transaction that already started|
-5903|Only COMMIT WORK command is valid after TRUNCATE inside a transaction|
-5902|Truncate Table cannot be rolled back|
-5901|Can't truncate table because an enabled referential integrity constraint exists.|
-5824|Improper target access method used to alter table or index.|
-5823|HASH_AM: An index cannot be altered to cluster on a hashed table.|
-5822|HASH_AM: A clustered index cannot be created on a hashed table.|
-5821|HASH_AM: Hash key column of the inserted row has null value.|
-5820|HASH_AM: Fragment by expression columns must be subset of the hashkey.|
-5819|HASH_AM: Unsupported fragmentation strategy.|
-5818|HASH_AM: Fillfactor is too large or too small.|
-5817|HASH_AM: Illegal or missing value for fillfactor.|
-5816|HASH_AM: Average_rowsize specified for fixed length records.|
-5815|HASH_AM: Illegal or missing value for average_rowsize.|
-5814|HASH_AM: Average_rowsize specified is too large.|
-5813|HASH_AM: Average_rowsize specified is too small.|
-5812|HASH_AM: Table has columns of complex types.|
-5811|HASH_AM: Hashkey paramater is mendatory for a static hashed table.|
-5810|HASH_AM: Illegal or missing value for Number_of_rows parameter.|
-5809|HASH_AM: Number_of_rows parameter is mendatory for static hashed table.|
-5808|HASH_AM: Row size too big to fit the page with given fillfactor.|
-5807|HASH_AM: Unknown parameter.|
-5806|HASH_AM: Unknown or missing mode.|
-5805|HASH_AM: Hashkey contains non-hashable column.|
-5804|HASH_AM: Hashkey contains unknown column.|
-5803|HASH_AM: Syntax error in hashkey specification.|
-5802|HASH_AM: No parameters specified.|
-5801|HASH_AM: Cannot use hash access method to create index.|
-920|Cannot read host address in network data base.|
-902|User not authorized or too many entries in authorization file. |
-901|User not found in network user authorization file.|
-900|Cannot read network user authorization file.|
-890|Cursor must be declared on an INSERT statement with a VALUES clause.|
-224|Cannot open transaction log file.|
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.