Informix Error -212

Reasons / Common Causes

-212 is a generic wrapper around a failed index creation — either an explicit CREATE INDEX, or an implicit one the optimizer builds internally to process a SELECT joining multiple unindexed tables. The official text names insufficient disk space as the common cause, and points to the accompanying ISAM error code for the specific detail.

1. Insufficient disk space — the officially named common cause, whether for an explicit CREATE INDEX or an implicit temp index the optimizer creates during query processing.
2. Various other ISAM-level failures during index creation, identified by the accompanying ISAM error code — a duplicate key if creating a unique index against data that already has duplicates (see -100), an illegal key descriptor if the column combination is invalid (see -103), or other space/extent issues (-131, -136, -137, -179).
3. An implicit index triggered by a SELECT joining multiple unindexed tables. This is as much a query-design signal as an error — the underlying tables lacking proper indexes is what forced the optimizer to build a temporary one in the first place.
4. An explicit CREATE INDEX failing due to a data issue — attempting a UNIQUE index against data that already contains duplicate values.

Solutions / Resolution

1. Check the accompanying ISAM error code first, per the official guidance — this generic wrapper needs that companion code to identify the specific cause.
2. Check available disk space given how directly the official text names it as the common cause.
3. For an implicit index triggered by an unindexed join, add permanent indexes to the tables involved — this both resolves the immediate error and improves the query's performance going forward, rather than relying on the optimizer's implicit temp-index mechanism repeatedly.
4. For an explicit CREATE INDEX failing on a data issue, resolve the underlying data problem (per -100's guidance for duplicate-key conditions) before retrying the index creation.
5. Address whatever the accompanying ISAM-level code names directly, using that specific error's own guidance in this catalogue.

Examples

Checking disk space first

onstat -d
-- confirm free space in the relevant dbspace before investigating further

An implicit index from an unindexed join

SELECT * FROM orders o, customer c, products p
WHERE o.customer_id = c.id AND o.product_id = p.id;
-- if none of these join columns are indexed, the optimizer may
-- build a temporary index to process this efficiently, and -212
-- if that temp index creation fails

Adding real indexes on customer.id, products.id, and the corresponding foreign-key columns in orders addresses both the error and the query's underlying performance.

A UNIQUE index against duplicate data

CREATE UNIQUE INDEX cust_email_uq ON customer (email);
-- -212, with an accompanying -100: duplicate values already exist

Resolve the duplicate data first (see -100's diagnostic checks), then retry the index creation.

Diagnostic Checks

1. Check the accompanying ISAM error code for the specific underlying cause.
2. Check disk space in the relevant dbspace or temp space.
3. Review whether the failing statement is an explicit CREATE INDEX or an implicit one arising from an unindexed join, and address accordingly.

Related Errors / Related Topics

• -131 — "ISAM error: no free disk space." The officially named common cause behind this error.
• -100 — "ISAM error: duplicate value for a record with unique key." Relevant when the accompanying error indicates a data conflict rather than a space issue.

Check the accompanying ISAM error code before anything else — -212 alone only tells you an index couldn't be created, not why.