Informix Error -104

Reasons / Common Causes

-104 means the ISAM processor has hit its ceiling on concurrently open files. The official text already splits the two realistic populations cleanly: C-ISAM programs that are genuinely holding too many files open at once, and SQL statements whose execution plan requires the engine to open more tables, fragments, or indexes simultaneously than the limit allows. Which one applies is usually obvious from context (a hand-written ISAM program vs. a query), but the underlying causes differ enough to treat separately.

For C-ISAM programs:

1. A straightforward accumulation — the program opens files as it goes and never closes ones it no longer needs, so the open count only grows over the life of the process.
2. A leak on an error or early-return path. The common-case code path calls isclose() correctly; a retry loop, an exception/error branch, or an early return skips it. Each time that path is taken, one more handle leaks, and the failure doesn't appear until the accumulated leaks finally cross the limit — often long after, and far from, the code that actually leaked.
3. No single owner for a file's lifetime — the same pattern behind -101, but the opposite symptom: instead of something using a file after it's closed, nothing closes it because each piece of code assumes another piece is responsible.

For SQL statements:

4. A query joining too many tables concurrently. Each table (and, for a fragmented table, potentially each fragment) the plan touches needs its own open file at the storage layer; a sufficiently wide join can exceed the limit even though no single table involved is unusual.
5. PDQ (parallel database query) amplifying concurrent opens. The official text calls this out directly: a trigger procedure running with PDQ enabled can open many tables at once during constraint processing — referential-integrity checks cascading through a set of related tables in parallel multiplies the concurrent-open-file count well beyond what the same logic run serially would need.
6. Heavy table fragmentation. A table split into many fragments is, from the storage layer's perspective, many files — a query against a single but aggressively fragmented table can exhaust the same limit that a normal query would never approach.
7. Trigger cascades — triggers firing further triggers or DML across a wide set of related tables within one transaction, each additional table adding to the concurrently-open count for the duration of that transaction.

Solutions / Resolution

For C-ISAM programs:

1. Audit every isopen() for a matching isclose() on every exit path, not just the common-case success path — error branches, early returns, and retry loops are where leaks hide.
2. Give each file handle a single owner responsible for closing it, rather than letting multiple parts of the program each assume someone else will.
3. Restructure to reduce concurrently-open files where the logic allows it — process one file at a time in sequence instead of holding many open across the whole operation.

For SQL statements:

4. Break the query into steps using temporary tables, exactly as the official text recommends — this is the standard fix for a single query that's simply too wide, and reduces how many tables any one execution needs open at once.
5. If PDQ is amplifying a trigger cascade, adjust or disable parallelism for that statement/session:

   SET PDQPRIORITY 0;
   

   and re-test — if the error disappears, the fix is either accepting serial execution for that specific operation or restructuring the trigger chain so it doesn't need PDQ's parallelism across so many tables at once.
6. Review fragmentation strategy for tables involved in the failing query — if fragment count is unusually high relative to data volume, consider consolidating fragments.
7. Flatten or stage trigger cascades — if one transaction is triggering DML across many related tables, consider whether the cascade can be restructured into staged batch operations instead of a single wide transaction.
8. Raise OS-level file descriptor limits (ulimit -n) only after ruling out a leak — raising the ceiling on a genuine leak just delays the failure, it doesn't fix it.

Examples

The error-path leak

int fd = isopen("staging", ISINPUT);
if (validate_header(fd) < 0) {
    return -1;                 /* leaked: isclose(fd) never runs on this path */
}
process_records(fd);
isclose(fd);                   /* only reached on the success path */

Every call that takes the validation-failure branch leaks one handle. The failure shows up much later, as -104, once enough leaked handles accumulate — nowhere near the line that actually caused it.

The query that's simply too wide

SELECT o.order_id, c.name, i.sku, w.location, s.status, p.method
FROM   orders o
JOIN   customers c ON c.id = o.customer_id
JOIN   items i      ON i.order_id = o.order_id
JOIN   warehouses w ON w.id = i.warehouse_id
JOIN   shipments s  ON s.order_id = o.order_id
JOIN   payments p   ON p.order_id = o.order_id
WHERE  o.status = 'pending';
-- On a system near its open-file ceiling, a plan touching six tables
-- (plus their indexes) at once can trip -104 where none of them would
-- individually.

Recommended fix, per the official guidance — stage it:

SELECT * INTO TEMP tmp_orders FROM orders WHERE status = 'pending';
-- join the remaining tables against the much narrower tmp_orders
-- in subsequent steps, rather than all six tables in one plan

PDQ-amplified trigger cascade

A single DELETE on a parent table fires triggers enforcing referential integrity across a dozen child tables; with PDQ enabled, the engine parallelizes that constraint processing, opening many of those tables concurrently instead of one after another. Disabling PDQ for that specific statement (SET PDQPRIORITY 0) serializes the cascade and keeps the concurrent-open-file count low enough to succeed, at the cost of that one statement running slower.

Diagnostic Checks

1. For C-ISAM programs, add open/close counting (a running counter incremented on isopen() success and decremented on isclose()) and log it periodically — a counter that only grows is a confirmed leak, independent of ever hitting -104 itself.
2. For SQL statements, check the execution plan:

   SET EXPLAIN ON;
   -- run the failing statement
   

   and count the distinct tables/fragments the plan touches.
3. Check fragmentation for tables in the failing query:

   SELECT tabname, COUNT(*) AS fragments
   FROM   sysfragments f, systables t
   WHERE  f.tabid = t.tabid
   GROUP BY tabname
   HAVING COUNT(*) > 10;
   

4. Check whether PDQ is enabled for the session or statement in question, and whether the failing operation involves trigger-driven cascades across multiple tables.
5. At the OS level, compare the engine process's actual open file count against its limit:

   lsof -p <oninit pid> | wc -l
   ulimit -n
   

   A count that climbs steadily over the life of a long-running process, rather than staying roughly flat, points at a leak rather than a one-time complex operation.

Related Errors / Related Topics

• -100 — "ISAM error: duplicate value for a record with unique key." The other foundational ISAM-level error in this family.
• -101 — "ISAM error: file is not open." The mirror-image lifecycle problem: -101 is a file used after (or before) it was actually open; -104 is too many files left open at once. Programs that get the open/close discipline wrong in one direction are often at risk of the other.

If -104 is coming from a query rather than a hand-written ISAM program, don't look for a leak — look at the plan's table/fragment count and whether PDQ is amplifying it, per Solutions above.