Re: Fragments vs location of tables

reyes01@ibm.net wrote in article <34cfa409.0@news2.ibm.net>... > "Art S. Kagel" <kagel@bloomberg.com> writes: > >Francisco Reyes > > > ->> Today while looking at table info I noticed the tables which are NOT > ->> supposed to be on db1 report to be using the dbspace. One of them > ->> even had db1 listed 3 times. Any clues why these tables are using > ->> db1? > > > >What do you mean by the table info? > > I meant from dbaccess "Table, Info, Fragments". My > understanding is this reports the dbspaces where the > fragments are. > > >Look in sysfragments instead. > >Art S. Kagel > > I just did. It coincides with the info from dbaccess. > By looking at sysfragments it seems the indexes > are the ones that have remained in db1. > > I thought when doing round robin the index followed > the tables (i.e. fragment2 in db2, index for fragment 2 > would also go in db2). Isn't this the case? It seems all > the fragment index are going to db1 instead of the > dbspaces where my fragments are. When doing round-robin, the indexes do NOT get fragmented along with the table. This would make index lookups very inefficient. Round robin fragmentation cannot be used for an index. > > The syntax I am using to create the tables is: > create table .... fragment by round robin in db1, db2, .... > Anything wrong with that? > > Just tried deleting the fragment from db1 in a table > (using dbaccess). The table is no longer in db1, > but the index remain. I believe these are the > primary key index. How can I re-locate those to > the other dbspaces? > To control attributes of the primary key index, create the table withoout the primary key. Next, create the unique index which corresponds to the primary key, setting the dbspace, fragmentation startegy, etc. as desired. Finally, add the primary key constraint using "alter table". HTH --- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com