Informix Error -9854: Required purpose purpose_name not used for access method.

Cause and resolution

Required purpose purpose_name not used for access method.

A required purpose has not been used with the CREATE ACCESS_METHOD statement, or a required or default purpose has been removed with the ALTER ACCESS_METHOD statement. For example, AM_SCANCOST has a default value if not used with the CREATE ACCESS_METHOD statement, but it cannot be dropped with the ALTER ACCESS_METHOD statement. Change the purposes used until the required purposes are included or preserved.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-9854 fires when a required purpose is missing from a CREATE ACCESS_METHOD statement, or a required/default purpose is removed by ALTER ACCESS_METHOD — per the official guidance.

A required purpose missing from CREATE ACCESS_METHOD, per the official guidance — the first named cause.
A required or default purpose removed by ALTER ACCESS_METHOD, per the official guidance — the second named cause; for example, AM_SCANCOST has a default value if unspecified at creation, but can't be dropped afterward.

Solutions / Resolution

Change the purposes used until the required ones are included or preserved, per the official guidance.

Examples

Attempting to drop a required/default purpose

ALTER ACCESS_METHOD my_am DROP AM_SCANCOST;   -- -9854

Diagnostic Checks

Check which purposes are required for the access method type, and add or restore any that are missing.

Related Errors / Related Topics

-9852 — "Improper purpose purpose_name used for access method." A related purpose- validity error, on impropriety/misspelling rather than a missing required purpose.
-9853 — "Duplicate purpose purpose_name used for access method." A related purpose- validity error, on duplication rather than a missing required purpose.

A required or default access-method purpose was missing or dropped — add or restore it.