Oninit
Talk to an expert →

Informix Error -221

-221 Cannot build temporary file for new table table-name.

The database server cannot create a temporary disk file. The file should be created in the directory specified by the DBTEMP environment variable. If DBTEMP is not defined, the database server searches by default in the root directory for a subdirectory named tmp in which to create the temporary disk file. Check the accompanying ISAM error code, and look for operating-system error messages that might give more information. Possibly your account does not have write permission in that directory, or the disk might be full. Since Version 5.01, IBM Informix SE database server uses the DBTEMP environment variable, but IBM Informix Dynamic Server and IBM Informix OnLine Dynamic Server do not.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-221 means the database server couldn't create a temporary disk file for a new table. Where it looks for that file depends entirely on which product is in use — a distinction the official text is explicit about, and one that's easy to get wrong when troubleshooting.

  1. Insufficient disk space in the target temp directory.
  2. Permission issues — the account running the database server process lacking write permission in the temp directory.
  3. DBTEMP not defined and the default fallback directory unsuitable. For IBM Informix SE, if DBTEMP isn't set, the server falls back to a tmp subdirectory under the root directory — which may not exist, may not be writable, or may simply be the wrong place for this workload.
  4. Confusion between DBTEMP (SE-specific) and DBSPACETEMP (Dynamic Server/OnLine Dynamic Server). The official text is explicit: DBTEMP is used by IBM Informix SE since Version 5.01, but not by Dynamic Server or OnLine Dynamic Server. Troubleshooting -221 on Dynamic Server by checking or setting DBTEMP looks in the wrong place entirely — see -179/-199 for the DBSPACETEMP mechanism those products actually use.
  5. General OS-level issues — hardware or filesystem problems affecting the temp directory.
Solutions / Resolution
  1. Check the accompanying ISAM error code and OS-level error messages for the specific cause, per the official guidance.
  2. Check disk space in the target temp directory.
  3. Check write permissions for the database server account in the temp directory.
  4. For Informix SE, set DBTEMP explicitly to a known-good directory rather than relying on the default tmp-under-root fallback, especially if that default isn't suitable.
  5. For Dynamic Server or OnLine Dynamic Server, check DBSPACETEMP instead — DBTEMP simply doesn't apply to these products, per the official guidance.
  6. Confirm which specific product is actually in use before deciding which configuration mechanism governs temp file location — this determines the entire troubleshooting direction.
Examples
Checking the right variable for the right product
-- For Informix SE:
echo $DBTEMP

-- For Dynamic Server / OnLine Dynamic Server, check DBSPACETEMP
-- in ONCONFIG instead — DBTEMP has no effect on these products
grep -i "^DBSPACETEMP" $ONCONFIG
The default fallback not being suitable
-- Informix SE, DBTEMP unset, falls back to /tmp
$ ls -ld /tmp
drwxr-xr-x 2 root root 4096 Sep 20 10:00 /tmp
-- database server account may not have write access here,
-- or /tmp may be too small/full for the workload

Setting DBTEMP explicitly to a directory the server account can write to, with adequate space, resolves this.

Diagnostic Checks
  1. Check the accompanying ISAM error code.
  2. Confirm which product is in use (SE vs. Dynamic Server/OnLine Dynamic Server) before checking DBTEMP or DBSPACETEMP respectively.
  3. Check disk space and permissions in the relevant temp directory.
Related Errors / Related Topics
  • -179 — "ISAM error: No free disk space for sort." The DBSPACETEMP/PSORT_DBTEMP configuration sibling for Dynamic Server products.
  • -199 — "ISAM error: Dbspace is full." Another temp-space-related condition tied to DBSPACETEMP mode specifically.

Confirm which product is actually in use before troubleshooting further — DBTEMP and DBSPACETEMP are not interchangeable, and checking the wrong one wastes time entirely.