This article is written in English and Portuguese
Este artigo estÃ¡ escrito em InglÃªs e PortuguÃªs
This post’s title is an allusion to “It’s the economy, stupid” phrase that become popular in one of the USA presidential campaigns. It seems appropriate for today, and for the short problem description I’m going to describe.
Today, while working on a customer site, I was confronted with the following scenario:
A developer complained that two programs working against a non-logged database (yes, some customers still use them…) raised errors (-243/111) while DELETEing an overlapped result set. The customer DBAs replied the same as I probably would: “That can happen even on a non-logged database. Please use some sort of “SET LOCK MODE TO WAIT…’ “
But the program was already using it… So… why does it happen?!
Well… we all know that there are some differences between logged and non-logged databases, but a complete list is hard to find. One is that we cannot change the isolation level in a non-logged database. It raises an error stating that the only mode allowed is DIRTY READ. But it does accept the SET LOCK MODE WAIT statement, so we would expect it to enforce it…
In short, what was I missing, apart from a possible, but improbable bug? The answer is, I’m missing a careful analysis of the error codes! And I should be ashamed, because I have recommended many times that people MUST always look carefully into the errors, before trying to create explanations for problems that they haven’t qualified. Precisely what I was doing… So let’s see some facts:
- The instruction causing he error was something like:
DELETE FROM some_table WHERE indexed_field = VALUE
And the query plan used the index on the column
- Error -243 reads:
-243 Could not position within a table table-name.
The database server cannot set the file position to a particular row
within the file that represents a table. Check the accompanying ISAM
error code for more information. A hardware error might have occurred,
or the table or index might have been corrupted (truncated). Unless the
ISAM error code or an operating-system message points to another cause,
run the oncheck utility (secheck with IBM Informix SE or tbcheck with
IBM Informix OnLine) to check and repair table and index.
- Error -111 reads:
-111 ISAM error: no record found.
The ISAM processor cannot locate the requested record. For C-ISAM
programs, no record was found with the requested index value or record
number, depending on the retrieval mode in use. Make sure that the
correct index is in use. For SQL products, see the SQL error message or
return code. Probably no row was found for thi…