Informix Error -796

Reasons / Common Causes

-796 fires when an interval-fragmented table/index's computed fragment position for a row exceeds 2147483646 — per the official guidance, this is a hard ceiling on how many interval fragments the position-numbering scheme can address.

1. A very long-running interval fragmentation scheme that has generated an enormous number of fragments over time, eventually exhausting the position-numbering range — per the official guidance, the specific limit named.
2. An interval expression that produces fragments too finely for the range of data the table actually spans, generating far more fragments (and higher position numbers) than necessary.

Solutions / Resolution

1. Change the interval value expression or transition value, per the official guidance, to produce coarser fragments (covering a wider range each) and accommodate the row within the position limit.
2. Review the fragmentation scheme's interval expression for whether it's finer than actually needed for the data's real range and growth rate:

   SELECT partn, evaltext FROM sysfragments WHERE tabid =
     (SELECT tabid FROM systables WHERE tabname = 'orders') AND fragtype = 'T';
   -- review the interval fragmentation strategy's interval expression
   

3. Consider re-establishing the fragmentation scheme from scratch (a bigger change) if the position limit was reached because of years of accumulated fine-grained fragments, rather than trying to extend the existing scheme further.

Examples

Widening the interval to reduce fragment count

-- Instead of a scheme generating a new fragment per week:
-- FRAGMENT BY RANGE (order_date) INTERVAL (NUMTOYMINTERVAL(0.25, 'MONTH')) STORE IN (...)
-- re-create the fragmentation scheme with a coarser interval, e.g. yearly:
ALTER FRAGMENT ON TABLE orders INIT FRAGMENT BY RANGE (order_date)
  INTERVAL (NUMTOYMINTERVAL(1, 'YEAR')) STORE IN (dbspace1, dbspace2);

Diagnostic Checks

1. Review the interval fragmentation scheme's interval expression and how many fragments it has generated over the table's history, to understand whether the position limit was reached through genuine data volume or an overly fine-grained interval choice.

Related Errors / Related Topics

• -791 — "Cannot find fragment for the row." A related interval-fragmentation error, about the row not matching any fragment at all, rather than the fragment position numbering itself reaching its ceiling.

A hard ceiling (2147483646) on interval fragment position numbers — widen the interval expression to generate fewer, larger fragments.