Informix Error -738: DROP DISTRIBUTIONS is only valid in LOW mode.

Reasons / Common Causes

-738 fires when DROP DISTRIBUTIONS is used with UPDATE STATISTICS MEDIUM/HIGH instead of LOW — per the official guidance, LOW mode is where DROP DISTRIBUTIONS belongs: LOW mode by default doesn't remove existing distributions on its own, so the DROP DISTRIBUTIONS clause is how you explicitly remove them (regardless of whether those existing distributions were originally built by an earlier MEDIUM or HIGH run) — the clause itself is only valid attached to LOW mode.

1. DROP DISTRIBUTIONS attached to UPDATE STATISTICS MEDIUM or HIGH, per the official guidance — the direct, only cause; the clause needs LOW mode specifically.
2. A misunderstanding of what removes distributions, expecting MEDIUM/HIGH mode combined with DROP DISTRIBUTIONS to both rebuild and clear distributions in one step, when the removal step itself always runs under LOW mode.

Solutions / Resolution

1. Use UPDATE STATISTICS LOW DROP DISTRIBUTIONS, per the official guidance, to remove existing distributions — regardless of whether they were originally built in MEDIUM or HIGH mode.
2. Follow with a separate MEDIUM/HIGH run, if new distributions are needed afterward, since DROP DISTRIBUTIONS under LOW mode only removes them; it doesn't rebuild new ones in the same statement.

Examples

The disallowed attempt

UPDATE STATISTICS HIGH DROP DISTRIBUTIONS FOR TABLE orders;
-- -738: DROP DISTRIBUTIONS requires LOW mode

Corrected — drop first under LOW mode, then rebuild if needed

UPDATE STATISTICS LOW DROP DISTRIBUTIONS FOR TABLE orders;
UPDATE STATISTICS HIGH FOR TABLE orders;

Diagnostic Checks

1. Check whether DROP DISTRIBUTIONS is attached to a MEDIUM/HIGH mode statement, and move it to a separate LOW-mode statement instead.

Related Errors / Related Topics

• -736 — "The RESOLUTION and SAMPLING SIZE clauses are not valid for the LOW mode of the UPDATE STATISTICS statement." A related mode/clause-compatibility restriction, going the other direction — clauses that need MEDIUM/HIGH rather than LOW.
• -737 — "The confidence value and the SAMPLING SIZE clause are not valid for the HIGH mode of the UPDATE STATISTICS statement." A related mode/clause-compatibility restriction, for HIGH mode specifically.

DROP DISTRIBUTIONS always runs under LOW mode, regardless of which mode originally built the distributions being removed — follow it with a separate MEDIUM/HIGH run if new distributions are needed.