Informix Error -25835: The transition value specified is not valid.
Cause and resolution
The transition value specified is not valid.
While altering the fragmentation scheme to modify the transition value, the new transition value must be greater than old transition value. If there are interval fragments following the new transition value then the new transition value must align at an interval fragment boundary.
For example, consider a table with following schema: 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(emp_id) INTERVAL(1000) STORE IN (dbs1, dbs2, dbs3) PARTITION p1 VALUES < 4000 IN dbs1, PARTITION p2 VALUES < 6000 IN dbs2, PARTITION p3 VALUES < 8000 IN dbs3;
The following statement generates an error:
ALTER FRAGMENT ON TABLE employee MODIFY INTERVAL TRANSITION TO 7000;
The transition value can only be increased. In the above example, the new transition value is lesser than the old one.
Lets assume the table has two interval fragments, sys_p4 >= 8000 AND < 9000 sys_p6 >= 10000 AND < 11000
The following statement generates an error:
ALTER FRAGMENT ON TABLE employee MODIFY INTERVAL TRANSITION TO 8500;
The new transition value 8500 is not aligned at an interval fragment boundary.
The following usage is correct:
ALTER FRAGMENT ON TABLE address MODIFY INTERVAL TRANSITION TO 9000; ALTER FRAGMENT ON TABLE address MODIFY INTERVAL TRANSITION TO 10000;
The new transition values 9000 and 10000 are aligned at an interval fragment boundary.
The following usage is also correct:
ALTER FRAGMENT ON TABLE address MODIFY INTERVAL TRANSITION TO 12500;
Since there are no interval fragments beyond the new transition value, the new transition value need not align at an interval fragment boundary.
Oninit® Troubleshooting Guidance
Reasons / Common Causes
-25835 fires when ALTER FRAGMENT ... MODIFY INTERVAL TRANSITION TO <value> gives a new
transition value that's inconsistent with the existing scheme — per the official guidance.
- A new transition value less than or equal to the old one, per the official guidance — the transition value can only be increased.
- A new transition value that doesn't align to an existing interval fragment's boundary, per the official guidance, when interval fragments already exist beyond the new transition value.
Solutions / Resolution
- Specify a new transition value strictly greater than the current one, per the official guidance.
- Align the new transition value to an existing interval fragment boundary, per the official guidance, if interval fragments already exist past it.
Examples
Decreasing the transition value
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(emp_id) INTERVAL(1000) STORE IN (dbs1, dbs2, dbs3)
PARTITION p1 VALUES < 4000 IN dbs1,
PARTITION p2 VALUES < 6000 IN dbs2,
PARTITION p3 VALUES < 8000 IN dbs3;
ALTER FRAGMENT ON TABLE employee MODIFY INTERVAL TRANSITION TO 7000;
-- -25835: 7000 is less than the current transition value of 8000
Misaligned with an existing interval fragment
-- given existing interval fragments sys_p4 (>=8000,<9000) and sys_p6 (>=10000,<11000)
ALTER FRAGMENT ON TABLE employee MODIFY INTERVAL TRANSITION TO 8500;
-- -25835: 8500 falls inside sys_p4's range, not on a fragment boundary
Diagnostic Checks
- Check that the new transition value is greater than the current one, and, if interval fragments already exist beyond it, that it aligns to one of their boundaries.
Related Errors / Related Topics
- -25831 — "Cannot modify fragment expression for transition fragment." A related transition-fragment error, on modifying the transition fragment's expression directly rather than the transition value's own validity.
- -25836 — "The transition value must be a non-null constant." A related transition-value error, on type rather than magnitude/alignment.
A new transition value was decreased, or misaligned with existing interval fragments — increase it and align to a fragment boundary.