Enhanced error message lookup using the basic text search (BTS) extension

This tutorial shows how to use the Basic Text Search (BTS) extension to create a searchable database of Informix® error messages.

The BTS extension is a fast and flexible way to make free-form text searches available in applications.

The usual method for looking up an Informix error message is to use the finderr utility. The finderr utility is a command-line utility that takes an error number as an argument. Suppose you want to do more than find an SQL error by number; perhaps you are troubleshooting a problem and want to find which errors are related to referential key constraints or virtual-index interface (VII) indexes.

A simple way to provide a more sophisticated search for Informix error messages is to load the error numbers and their definitions into a database and create a Basic Text Search (BTS) index on the text. The Basic Text Search extension is an Informix feature that uses the power of the CLucene open source text search engine to support searching for words and phrases in unstructured text.

What follows is a step-by-step guide to creating a database to index the Informix error messages and search them using the BTS extension.

Create an sbspace

The Basic Text Search extension uses a logged sbspace to store index information. To create a sbspace, use the onspaces -c command.

Open a terminal window and enter the following commands to create the directories and set permissions:

mkdir /home/informix/chunks
touch /home/informix/chunks/bts_sbspace
chown informix:informix /home/informix/chunks/bts_sbspace
chmod 660 /home/informix/chunks/bts_sbspace

Use the onspaces command to create the blobspace:

onspaces -c -S bts_sbspace -p  /home/informix/chunks/bts_sbspace 
   -o 0 -s 30720 -Df "LOGGING=ON"

Create a BTS virtual processor

The steps in this section are for information only; the BTS virtual processor is already been configured in the HCL Informix Virtual Appliance onconfig file.

The Basic Text Search engine runs in its own Virtual Processor class, so to use BTS add a VPCLASS entry to the onconfig file.

Use the following command to view all VPCLASS settings:
```
onstat -c VPCLASS
```
If the output from the onstat command displays bts,noyield,num=1 then the virtual processor is configured and you can ignore the rest of the steps and proceed to Create the error message database.
Use a text editor to open the onconfig file.
Add the following information to the onconfig file:
```
# for BTS search
VPCLASS bts,noyield,num=1
```
Restart Informix for the VPCLASS parameter to take effect.

Create the error message database

The finderr database uses a single LVARCHAR column table with each row representing one error message (including number and text). In the example below, no dbspace is specified in the CREATE DATABASE statement, which means that the database is created in the root dbspace:

In a terminal window, enter the following commands:

dbaccess <<EOF
create database finderr with buffered log;
create table errtable(errcol lvarchar(10000));
EOF

Load the error message data

The list of Informix error messages is supplied with the Informix media in a file called errmsg.txt. In distributions using the standard US locales the file is found here on Linux or UNIX systems: $INFORMIXDIR/msg/en_us/0333/errmsg.txt.

To convert the errmsg.txt file into a format that can be loaded into the errtable table add a pipe "|" character between each message, and escape every end-of-line with a backslash "\" character. This conversion involves more than a simple sed script because the number of blank lines between messages is not always consistent. A formatted error message file can be found in: /opt/IBM/informix/FirstSteps/BTS/errmsg.unl

In a terminal window, enter the following command to load the database:

echo load from '/opt/IBM/informix/FirstSteps/BTS/errmsg.unl' 
   insert into errtable | dbaccess finderr

Register the BTS extension

This step is optional for Informix release 11.70 and higher. The BTS DataBlade is automatically registered in versions of Informix later than 11.70

If you are using a version Informix older than 11.70, you must register the BTS DataBlade module with the finderr database. If necessary, you can register the BTS DataBlade module by running the blademgr command-line utility as user informix:

Determine the version of the BTS extension by typing the following command in a terminal window:
```
ls -d $INFORMIXDIR/extend/bts.*
```
The output of the command displays the directory in which the BTS module is stored and the version of the BTS DataBlade module. For example, if the version you are using is 3.00, the following is displayed:
```
/opt/IBM/informix/extend/bts.3.00
```
In a terminal window, start the blademgr utility:
```
blademgr
```
Register the finderr database using the result of the command from step 1. For example, if step 1 displayed /opt/IBM/informix/extend/bts.3.00 then enter the following command:
```
register bts.3.00 finderr
```

Type "y" when prompted with:

Register module bts.3.00 into database finderr? [Y/n].

After the database is created, exit from the blademgr utility:
```
quit
```

Create the BTS index

With the BTS extension registered and an external dbspace in place, you can create a BTS index on the errcol column.

dbaccess finderr <<EOF
create index err_bts on errtable (errcol bts_lvarchar_ops)
  using bts in bts_sbspace;
EOF

In this example, bts_lvarchar_ops is an operator class that corresponds to the LVARCHAR data type. An operator class represents a set of functions for Informix to associate with the BTS access method. A similar operator class exists for every data type that can be represented as text (BLOB, CHAR, CLOB, LVARCHAR, NCHAR, NVARCHAR, VARCHAR).

The BTS index can take a few minutes to create, and after it is created, the database is ready for queries.

Running queries

With the index in place, queries on the errcol column would take the form:

select errcol from errtable where bts_contains(errcol,"xxx");

where "xxx" represents a search string that conforms to the Lucene search syntax. For example, suppose you want to search for error messages that contain the words "unlock" and "table" but not "transaction", the query would be:

select errcol from errtable where bts_contains(errcol,
   "unlock AND table AND NOT transaction");

You can replace the command-line finderr utility by writing a shell script that constructs a similar query that is based on the command-line arguments:

dbaccess finderr <<EOF
select errcol from errtable where bts_contains(errcol,"$*");
EOF

Here is example output from the script:

Database selected.




errcol  -12020  Smart Large Objects: Cannot unlock table entry.

        If this error recurs, note all circumstances and contact Technical Supp
        ort at tsmail@us.ibm.com.



errcol  -291    Cannot change lock mode of table.

        The current LOCK TABLE statement cannot be executed because you have
        already locked the same table using a different mode (EXCLUSIVE or
        SHARE). To change the lock mode, arrange to unlock the table before
        you lock it again.



errcol  -153    ISAM error: not in ISMANULOCK mode.

        The ISAM processor has been asked to lock or unlock the current file
        (table), but the file was not opened in the appropriate mode. For
        C-ISAM programs, review the uses of isopen, and make sure that the
        ISMANULOCK flag is passed when the program opens a table for manual
        locking. If the error recurs, note all circumstances and
        contact Technical Support at tsmail@us.ibm.com.



3 row(s) retrieved.

To use this script in production, you must allow it to escape characters that are treated as special characters by the BTS engine. For example, to look up an error number such as -291, the script must escape the negative sign twice; one time for the command shell and one time for the BTS parser:

btserr \\-291

A good way to use this type of index would be as part of a PHP or Java™ web application that ica capable of parsing any special characters before it constructs the query, and be available to all users. Another method is to make the error message database available through an instant message chat bot.