more on extent problem

hey all, thanks for all the suggestions, they are great!. :) doing some more digging around, did a oncheck -pt and it's showing the following on the table Number of pages allocated 16777215 Number of pages used 16777215 Number of data pages 16129687 so I went to check the Administrator Guide for Informix Dynamic Server, and it says that the largest number that can be stored in the three most-signifcant bytes of a rowid is 16,777,215, this figure is the upper limit of the number of pages that cvan be contained in a single tblspace. so according to that, i am out of pages in the tblspace. My question is, what is the common solution to that? I only have about 30 gigs of data and 100 million records, what do people do when their pages in the tblspace is maxed? thanks a lot. yan

Zhu, Fragmenting the table(s) would be a good work around for this. Ashish. Yan Zhu wrote in message <86lg60$gdq$1@news.xmission.com>... > > >hey all, > thanks for all the suggestions, they are great!. :) > doing some more digging around, did a oncheck -pt and it's showing >the following on the table > > Number of pages allocated 16777215 > Number of pages used 16777215 > Number of data pages 16129687 > >so I went to check the Administrator Guide for Informix Dynamic Server, >and it says that the largest number >that can be stored in the three most-signifcant bytes of a rowid is >16,777,215, this figure is the upper limit of >the number of pages that cvan be contained in a single tblspace. > > so according to that, i am out of pages in the tblspace. > My question is, what is the common solution to that? I only have >about 30 gigs of data and 100 million records, what do >people do when their pages in the tblspace is maxed? > thanks a lot. >yan > >

Keep in mind...the max number of extents for a tablespace - fragmented or not - is a function of the room left on the partition page for that tablespace. There are 5 slots on that page - the 56 partition structure, general table info, special columns (varchars and blobs), indexes, and finally the extent list. If a table is chock full of varchars/blobs & has a boatload of indexes, you can run short of extent(s) prior to maxing the number of pages for a tablespace with respect to the 3-byte (LLLLLL) logical page number from the rowid construct. Mark. Yan Zhu wrote: > hey all, > thanks for all the suggestions, they are great!. :) > doing some more digging around, did a oncheck -pt and it's showing > the following on the table > > Number of pages allocated 16777215 > Number of pages used 16777215 > Number of data pages 16129687 > > so I went to check the Administrator Guide for Informix Dynamic Server, > and it says that the largest number > that can be stored in the three most-signifcant bytes of a rowid is > 16,777,215, this figure is the upper limit of > the number of pages that cvan be contained in a single tblspace. > > so according to that, i am out of pages in the tblspace. > My question is, what is the common solution to that? I only have > about 30 gigs of data and 100 million records, what do > people do when their pages in the tblspace is maxed? > thanks a lot. > yan

Yan, The only thing that I know to do at this point is to export, drop, and rebuild the table, with a larger extent size. If you are looking at 100 million rows, the HPL is almost required (I'd imagine) to do the load and unload. You should be able to do a no-conversion unload relatively quickly (we estimate approximately 20,000 rows per second on our hardware... If we get less, we're looking for a problem), and the load is correspondingly fast (though slower than the unload. The big ticket is re-creating the indexes, which you'll want to do manually, rather than letting HPL do it, and rebuilding any of your constraints (if you have them). Make sure that you do that in parallel (PDQPRIORITY as high as you can have it). Still, you're right, it will take a large amount of time to do, and the table will be locked for the duration. HTH. -- Dan Michaelis Database Administrator dan@kax.com Sent via Deja.com http://www.deja.com/ Before you buy.