Informix Error -196

Reasons / Common Causes

-196 is a storage-space type mismatch: an operation requiring functionality temporary dbspaces don't provide — most likely transaction logging — was attempted against one. Temporary dbspaces are designed for transient sort/temp-table space, not durable, recoverable storage, so they don't support the same feature set as a nontemporary dbspace.

1. An operation requiring transaction logging attempted against a table in a temporary dbspace — the direct cause, and the specific functionality the official text names as most likely missing.
2. A permanent table meant for real data accidentally created in a temporary dbspace instead of a nontemporary one — a naming/configuration mix-up similar in spirit to -170's dbspace/blobspace confusion, here between temporary and nontemporary dbspaces.
3. Application code or scripts dynamically selecting a dbspace for a new table without validating whether it's temporary or permanent, landing on a temp dbspace by mistake.
4. A DBSPACETEMP misconfiguration causing an operation intended for a real dbspace to resolve to a temporary one instead.

Solutions / Resolution

1. Use a nontemporary dbspace for the operation, per the official guidance — identify the correct permanent dbspace and redirect the operation there.
2. Review dbspace selection logic in application code or scripts to ensure permanent objects are created in nontemporary dbspaces specifically, not wherever a generic selection routine happens to land.
3. Adopt naming conventions that clearly distinguish temporary from nontemporary dbspaces — the same discipline -170 recommends for blobspaces vs. dbspaces — to reduce this class of mistake.
4. Review DBSPACETEMP configuration if an unintended resolution to a temporary dbspace is suspected.

Examples

The mistaken target

CREATE TABLE customer (id INT, name VARCHAR(50)) IN tempdbs1;
-- -196: tempdbs1 is a temporary dbspace — this table needs logging
-- support that temporary dbspaces don't provide

Fix:

CREATE TABLE customer (id INT, name VARCHAR(50)) IN dbspace1;
-- dbspace1 is a nontemporary dbspace — this succeeds

Naming conventions that prevent the mistake

-- Clear naming distinguishes the two types at a glance:
dbspace_primary, dbspace_archive    -- nontemporary
tempdbs1, tempdbs2                  -- temporary

Diagnostic Checks

1. Confirm the type of the dbspace involved (temporary vs. nontemporary):

   onstat -d
   

2. Review the specific operation attempted — most operations that hit this need logging support — and confirm it's targeting the wrong space type.

Related Errors / Related Topics

• -170 — "ISAM error: illegal use of a storage space for TEXT or BYTE data." The direct structural sibling — same general category of storage-space type mismatch, applied to blobspaces vs. dbspaces rather than temporary vs. nontemporary.
• -122 — "ISAM error: transaction not available." Related through the same underlying concern — transaction logging support — that temporary dbspaces specifically don't provide.

Confirm the dbspace's actual type before attempting an operation that needs logging support — a quick, direct check that resolves the confusion immediately.