Index and Table Rename

Hi, I'm still busy with different methods of loading data into tables. Thanks for all the help I recevied from readers of this newsgroup so far! Now I've got a problem with indexes. I want to replace table "a" with a new version loaded from external sources, and "a" has a number of indexes. I thought along the lines of create a_new load a_new with data ?? would like to create index here lock a in exclusive mode rename a a_old rename a_new a drop a_old If I put a set of create index statements where the ?? are, my procedure fails when run for the second time because those index names already exist... but creating the indexes AFTER dropping a_old would bring an un-indexed "a" on-line and thus make some clients wait forever! I'm sure there is a solution for things like this...? Bye Frederik -- Frederik Ramm (frederik@remote.org)

We use two sets of names for the indexes. If index_a exists in sysindexes, create index_b, other wise create index_a. The only drawback is if some queries use optimizer directives that use the index name. I think I may have read somewhere that a rename index command was available in 7.31 that would allow you to switch the name back to index_a after dropping the old table. Jay Buckler Frederik Ramm <frederik@remote.org> wrote in message news:7rm0mk$p2$1@guanaco.rhein-main.de... > Hi, > > I'm still busy with different methods of loading data into tables. > Thanks for all the help I recevied from readers of this newsgroup so > far! Now I've got a problem with indexes. I want to replace table "a" > with a new version loaded from external sources, and "a" has a number > of indexes. > > I thought along the lines of > > create a_new > load a_new with data > ?? would like to create index here > lock a in exclusive mode > rename a a_old > rename a_new a > drop a_old > > If I put a set of create index statements where the ?? are, my procedure > fails when run for the second time because those index names already > exist... but creating the indexes AFTER dropping a_old would bring > an un-indexed "a" on-line and thus make some clients wait forever! > > I'm sure there is a solution for things like this...? > > Bye > Frederik > > -- > Frederik Ramm (frederik@remote.org)