From: shauser@epx.cis.umn.edu (Steven Hauser)
Newsgroups: comp.databases.informix
Subject: Informix error script enclosed
Date: 16 May 1995 23:46:06 GMT

The following is a shell-dbaccess script to create an error message
table from the Informix distribution file $INFORMIXDIR/msg/errmsg.txt.
I use it to make zippy fast error lookups. Use finderr (or that perl
equivalent) only when the engine bloats up and dies.  

I use SPL to trap those pesky errors and have loads fun sending doubleplusgood
InformixSpeak into the eyeballs of confused programmers and users!

Write me and let me know if you find any bugs.  Of course, if anything bad 
happens from using this you have no one to blame but yourself, 
your on yer own, pal.

#!/bin/sh
# Process $INFORMIXDIR/msg/errmsg.txt into Informix load utility file format.
# Create a database and errmsg table, load table.
# Developed on Informix 5.xx release.
# Steven Hauser          shauser@epx.cis.umn.edu

usage () {
    echo
    echo Usage: $0 $INFORMIXDIR/msg/errmsg.txt
    echo Process $INFORMIXDIR/msg/errmsg.txt into Load utility file format.
    echo Create a test database and table, load table.
    echo
    exit
}

# Print records with the wrong number of fields. Print field lengths.
# Usage:  Verify < errmsg.unl
Verify () {
    nawk 'BEGIN {FS="|";OFS="|"}
         {print length($1), length ($2), length ($3);}
         NF != 3 {print}'
}

# Process $INFORMIXDIR/msg/errmsg.txt into Informix load utility file format. 
Format () {
    nawk '
        /^Informix Messages and Corrections/ {next}
        /^Informix Error Messages/ {next}
        /^[-1-9][0-9]+[\t]/ {sub ("\t", "|"); printf "\n%s| ", $0; next;} 
        !/^[-1-9][0-9]+[\t]/ {sub ("\n"," "); printf "%s", $0 }
        END { printf "\n" } '
}

#####################################
# Main

test $# -eq 1 || usage 

# Remove stray "|" delimiter, format file, remove blank line.
tr "\174" "X" < $1 | Format | sed '/^$/d' > errmsg.unl

# Use to find problem records.
# Verify < errmsg.unl

# Make a table, load error messages into it.
# Note: Many of the LongMesg fields are way longer than 255.
dbaccess << EOF
    drop database errtest;
    create database errtest;
    create table ErrMsg
     (
    ErrNo integer not null,     
    ShortMesg char (80),        {Short message.}
    LongMesg varchar(255)       {Long explanation, truncated.}
     );
    revoke all on ErrMsg from "public";
 
    load from "errmsg.unl" insert into errmsg;

    create index ErI1 on ErrMsg (ErrNo);
EOF