Re: Moving a table

Hi Mike, Below can be found in "Informix Guide to SQL: Syntax, February 1998" under "ALTER FRAGMENT": "The new table that results from the execution of the DETACH clause does not inherit any indexes or constraints from the original table. Only the data remains." So after execute alter fragment on table <TABLE> INIT fragment by expression reins_yr_id = "2005" in <DBSPACE_1>,reins_yr_id != "2005" in <DBSPACE_2>; may just detach <DBSPACE_1> and the original table will stay in <DBSPACE_2> and be with indexes. Not sure if it is what you want. Regards, Gary ------------------------ MIKE MAGIE wrote: Dudes and Dudettes, 10.00.UC8 Solaris 2.8 6-1, 180 11/17/1967 Scorpio I have been messing with alter frag, attach detach init, etc. all day. Someone clue me in on the secret ALTER TABLE statement that lets me move a non-fragmented table with indexes created IN TABLE to a new dbspace. I am at this point settling on the following sequence of events which leaves my newly moved table index-less: alter fragment on table <TABLE> INIT fragment by expression reins_yr_id = "2005" in <DBSPACE_1>,reins_yr_id != "2005" in <DBSPACE_2>; Simple and keeps rows out of my new fragment - which I dig. Then I : alter fragment on table <TABLE> detach <DBSPACE_2> <NEW_TABLE>; Now I have my new, empty, non-fragged table in the dbspace I want. I next drop the original table and rename the new one to the old ones name. But I have no indexes - as is freaking documented. Help - Mike