native_error=-271, msg=Could not insert new row into the table

Hi, We are getting an error -271 while inserting records to a table even though we see the database free space is more than 60%. Our Informix Version is : Informix Dynamic Server 2000 Version 9.21.UC4 on Solaris 9 To sort the issue, I dropped the table and recreated an empty table. Could you please let me know on what reason the database could not add new rows to a table. During the insert error, there was around 5 million rows in the table. The row size on the table is 1720 and the index size is 325. Extent size is 16 and next size is 16, lock more is row. Is there any table size restrictions on the DB version or can there be any bug. We are daily performing update statistics on the full DB. Please let me know your thoughts on the issue. Dhaya

Informix always raises two errors. A generic one and a detailed one. The later is the ISAM error code. -271 is the "generic" code. We would need the ISAM error code also... >From your details I cannot give you a definitive answer, but you EXTENT SIZE/NEXT SIZE seems to short for the table dimension. One usual error on these situations is the limit of extents a table can have. This limit is not the same always. It varies with the table schema (mostly with the number os special - VARCHAR - columns) and the page size. On 2K page sizes the limit usually is around 200, but can be lower if your table has lots of VARCHARs. On 4K page sizes it's around the double of that. You probably should alter the extent definitions (ALTER TABLE ... NEXT SIZE ...) V11.7 does not suffer from this issue. In any case, always get the ISAM error code. Regards. On Thu, Mar 31, 2011 at 6:22 PM, sdhaya <dhaya.snidhi@gmail.com> wrote: > Hi, > > We are getting an error -271 while inserting records to a table even > though we see the database free space is more than 60%. > > Our Informix Version is : Informix Dynamic Server 2000 Version > 9.21.UC4 on Solaris 9 > > To sort the issue, I dropped the table and recreated an empty table. > > Could you please let me know on what reason the database could not add > new rows to a table. During the insert error, there was around 5 > million rows in the table. The row size on the table is 1720 and the > index size is 325. Extent size is 16 and next size is 16, lock more is > row. > > Is there any table size restrictions on the DB version or can there be > any bug. We are daily performing update statistics on the full DB. > > Please let me know your thoughts on the issue. > > Dhaya > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

On Mar 31, 8:43 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > Informix always raises two errors. A generic one and a detailed one. The > later is the ISAM error code. > -271 is the "generic" code. We would need the ISAM error code also...>From your details I cannot give you a definitive answer, but you EXTENT > > SIZE/NEXT SIZE seems to short for the table dimension. > One usual error on these situations is the limit of extents a table can > have. This limit is not the same always. It varies with the table schema > (mostly with the number os special - VARCHAR - columns) and the page size. > On 2K page sizes the limit usually is around 200, but can be lower if your > table has lots of VARCHARs. On 4K page sizes it's around the double of that. > > You probably should alter the extent definitions (ALTER TABLE ... NEXT SIZE > ...) > > V11.7 does not suffer from this issue. > In any case, always get the ISAM error code. > > Regards. > > > > > > On Thu, Mar 31, 2011 at 6:22 PM, sdhaya <dhaya.sni...@gmail.com> wrote: > > Hi, > > > We are getting an error -271 while inserting records to a table even > > though we see the database free space is more than 60%. > > > Our Informix Version is : Informix Dynamic Server 2000 Version > > 9.21.UC4 on Solaris 9 > > > To sort the issue, I dropped the table and recreated an empty table. > > > Could you please let me know on what reason the database could not add > > new rows to a table. During the insert error, there was around 5 > > million rows in the table. The row size on the table is 1720 and the > > index size is 325. Extent size is 16 and next size is 16, lock more is > > row. > > > Is there any table size restrictions on the DB version or can there be > > any bug. We are daily performing update statistics on the full DB. > > > Please let me know your thoughts on the issue. > > > Dhaya > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently...- Hide quoted text - > > - Show quoted text - Thanks for the reply... For a table of this size and if would like to store around 10 millions rows, could you please recommend the extent size and next size . Our Page size is 2K. Dhaya

Could be one of three problems, the ISAM level error code would tell you which. Solutions included: 1. No space available in the dbspace. 1. Add chunks - but you indicate that this is not the problem 2. The table (including any attached index pages) exceeds the 16million page limit (2^24 pages actually) 1. Detach all indexes to free pages in the table's partition for more data 2. Fragment the table across multiple dbspaces to permit more rows. 3. The table (' ') has more than the maximum number of extents which is about 200 depending on the number of attached indexes and special columns (VARCHAR, BLOBs, etc) the table has. 1. Reorg the table into fewer extents. Extent sizing is dependent on many factors, here are a few guidelines: - You only want one or two extents to be actively accessed, so size the extent to the size of the active data. If six months of data are active, then the next extent size should hold six months of data. - Maximum extent size is a chunk, so in 9.21 maximum chunk size was 2GB.... - During a reorg some like to have all of the existing data in one or the smallest number of chunks, so in this case, you will need 5million pages in the initial extent, but your max is 1million pages (2GB chunk limit), so... Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Mar 31, 2011 at 1:22 PM, sdhaya <dhaya.snidhi@gmail.com> wrote: > Hi, > > We are getting an error -271 while inserting records to a table even > though we see the database free space is more than 60%. > > Our Informix Version is : Informix Dynamic Server 2000 Version > 9.21.UC4 on Solaris 9 > > To sort the issue, I dropped the table and recreated an empty table. > > Could you please let me know on what reason the database could not add > new rows to a table. During the insert error, there was around 5 > million rows in the table. The row size on the table is 1720 and the > index size is 325. Extent size is 16 and next size is 16, lock more is > row. > > Is there any table size restrictions on the DB version or can there be > any bug. We are daily performing update statistics on the full DB. > > Please let me know your thoughts on the issue. > > Dhaya > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

> > > million rows in the table. The row size on the table is 1720 and the > For a table of this size and if would like to store around 10 millions initial extent size i would choose <your page size > * 10 millions (1720 row size means 1 row on a page.) Next extent size is dependent on how fast this grows say 1 million a year, then i would probably have a next size of <your page size > * 1 millions there is an ugly hack on ftp://ftp.iiug.org/pub/informix/pub/ called size9.sql you can play around on a test box, it will calc the ext. size for you based on the nr of rows. Simply create the tables and indexes... It does require handy work to get it going!! Superboer.