Informix Error -797

Reasons / Common Causes

-797 fires when an operation requiring an empty fragment (such as DROP of a specific fragment, or certain DETACH/reorganization operations) is attempted against a fragment that still contains data rows or index entries — per the official guidance, the fragment must be empty first.

1. A fragment-drop or similar operation attempted against a fragment still holding data, per the official guidance — the direct, only cause.
2. An assumption that the operation would implicitly clear the fragment, when it actually requires the fragment to already be empty beforehand.

Solutions / Resolution

1. Remove the data from the fragment first, per the official guidance's implicit requirement — delete the rows (or, for an index fragment, ensure no index entries remain) before retrying the operation.
2. Confirm the fragment is genuinely empty before retrying:

   SELECT COUNT(*) FROM orders WHERE <the fragment's own range/expression condition>;
   

3. If the data needs to be preserved rather than deleted, move it to a different fragment or table first (via an UPDATE that changes its fragmentation-key value, or an unload/reload), then retry the operation against the now-empty fragment.

Examples

Emptying a fragment before dropping it

DELETE FROM orders WHERE region = 'discontinued_region';
-- confirm the fragment covering 'discontinued_region' is now empty,
-- then proceed with the fragment-removal operation

Diagnostic Checks

1. Query the fragment's own data condition to confirm whether it genuinely still holds rows or index entries before retrying the operation.

Related Errors / Related Topics

• -784 — "Cannot detach because of the existing referential constraints." A related fragment-removal restriction, about referential constraints blocking a detach rather than the fragment's own data content.

The fragment must be genuinely empty first — delete or relocate its data before retrying the operation.