Informix Error -25811: The transition value for interval fragments must be aligned to the boundary of the interval value type.
Cause and resolution
The transition value for interval fragments must be aligned to the boundary of the interval value type.
The transition value, or the upper limit of the last range fragment, must be aligned to the boundary of the interval value type.
The following requirements apply: - For an interval value of any numeric data type, the transition value cannot have a fractional part. - For an interval value of INTERVAL type whose first field qualifier is YEAR or MONTH, the transition value cannot not contain field qualifiers smaller than MONTH. Unless the DAY field has a value of 01 and the values of any time units smaller than DAY are zero (for example, 01 00:00:00.00000). - For an interval value of INTERVAL type whose first field qualifier is DAY or a smaller time unit, the transition value cannot have FRACTION as the last field qualifier, unless the FRACTION field has a value of zero.
For example, the following statements generate an error:
Example 1:
CREATE TABLE employee (emp_id INTEGER, emp_name CHAR(64), ssn CHAR(12), basepay FLOAT, varpay FLOAT, dept_id SMALLINT, hire_date DATE) FRAGMENT BY RANGE(basepay) INTERVAL(1000) STORE IN (dbs1, dbs2, dbs3) PARTITION p1 VALUES < 40000.50 IN dbs1, PARTITION p2 VALUES < 60000.50 IN dbs2, PARTITION p3 VALUES < 80000.50 IN dbs3;
In the above example the upper limit of first and intermediate range fragments can be of any numeric type, but the upper limit of the last range fragments (which is the transition value for interval fragments) must be an INTEGER. The transition value in the above example, 80000.50, has a fractional part.
The correct usage is as follows:
CREATE TABLE employee (emp_id INTEGER, emp_name CHAR(64), ssn CHAR(12), basepay FLOAT, varpay FLOAT, dept_id SMALLINT, hire_date DATE) FRAGMENT BY RANGE(basepay) INTERVAL(1000) STORE IN (dbs1, dbs2, dbs3) PARTITION p1 VALUES < 40000.50 IN dbs1, PARTITION p2 VALUES < 60000.50 IN dbs2, PARTITION p3 VALUES < 80001 IN dbs3;
Example 2:
CREATE TABLE employee (emp_id INTEGER, emp_name CHAR(64), ssn CHAR(12), basepay FLOAT, varpay FLOAT, dept_id SMALLINT, hire_date DATE) FRAGMENT BY RANGE(hire_date) INTERVAL(3 UNITS YEAR) STORE IN (dbs1, dbs2, dbs3) PARTITION p1 VALUES < DATE('03/25/2006') IN dbs3;
In the above example the interval value is specified in units of YEAR, but the transition value is not aligned to the year or month boundary.
The correct usage is as follows:
CREATE TABLE employee (emp_id INTEGER, emp_name CHAR(64), ssn CHAR(12), basepay FLOAT, varpay FLOAT, dept_id SMALLINT, hire_date DATE) FRAGMENT BY RANGE(hire_date) INTERVAL(3 UNITS YEAR) STORE IN (dbs1, dbs2, dbs3) PARTITION p1 VALUES < DATE('03/01/2006') IN dbs3;
Example 3:
CREATE TABLE orders (order_num INT, order_time DATETIME YEAR TO SECOND, customer_num INT, ship_instruct_code CHAR(1)) FRAGMENT BY RANGE(order_time) INTERVAL(INTERVAL(10 12) DAY(2) TO HOUR) PARTITION p1 VALUES < DATETIME(2010-03-25 01:00:00.123) YEAR TO FRACTION(3) IN dbs1;
In the above example the interval value is a DAY(2) TO HOUR interval. The INTERVAL field qualifier transition value is not aligned to the second boundary (because it contains a nonzero FRACTION value).
The correct usage is as follows:
CREATE TABLE orders (order_num INT, order_time DATETIME YEAR TO SECOND, customer_num INT, ship_instruct_code CHAR(1)) FRAGMENT BY RANGE(order_time) INTERVAL(INTERVAL(10 12) DAY(2) TO HOUR) PARTITION p1 VALUES < DATETIME(2010-03-25 01:00:00.000) YEAR TO FRACTION(3) IN dbs1;
Oninit® Troubleshooting Guidance
Reasons / Common Causes
-25811 fires when the transition value (the upper limit of the last range fragment) isn't
aligned to the interval value type's boundary — per the official guidance: a numeric
interval requires a non-fractional transition value; a YEAR/MONTH-leading INTERVAL
requires no field smaller than MONTH (unless DAY is 01 and smaller units are zero); a
DAY-or-smaller-leading INTERVAL requires the FRACTION field, if present, to be zero.
- A transition value not aligned to the interval type's required boundary, per the official guidance — the specific alignment rule depends on the interval value's type, per the three cases above.
Solutions / Resolution
- Align the transition value to the interval type's boundary rule, per the official guidance.
Examples
N/A
No specific triggering statement is documented beyond a misaligned transition value.
Diagnostic Checks
- Check the transition value against the interval type's boundary-alignment rule (no
fractional part for numeric; no sub-
MONTHfields forYEAR/MONTHintervals; zeroFRACTIONforDAY-or-smaller intervals).
Related Errors / Related Topics
- -25804 — "The interval value must be a non-zero positive constant." A related interval-fragmentation restriction.
- -25812 — "The difference between the old and new transition values must be a
multiple of the interval value." A related transition-value error, on
ALTER FRAGMENTrather than the initial definition.
The transition value wasn't aligned to the interval value type's required boundary — align it per the type-specific rule.