Informix Error -9665: Cannot create attached indexes on boolean, lvarchar, user defined data types and fragmented tables.

Cause and resolution

Cannot create attached indexes on boolean, lvarchar, user defined data types and fragmented tables.

Specifying IN TABLE as the storage option for create index statement, creates a legacy attached index. This attached index supports the legacy behavior of Version 7.x of IBM Informix, in which the pages of nonfragmented B-tree indexes on nonfragmented tables are stored, by default, in the same dbspace partition as the data pages. These legacy behavior attached indexes are not supported on boolean, lvarchar, user defined data types and fragmented tables.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-9665 fires when a legacy "attached index" (IN TABLE storage option) is created on a BOOLEAN, LVARCHAR, or user-defined-type column, or on a fragmented table — per the official guidance.

IN TABLE used to create an attached index on an unsupported column type or a fragmented table, per the official guidance — the direct, only cause; attached indexes reproduce Version 7.x legacy behavior (B-tree index pages stored in the same dbspace partition as the data pages) and don't support these cases.

Solutions / Resolution

Avoid the IN TABLE storage option for indexes on BOOLEAN, LVARCHAR, or UDT columns, or on fragmented tables, per the official guidance's implication — create the index without IN TABLE instead.

Examples

An attached index on an unsupported column type

CREATE INDEX idx1 ON tab(bool_col) IN TABLE;   -- -9665: bool_col is BOOLEAN

Corrected

CREATE INDEX idx1 ON tab(bool_col);

Diagnostic Checks

Check the indexed column's data type and whether the table is fragmented, and drop IN TABLE if either applies.

Related Errors / Related Topics

No closely related error codes are cross-referenced for -9665 in this set yet.

An IN TABLE attached index was attempted on an unsupported column type or a fragmented table — create the index without IN TABLE.