Informix Error -111

Reasons / Common Causes

-111 means a keyed lookup didn't match anything — like -110, this is often a normal outcome rather than a defect, but unlike -110 (a predictable end-of-scan signal), -111 covers several genuinely different situations that are worth telling apart:

1. The value genuinely doesn't exist. A lookup for a specific key value, when nothing has that value, is expected to return -111 — this is the ordinary, correct outcome for a "does this exist?" check that comes back negative.
2. The wrong index is in use. The official text calls this out directly: a program can specify the wrong key number for a lookup — one that doesn't correspond to the column(s) the code actually means to search by — so a value that would match under the intended index fails to match under whichever one was actually used. This is especially easy to introduce after a schema or key-numbering change that the calling code wasn't updated to match.
3. A formatting mismatch between the search value and the stored value — trailing spaces, case differences (under a case-sensitive collation), or leading-zero/padding differences between what the code constructs and what's actually indexed. The values look the same to a human reading the code, but aren't equal at the index level.
4. A visibility/isolation issue — the record exists, but was inserted by another transaction that hasn't committed yet, and the current session's isolation level doesn't see uncommitted data. The lookup is correctly reporting "not found from where I'm standing," not lying.
5. Incorrect construction of a compound/composite key — a mismatched part order, wrong length, or missing padding produces a well-formed key value that legitimately doesn't match the value actually stored, even though the individual field values going into it are correct.
6. A record-number-based retrieval referencing a record that was deleted, or a record number from a stale reference taken before a delete or reorganization.

Solutions / Resolution

1. Treat -111 as a normal "not found" outcome by default — build code paths that distinguish "nothing matched" (an expected branch) from "something went wrong" (a real error), rather than routing both through the same failure handling.
2. Verify which key/index number is actually in use, especially after any change to key ordering or index definitions — this is the official guidance's own top recommendation, and it's the right first check whenever -111 appears where a match was clearly expected.
3. Check the exact value being searched against the stored value's actual format — query the data directly with case/trim variations if a match seems like it should exist:

   SELECT * FROM customer WHERE UPPER(TRIM(email)) = UPPER(TRIM('a@example.com'));
   

   A match here that the original lookup didn't find confirms a formatting mismatch rather than a genuinely missing row.
4. Consider transaction visibility if the "missing" record was just inserted by another session — confirm whether that transaction has actually committed, and what isolation level the failing lookup is running under.
5. Review compound-key construction logic for part order, length, and padding if the failing key is composite — reconstruct the exact key value the code builds and compare it byte-for-byte against what's actually stored.
6. Log the exact key value and key number immediately before a lookup that unexpectedly fails — this turns "-111 happened somewhere in this function" into a directly diagnosable mismatch.

Examples

The wrong index, not a missing value

/* File has key 0 = customer_id, key 1 = email */
isstart(fd, 0, &search_key, ISEQUAL);   /* searching key 0 (customer_id)... */
isread(fd, &record, ISCURR);
/* -111, because search_key actually holds an email address —
   the caller meant to search key 1, not key 0 */

The value being searched for exists in the file — just not under the key number the code actually used.

The invisible formatting mismatch

-- Stored value: 'a@example.com  ' (trailing spaces from an old import)
SELECT * FROM customer WHERE email = 'a@example.com';   -- SQLCODE 100, "not found"

The row is there; the stored value simply isn't equal to the search value as far as the engine is concerned. TRIM() on both sides during the search (and cleaning the stored data) resolves this class of mismatch.

Racing an uncommitted insert

-- Session A                                  -- Session B
INSERT INTO orders VALUES (...);
-- not yet committed
                                               SELECT * FROM orders WHERE order_id = :new_id;
                                               -- -111 / SQLCODE 100: not visible yet
COMMIT;
                                               -- Session B's next attempt succeeds

Session B's query wasn't wrong — the row genuinely wasn't visible to it at the time it ran.

Diagnostic Checks

1. Log the exact key value and key/index number used in the failing lookup, right at the call site — this is the fastest way to catch a wrong-index or malformed-key bug.
2. Query the data directly with formatting variations (TRIM, UPPER) to rule out a cosmetic mismatch between the search value and the stored value.
3. Review recent schema or key-numbering changes if -111 started appearing on previously reliable lookups — a renumbered or reordered index is a common trigger.
4. Check commit status and isolation level if the "missing" record was expected to have been inserted moments earlier by another session.
5. For SQL, confirm the standard NOTFOUND handling (SQLCODE == 100) is what's actually being checked, rather than treating every non-zero return the same way.

Related Errors / Related Topics

• -100 — "ISAM error: duplicate value for a record with unique key." The other foundational ISAM-level error in this family.
• -110 — "ISAM error: end or beginning of the file." Like -111, often a normal, expected outcome rather than a defect — the two are frequently confused with genuine errors by code that doesn't distinguish "nothing (more) to find" from "something went wrong."

Before debugging -111 as though it were a bug, confirm the record really is expected to exist right now, under the exact key being used — a large share of -111 reports turn out to be correct behavior once that's checked.