extent problem

hey all: Trying to load a table, having the following problem: 271: Could not insert new row into the table. 136: ISAM error: nomore extents 847: Error in load file line 59770. hmm, what does this mean? and how can I fix it? I don't even remeber setting up extents when I create the tables. I checked the dbspace, plenty left, how could it run out? running 7.23 on solaris 7.0. thanks a lot guys. yan

Here's the scoop on extents and running out of same. When a table is created it allocates a single page in the TABLESPACE TABLESPACE of the dbspace in which it will reside. This page must contain identifying data, descriptions of special columns, and a list of all of the tables extent starting addresses and lengths. For fragmented tables and detached indexes the same is true but per table or index fragment. Since the entire tablspace tablespace entry must fit on one page the number of extents that a table, an index, or a fragment can be composed of is limited. Informix tries to reduce the severity of this by extent compression (compressing contiguous extents into a single larger extent) and extent doubling (every time 32 extents are allocated the NEXT SIZE of the table is automatically doubled so there will be fewer new extents in the future) but with extent interleaving and a small starting extent size and next size (such as the default of 16K) this is not always possible. So, what to do? When creating a new table that is to be loaded with an initial set of data, try to estimate the number of pages needed for the initial load and for a reasonable block of future growth and create the table with extent and next sizes to allow for the fewest extents possible over time. You can estimate next size as N months growth or based on the number of cronologically contiguous rows a typical query is likely to access. The first method minimizes the number of extents and limits fragmentation, the second, useful when such extents would be too large, minimizes the effect of any fragmentation by ensuring that the most common queries only access one or two extents. OK, but you have already loaded much of the data, what to do now? You can reorg the table, after setting the new NEXT SIZE or scratch the table, recreate with proper EXTENT SIZE and NEXT SIZE and reload. There are several ways to reorg, in order by speed: 1) ALTER FRAGMENT ON TABLE mytable INIT IN some_dbspace; You can do this even for non-fragmented tables and even into the same dbspace in which the table already resides. If you set the NEXT SIZE correctly you should not have more than two or three extents when you are finished. 2) ALTER INDEX some_index TO CLUSTER; This will sort the table and effectively create a new contiguous table with the rows in index order. Again you should end up with only one to three extents. 3) Unload the data, drop and recreate the table, reload. In your case it might be better to just trash the data and begin the load from the original source files again. Art S. Kagel Yan Zhu wrote: > hey all: > > Trying to load a table, having the following problem: > > 271: Could not insert new row into the table. 136: ISAM error: no> more extents > 847: Error in load file line 59770.> > hmm, what does this mean? and how can I fix it? I don't even remeber > setting up extents when I create the tables. > I checked the dbspace, plenty left, how could it run out? > > running 7.23 on solaris 7.0. > > thanks a lot guys. > > yan

Yan, In correcting this, unless you have enough disk space to make a copy of the table in the same dbspace that you already have, you're going to have to unload, drop, and rebuild the table. I would strongly recommend the HPL to do the unload and re-load. Make sure that you do a "no-conversion" unload. You'll have to create the table yourself, but once you do that, you can reload the data, and the job will run very fast. After the data is loaded, you'll want to build the indexes. You can do this with PDQ set fairly high, and get good performance. Feel free to mail me if you want help with this; I've got more experience than I want in this process. -- Dan Michaelis Database Administrator dan@kax.com Sent via Deja.com http://www.deja.com/ Before you buy.