Informix Error -501

Reasons / Common Causes

-501 is a no-op warning, not a true failure: it fires when ALTER INDEX ... TO NOT CLUSTER is issued against an index that is already not the table's clustered index (or never was).

1. ALTER INDEX index-name TO NOT CLUSTER run on an index that isn't currently clustered — the direct, only cause.
2. A script or migration that unconditionally un-clusters an index without first checking whether it's the one actually clustered, as part of a broader schema-tuning step.
3. Confusion about which index a table is clustered on, the same underlying gap in visibility that also produces -500 — clustering state isn't obvious from a table's definition alone.

Solutions / Resolution

1. Nothing needs fixing — per the official guidance, if this is the index you meant to un-cluster, no further action is required; the table already isn't clustered on it.
2. Confirm which index the table actually is clustered on, per the official guidance's pointer to -500's discussion, by querying systables/sysindexes:

   SELECT t.tabname, i.idxname FROM systables t, sysindexes i
   WHERE t.tabid = i.tabid AND i.clustered = 'C';
   

3. If a different index needs un-clustering, target that index by name instead:

   ALTER INDEX correct_index_name TO NOT CLUSTER;
   

Examples

Confirming there's nothing to do

ALTER INDEX idx_orders_old TO NOT CLUSTER;
-- -501: idx_orders_old is already not clustered

-- Check what actually is clustered:
SELECT t.tabname, i.idxname FROM systables t, sysindexes i
WHERE t.tabid = i.tabid AND t.tabname = 'orders' AND i.clustered = 'C';

Retargeting the un-cluster at the right index

-- The query above shows idx_orders_new is the clustered one:
ALTER INDEX idx_orders_new TO NOT CLUSTER;

Diagnostic Checks

1. Query systables/sysindexes for the table's currently clustered index to confirm whether -501 reflects a genuine mistake in which index was named, or simply a redundant command against a table that was never clustered.

Related Errors / Related Topics

• -500 — "Clustered index index-name already exists in the table." The companion error on the other side of the same clustering-state confusion: attempting to cluster a second index instead of un-clustering the wrong one.

This is a warning-level no-op, not a failure requiring a fix — if the named index genuinely wasn't clustered, the command simply had nothing to do.