Clustered index

When changing an index to cluster the database must make a complete copy of the table in order to re-order the rows. Where must the space be available to do this? On the dbspace, or in the chunk in which the table resides? I am trying to cluster an index on a table in order to force the number of table extents down. However, I get an error 502 when I try. I've got 12 2Gig chunks in the dbspace, but only three of the chunks have any space used. So, I've got plenty of space in the dbspace, but keep getting this error on this specific table. Other tables have worked fine. I'm using 7.24 UC4 on Solaris 2.6. Russ Evans Division of Information Resource Management The Alexander Building 2020 Capital Circle SE, Room 2407 Tallahassee, FL 32399-1733 (850)413-8490 S/C 293-8490

Russ_Evans@doh.state.fl.us wrote: > When changing an index to cluster the database must make a complete copy of > the table in order to re-order the rows. Where must the space be available > to do this? On the dbspace, or in the chunk in which the table resides? I am > trying to cluster an index on a table in order to force the number of table > extents down. However, I get an error 502 when I try. I've got 12 2Gig > chunks in the dbspace, but only three of the chunks have any space used. So, > I've got plenty of space in the dbspace, but keep getting this error on this > specific table. Other tables have worked fine. I'm using 7.24 UC4 on Solaris > 2.6. > > Russ Evans > Division of Information Resource Management > The Alexander Building > 2020 Capital Circle SE, Room 2407 > Tallahassee, FL 32399-1733 > (850)413-8490 > S/C 293-8490 Well the ISAM error might help out, but assuming it is out of disk space, since it has to create the index first, it might be running out of space trying to create the index. If this is the case, it could be your DBSPACETEMPS filling, that doesn't mean all of them have to fill, but if one fills completely it can't split that sort file into another dbspace so it can abort the index build. Again, assuming it is an out of space message, I'd run a recursive onstat -d and check for any chunks/dbspaces that get to 0 or below 4 (or maybe 8) free pages. Also not knowing if the index is fragmented or detached, it will have to create a complete copy of the index assuming it is fragmented or detached in another dbspace so the index dbspace/s might not have enough room as well. Hope that helps you out. Jacques -- ******************************************************************** * Jacques P. Renaut "I'd dazzle you with brilliance * * Informix Advanced Support if I only had the knack..." * * email: jrenaut@informix.com #include <disclamier.h> * ********************************************************************

Check that you have enough temp dbspace to sort the indexes. Neil Truby aracnet Limited Weybridge, UK Russ_Evans@doh.state.fl.us wrote in message <770p1n$qrm$1@news.xmission.com>... > >When changing an index to cluster the database must make a complete copy of >the table in order to re-order the rows. Where must the space be available >to do this? On the dbspace, or in the chunk in which the table resides? I am >trying to cluster an index on a table in order to force the number of table >extents down. However, I get an error 502 when I try. I've got 12 2Gig >chunks in the dbspace, but only three of the chunks have any space used. So, >I've got plenty of space in the dbspace, but keep getting this error on this >specific table. Other tables have worked fine. I'm using 7.24 UC4 on Solaris >2.6. > > >Russ Evans >Division of Information Resource Management >The Alexander Building >2020 Capital Circle SE, Room 2407 >Tallahassee, FL 32399-1733 >(850)413-8490 >S/C 293-8490 > >

Neil Truby wrote: > > Check that you have enough temp dbspace to sort the indexes. If not you might try using PSORT_DBTEMP to assign filesystem space to the temporary sort-work files created by the table sort. Also keep in mind the ALTER FRAGMENT ON TABLE tablename INIT IN dbspace; option - it tends to run MUCH faster than ALTER INDEX indexname TO CLUSTER; does as good a job and requires less disk space during the compression. > Neil Truby > aracnet Limited > Weybridge, UK > > Russ_Evans@doh.state.fl.us wrote in message > <770p1n$qrm$1@news.xmission.com>... > > > >When changing an index to cluster the database must make a complete copy of > >the table in order to re-order the rows. Where must the space be available > >to do this? On the dbspace, or in the chunk in which the table resides? I > am > >trying to cluster an index on a table in order to force the number of table > >extents down. However, I get an error 502 when I try. I've got 12 2Gig > >chunks in the dbspace, but only three of the chunks have any space used. > So, > >I've got plenty of space in the dbspace, but keep getting this error on > this > >specific table. Other tables have worked fine. I'm using 7.24 UC4 on > Solaris > >2.6. > Art S. Kagel