Invalid fragment strategy?
I have been trying to create a database from an sql file that I have put together. When I run it in dbaccess against the sysmaster database, I get the following error.
872: Invalid fragment strategy or expression for the unique index.
When I choose Modify from the menu, it points me to this line:
CREATE UNIQUE INDEX id_ao_row
ON ao_res_row (aores_idnum, tstrun_idnum)
FRAGMENT BY EXPRESSION
MOD(aoextract_idnum, 3) = 0 IN padbdbs1,
MOD(aoextract_idnum, 3) = 1 IN padbdbs2,
MOD(aoextract_idnum, 3) = 2 IN padbdbs3; ^
The table creation statement just prior to this is:
CREATE TABLE ao_res_row
(
aoextract_idnum SERIAL NOT NULL,
aores_idnum INTEGER NOT NULL,
tstrun_idnum INTEGER NOT NULL
)
FRAGMENT BY EXPRESSION
MOD(aoextract_idnum, 3) = 0 IN padbdbs1,
MOD(aoextract_idnum, 3) = 1 IN padbdbs2,
MOD(aoextract_idnum, 3) = 2 IN padbdbs3;
So my question is, what does this error mean, and what do I do to fix it? Any help would be appreciated.
*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*
Don Conley *
IT Solutions Specialist *
Hewlett-Packard Co. *
Colorado Springs, Colorado USA *
(719) 590-2167 *
*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*
Nevermind, using finderr 872 pointed me to the answer:
-872 Invalid fragment strategy or expression for the unique
index.
Unique indexes cannot be fragmented by the round-robin
method. If the indexes are fragmented by the expression
method, then all the columns that are used in the
fragmentation expressions must also be part of the index
key.
So in order to fix this, I needed to change the fragmentation key from aoextract_idnum to aores_idnum. Once this change was made, it created the index with no problems.
*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*
Don Conley *
IT Solutions Specialist *
Hewlett-Packard Co. *
Colorado Springs, Colorado USA *
(719) 590-2167 *
*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*
Donald Conley (conley@col.hp.com) wrote:
: I have been trying to create a database from an sql file that I have put together. When I run it in dbaccess against the sysmaster database, I get the following error.
: 872: Invalid fragment strategy or expression for the unique index.
: When I choose Modify from the menu, it points me to this line:
: CREATE UNIQUE INDEX id_ao_row
: ON ao_res_row (aores_idnum, tstrun_idnum)
: FRAGMENT BY EXPRESSION
: MOD(aoextract_idnum, 3) = 0 IN padbdbs1,
: MOD(aoextract_idnum, 3) = 1 IN padbdbs2,
: MOD(aoextract_idnum, 3) = 2 IN padbdbs3;
: ^
: The table creation statement just prior to this is:
: CREATE TABLE ao_res_row
: (
: aoextract_idnum SERIAL NOT NULL,
: aores_idnum INTEGER NOT NULL,
: tstrun_idnum INTEGER NOT NULL
: )
: FRAGMENT BY EXPRESSION
: MOD(aoextract_idnum, 3) = 0 IN padbdbs1,
: MOD(aoextract_idnum, 3) = 1 IN padbdbs2,
: MOD(aoextract_idnum, 3) = 2 IN padbdbs3;
: So my question is, what does this error mean, and what do I do to fix it? Any help would be appreciated.
: *!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*
: Don Conley *
: IT Solutions Specialist *
: Hewlett-Packard Co. *
: Colorado Springs, Colorado USA *
: (719) 590-2167 *
: *!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*