Informix Error -787

-787 Index fragmented same-as-table cannot be altered.

If you do not specify storage-spec in a CREATE INDEX statement, the indexes are fragmented the same as the underlying table, subject to all the restrictions on fragmented indexes. For example, an error is returned if the underlying fragmentation strategy is round-robin, and the index is unique. You cannot alter this type of index fragmentation. If you want to change the index fragmentation, use the INIT option of ALTER INDEX to detach the index. That operation makes the index and table independent and lets you alter each independently.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-787 fires when ALTER INDEX is attempted against an index that inherited its fragmentation scheme from the underlying table — per the official guidance, CREATE INDEX without an explicit storage-spec produces an index fragmented the same way as its table, and that kind of index can't be altered directly.

ALTER INDEX attempted on an index created without an explicit storage-spec, per the official guidance — the direct, only cause; such indexes are tied to the table's own fragmentation and can't be independently altered.
A misunderstanding of index fragmentation independence — assuming any index can be altered directly, without realizing "same-as-table" indexes are a special case tied to the table's own scheme.

Solutions / Resolution

Use ALTER FRAGMENT ON INDEX ... INIT to detach the index first, per the official guidance — this makes the index and table independently fragmented, after which the index can be altered on its own (there is no ALTER INDEX ... INIT clause; ALTER INDEX itself is a distinct statement, limited to toggling TO [NOT] CLUSTER):
```
ALTER FRAGMENT ON INDEX idx_orders INIT FRAGMENT BY EXPRESSION
  (region = 'east') IN east_dbspace, (region = 'west') IN west_dbspace;
```
Then alter the now-independent index's fragmentation as needed, via ALTER FRAGMENT ON INDEX, once it's detached from the table's scheme.
Be aware of restrictions on fragmented indexes generally, per the official guidance's example — a unique index can't use round-robin fragmentation, so detaching doesn't remove every constraint, just the same-as-table tie.

Examples

The disallowed attempt

-- idx_orders was created without an explicit storage-spec, so it's
-- fragmented the same as its table
ALTER FRAGMENT ON INDEX idx_orders MODIFY east_dbspace TO new_east_dbspace;
-- -787: this index's fragmentation is tied to the table's; it can't be altered directly

Corrected — detach first, then alter

ALTER FRAGMENT ON INDEX idx_orders INIT FRAGMENT BY EXPRESSION
  (region = 'east') IN east_dbspace, (region = 'west') IN west_dbspace;
ALTER FRAGMENT ON INDEX idx_orders MODIFY east_dbspace TO new_east_dbspace;

Diagnostic Checks

Check whether the index was created with an explicit storage-spec (independent fragmentation) or without one (same-as-table) — the latter requires ALTER FRAGMENT ON INDEX ... INIT before any other fragmentation change.

Related Errors / Related Topics

-774 — "Cannot specify fragment expressions with a round-robin fragmentation." A related fragmented-index restriction the official guidance's own example references (a unique index can't be round-robin fragmented).

Detach the index from the table's fragmentation scheme first (ALTER FRAGMENT ON INDEX ... INIT), then alter it independently.