Informix Error -25806: The fragment key for interval fragmentation must be a numeric, DATE, or DATETIME type.
Cause and resolution
The fragment key for interval fragmentation must be a numeric, DATE, or DATETIME type.
The fragment key, which is the column expression on which the table or index is fragmented, must be a numeric, DATE, or DATETIME type. For example, the following statement generates an error:
CREATE TABLE address (street VARCHAR(128), city VARCHAR(32), state CHAR(2), zip INT) FRAGMENT BY RANGE(city) INTERVAL(5) STORE IN (dbs1, dbs2, dbs3) PARTITION p1 VALUES < 10000 IN dbs3;
The fragment key is the column city, which is of type CHAR.
The following statements are correct:
CREATE TABLE orders (order_num INT, order_date DATE, customer_num INT, ship_instruct_code CHAR(1)) FRAGMENT BY RANGE(order_date) INTERVAL(INTERVAL(3) MONTH TO MONTH) STORE IN (dbs1, dbs2, dbs3) PARTITION p1 VALUES < DATE('01/01/2009') IN dbs1;
CREATE INDEX ord_ship_idx ON orders(ship_instruct_code) FRAGMENT BY RANGE(ASCII(ship_instruct_code)) INTERVAL(10) STORE IN (dbs1, dbs2) PARTITION p1 VALUES < 65 IN dbs1;
Oninit® Troubleshooting Guidance
Reasons / Common Causes
-25806 fires when FRAGMENT BY RANGE(...)'s column expression isn't numeric, DATE, or
DATETIME — per the official guidance.
- A
FRAGMENT BY RANGEkey expression of a non-numeric, non-DATE, non-DATETIMEtype (e.g.CHAR), per the official guidance — the direct, only cause.
Solutions / Resolution
- Use a numeric,
DATE, orDATETIMEcolumn expression as the fragment key, per the official guidance — e.g.ASCII(char_col)to convert a character column.
Examples
A CHAR fragment key
CREATE TABLE address (street VARCHAR(128), city VARCHAR(32), state CHAR(2), zip INT)
FRAGMENT BY RANGE(city) INTERVAL(5) STORE IN (dbs1, dbs2, dbs3)
PARTITION p1 VALUES < 10000 IN dbs3;
-- -25806: city is CHAR, not numeric/DATE/DATETIME
Corrected — using ASCII() on a character column
CREATE INDEX ord_ship_idx ON orders(ship_instruct_code)
FRAGMENT BY RANGE(ASCII(ship_instruct_code)) INTERVAL(10) STORE IN (dbs1, dbs2)
PARTITION p1 VALUES < 65 IN dbs1;
Diagnostic Checks
- Check the
FRAGMENT BY RANGEkey expression's resultant type, and convert it to numeric/DATE/DATETIMEif it isn't already.
Related Errors / Related Topics
- -25804 — "The interval value must be a non-zero positive constant." A related interval-fragmentation restriction.
- -25805 — "The interval value must be a numeric or INTERVAL data type." A related interval-fragmentation type-matching restriction.
- -25807 — "The fragment key for interval fragmentation must be a single column expression." A related fragment-key restriction, on multi-column expressions rather than type.
An interval fragmentation key wasn't numeric/DATE/DATETIME — convert it, e.g. with
ASCII().