Fragmentation

I'm trying to reorg one of our databases. In doing so, I want to = utilize "fragment=20 by expression" on several tables. I've made the change to the schema: create table "cshbet".tjrnl ( acct_id integer not null , counter integer not null , session_id integer, cr_date datetime year to second default current year to second not null , j_op_type char(4) not null , j_op_ref_key char(4), j_op_ref_id int8, user_id integer, amount decimal(12,2) not null , desc varchar(80), balance decimal(12,2) ) fragment by expression DATE(cr_date) between '04/01/2003' AND '04/30/2003' in tjrnl01 , DATE(cr_date) between '05/01/2003' AND '05/31/2003' in tjrnl02 , DATE(cr_date) between '06/01/2003' AND '06/30/2003' in tjrnl03 , DATE(cr_date) between '07/01/2003' AND '07/31/2003' in tjrnl04 , DATE(cr_date) between '08/01/2003' AND '08/31/2003' in tjrnl05 However, when I run start the dbimport, it fails on index creation with = the following error: 872 - Invalid fragment strategy or expression for the unique index. My primary key/unique index is on acct_id. Does cr_date have to be part = of the primary key? =20

Terrence Mu.... wrote: > I'm trying to reorg one of our databases. In doing so, I want to = > utilize "fragment=20 > by expression" on several tables. I've made the change to the schema: > > create table "cshbet".tjrnl > ( > acct_id integer not null , > counter integer not null , > session_id integer, > cr_date datetime year to second > default current year to second not null , > j_op_type char(4) not null , > j_op_ref_key char(4), > j_op_ref_id int8, > user_id integer, > amount decimal(12,2) not null , > desc varchar(80), > balance decimal(12,2) > ) > fragment by expression > DATE(cr_date) between '04/01/2003' AND '04/30/2003' in tjrnl01 , > DATE(cr_date) between '05/01/2003' AND '05/31/2003' in tjrnl02 , > DATE(cr_date) between '06/01/2003' AND '06/30/2003' in tjrnl03 , > DATE(cr_date) between '07/01/2003' AND '07/31/2003' in tjrnl04 , > DATE(cr_date) between '08/01/2003' AND '08/31/2003' in tjrnl05 > > However, when I run start the dbimport, it fails on index creation with = > the following error: > > 872 - Invalid fragment strategy or expression for the unique index. > > My primary key/unique index is on acct_id. Does cr_date have to be part = > of the primary key? =20 No, but you are choosing to fragment your index based on cr_date. Surely a more sensible fragmentation strategy for that index would be based on acct_id, or simply locate the entire index in a single dbspace. If you don't specify a fragmentation strategy for each index, then they will follow that used for the table. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+