136: ISAM error: no more extents even with 7 exten

Hi list, I'm having a problem with my database. When I try to insert a row in my biggest table i get: 271: Could not insert new row into the table. 136: ISAM error: no more extents oncheck -pt shows: Physical Address 4:527 Creation date 01/24/2010 13:19:43 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 24 Number of special columns 0 Number of keys 0 Number of extents 7 Current serial value 1 Pagesize (k) 2 First extent size 5000000 Next extent size 750000 Number of pages allocated 16777215 Number of pages used 16777215 Number of data pages 16773053 Number of rows 1207659816 Partition partnum 4194396 Partition lockid 4194396 Extents Logical Page Physical Page Size Physical Pages 0 4:5095236 5750000 5750000 5750000 4:42968656 7500000 7500000 13250000 4:32405955 750000 750000 14000000 4:36380420 750000 750000 14750000 4:39023045 750000 750000 15500000 4:54801988 750000 750000 16250000 4:56145738 527215 527215 Indexes are very similar 5-7 extents. onstat -d shows: IBM Informix Dynamic Server Version 10.00.FC6 -- On-Line -- Up 44 days 16:37:13 -- 1684560 Kbytes Dbspaces address number flags fchunk nchunks pgsize flags owner name 8674ee78 1 0x40001 1 1 2048 N B informix rootdbs 879599e8 2 0x40001 2 1 2048 N B informix physdbs 87959b80 3 0x42001 3 1 2048 N TB informix tempdbs 87959d18 4 0x40001 4 1 2048 N B informix datadbs 4 active, 2047 maximum Chunks address chunk/dbs offset size free bpages flags pathname 8674f028 1 1 0 2560000 807158 PO-B /opt/informix/IFMXDATA/rootdbs 8674fd10 2 2 0 1000000 499947 PO-B /opt/informix/IFMXDATA/physdbs 87959028 3 3 0 1000000 995996 PO-B /opt/informix/IFMXDATA/tempdbs 879591c8 4 4 0 286346500 230360798 PO-B /opt/informix/IFMXDATA/datadbs and at the end of oncheck -pe I can see: FREE 58091703 228254797 Total Used: 55985702 Total Free: 230360798 Even with free space, and really far of getting my extent limit, I'm getting this error. The last extent is a little smaller than what I set in next extent size. I dont think i get out of contiguous space because oncheck -pe shows that I have 230360798 pages free. Any help should be great. Thanks and regards Emiliano Romero

You've hit the max # of pages for IDS (32-bit?) for a table. That's all the bitmap pages can keep track of. If you have IDS 10 or greater, you could recreate the table in a new dbspace with 16k pages or another option is to recreate the table as a fragmented table. Bob ----- Original Message ----- From: "Emiliano Romero" <eromero@sitrack.com> To: ids@iiug.org Sent: Thursday, March 11, 2010 1:02:48 PM GMT -05:00 US/Canada Eastern Subject: 136: ISAM error: no more extents even with 7 e.... [19312] Hi list, I'm having a problem with my database. When I try to insert a row in my biggest table i get: 271: Could not insert new row into the table. 136: ISAM error: no more extents oncheck -pt shows: Physical Address 4:527 Creation date 01/24/2010 13:19:43 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 24 Number of special columns 0 Number of keys 0 Number of extents 7 Current serial value 1 Pagesize (k) 2 First extent size 5000000 Next extent size 750000 Number of pages allocated 16777215 Number of pages used 16777215 Number of data pages 16773053 Number of rows 1207659816 Partition partnum 4194396 Partition lockid 4194396 Extents Logical Page Physical Page Size Physical Pages 0 4:5095236 5750000 5750000 5750000 4:42968656 7500000 7500000 13250000 4:32405955 750000 750000 14000000 4:36380420 750000 750000 14750000 4:39023045 750000 750000 15500000 4:54801988 750000 750000 16250000 4:56145738 527215 527215 Indexes are very similar 5-7 extents. onstat -d shows: IBM Informix Dynamic Server Version 10.00.FC6 -- On-Line -- Up 44 days 16:37:13 -- 1684560 Kbytes Dbspaces address number flags fchunk nchunks pgsize flags owner name 8674ee78 1 0x40001 1 1 2048 N B informix rootdbs 879599e8 2 0x40001 2 1 2048 N B informix physdbs 87959b80 3 0x42001 3 1 2048 N TB informix tempdbs 87959d18 4 0x40001 4 1 2048 N B informix datadbs 4 active, 2047 maximum Chunks address chunk/dbs offset size free bpages flags pathname 8674f028 1 1 0 2560000 807158 PO-B /opt/informix/IFMXDATA/rootdbs 8674fd10 2 2 0 1000000 499947 PO-B /opt/informix/IFMXDATA/physdbs 87959028 3 3 0 1000000 995996 PO-B /opt/informix/IFMXDATA/tempdbs 879591c8 4 4 0 286346500 230360798 PO-B /opt/informix/IFMXDATA/datadbs and at the end of oncheck -pe I can see: FREE 58091703 228254797 Total Used: 55985702 Total Free: 230360798 Even with free space, and really far of getting my extent limit, I'm getting this error. The last extent is a little smaller than what I set in next extent size. I dont think i get out of contiguous space because oncheck -pe shows that I have 230360798 pages free. Any help should be great. Thanks and regards Emiliano Romero ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

On 11.03.10 19:02, Emiliano Romero wrote: > Hi list, I'm having a problem with my database. When I try to insert a > row in my biggest table i get: > 271: Could not insert new row into the table. > 136: ISAM error: no more extents> > oncheck -pt shows:> > Physical Address 4:527 > > Creation date 01/24/2010 13:19:43 > > TBLspace Flags 802 Row Locking > > TBLspace use 4 bit bit-maps > > Maximum row size 24 > > Number of special columns 0 > > Number of keys 0 > > Number of extents 7 > > Current serial value 1 > > Pagesize (k) 2 > > First extent size 5000000 > > Next extent size 750000 > > Number of pages allocated 16777215 > > Number of pages used 16777215 > > Number of data pages 16773053 > see http://www-01.ibm.com/support/docview.wss?rs=630&context=SSGU8G&dc=DB560&dc=DB52 0&uid=swg21377993&loc=en_US&cs=UTF-8&lang=en&rss=ct630db2

The problem isn't the number of extents but the number of pages. A single table or index partition can't have more than 16777215 pages and that's exactly the number of pages you have. You will have to fragment the table in to multiple partitions. Since you are using IDS 10.00 you will have to fragment the table into two or more dbspaces. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 11, 2010 at 1:02 PM, Emiliano Romero <eromero@sitrack.com>wrote: > Hi list, I'm having a problem with my database. When I try to insert a > row in my biggest table i get: > 271: Could not insert new row into the table. > 136: ISAM error: no more extents> > oncheck -pt shows:> > Physical Address 4:527 > > Creation date 01/24/2010 13:19:43 > > TBLspace Flags 802 Row Locking > > TBLspace use 4 bit bit-maps > > Maximum row size 24 > > Number of special columns 0 > > Number of keys 0 > > Number of extents 7 > > Current serial value 1 > > Pagesize (k) 2 > > First extent size 5000000 > > Next extent size 750000 > > Number of pages allocated 16777215 > > Number of pages used 16777215 > > Number of data pages 16773053 > > Number of rows 1207659816 > > Partition partnum 4194396 > > Partition lockid 4194396 > > Extents > > Logical Page Physical Page Size Physical Pages > > 0 4:5095236 5750000 5750000 > > 5750000 4:42968656 7500000 7500000 > > 13250000 4:32405955 750000 750000 > > 14000000 4:36380420 750000 750000 > > 14750000 4:39023045 750000 750000 > > 15500000 4:54801988 750000 750000 > > 16250000 4:56145738 527215 527215 > Indexes are very similar 5-7 extents. > onstat -d shows: > IBM Informix Dynamic Server Version 10.00.FC6 -- On-Line -- Up 44 > days 16:37:13 -- 1684560 Kbytes> > Dbspaces > address number flags fchunk nchunks pgsize flags > owner name > 8674ee78 1 0x40001 1 1 2048 N B > informix rootdbs > 879599e8 2 0x40001 2 1 2048 N B > informix physdbs > 87959b80 3 0x42001 3 1 2048 N TB > informix tempdbs > 87959d18 4 0x40001 4 1 2048 N B > informix datadbs > 4 active, 2047 maximum > > Chunks > address chunk/dbs offset size free bpages > flags pathname > 8674f028 1 1 0 2560000 807158 > PO-B /opt/informix/IFMXDATA/rootdbs > 8674fd10 2 2 0 1000000 499947 > PO-B /opt/informix/IFMXDATA/physdbs > 87959028 3 3 0 1000000 995996 > PO-B /opt/informix/IFMXDATA/tempdbs > 879591c8 4 4 0 286346500 230360798 > PO-B /opt/informix/IFMXDATA/datadbs > > and at the end of oncheck -pe I can see: > FREE 58091703 > 228254797 > > Total Used: 55985702 > Total Free: 230360798 > > Even with free space, and really far of getting my extent limit, I'm > getting this error. The last extent is a little smaller than what I set > in next extent size. I dont think i get out of contiguous space because > oncheck -pe shows that I have 230360798 pages free.> > Any help should be great. > > Thanks and regards > > Emiliano Romero > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0ce07a9c640c9204818cdf05

Art, Dave, Bob. Thanks everyone for your help, I'm going to fragment my table. Thanks and Regards Art Kagel escribió: > The problem isn't the number of extents but the number of pages. A single > table or index partition can't have more than 16777215 pages and that's > exactly the number of pages you have. You will have to fragment the table > in to multiple partitions. Since you are using IDS 10.00 you will have to > fragment the table into two or more dbspaces. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > 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 11, 2010 at 1:02 PM, Emiliano Romero <eromero@sitrack.com>wrote: > > >> Hi list, I'm having a problem with my database. When I try to insert a >> row in my biggest table i get: >> 271: Could not insert new row into the table. >> 136: ISAM error: no more extents>> >> oncheck -pt shows:>> >> Physical Address 4:527 >> >> Creation date 01/24/2010 13:19:43 >> >> TBLspace Flags 802 Row Locking >> >> TBLspace use 4 bit bit-maps >> >> Maximum row size 24 >> >> Number of special columns 0 >> >> Number of keys 0 >> >> Number of extents 7 >> >> Current serial value 1 >> >> Pagesize (k) 2 >> >> First extent size 5000000 >> >> Next extent size 750000 >> >> Number of pages allocated 16777215 >> >> Number of pages used 16777215 >> >> Number of data pages 16773053 >> >> Number of rows 1207659816 >> >> Partition partnum 4194396 >> >> Partition lockid 4194396 >> >> Extents >> >> Logical Page Physical Page Size Physical Pages >> >> 0 4:5095236 5750000 5750000 >> >> 5750000 4:42968656 7500000 7500000 >> >> 13250000 4:32405955 750000 750000 >> >> 14000000 4:36380420 750000 750000 >> >> 14750000 4:39023045 750000 750000 >> >> 15500000 4:54801988 750000 750000 >> >> 16250000 4:56145738 527215 527215 >> Indexes are very similar 5-7 extents. >> onstat -d shows: >> IBM Informix Dynamic Server Version 10.00.FC6 -- On-Line -- Up 44 >> days 16:37:13 -- 1684560 Kbytes>> >> Dbspaces >> address number flags fchunk nchunks pgsize flags >> owner name >> 8674ee78 1 0x40001 1 1 2048 N B >> informix rootdbs >> 879599e8 2 0x40001 2 1 2048 N B >> informix physdbs >> 87959b80 3 0x42001 3 1 2048 N TB >> informix tempdbs >> 87959d18 4 0x40001 4 1 2048 N B >> informix datadbs >> 4 active, 2047 maximum >> >> Chunks >> address chunk/dbs offset size free bpages >> flags pathname >> 8674f028 1 1 0 2560000 807158 >> PO-B /opt/informix/IFMXDATA/rootdbs >> 8674fd10 2 2 0 1000000 499947 >> PO-B /opt/informix/IFMXDATA/physdbs >> 87959028 3 3 0 1000000 995996 >> PO-B /opt/informix/IFMXDATA/tempdbs >> 879591c8 4 4 0 286346500 230360798 >> PO-B /opt/informix/IFMXDATA/datadbs >> >> and at the end of oncheck -pe I can see: >> FREE 58091703 >> 228254797 >> >> Total Used: 55985702 >> Total Free: 230360798 >> >> Even with free space, and really far of getting my extent limit, I'm >> getting this error. The last extent is a little smaller than what I set >> in next extent size. I dont think i get out of contiguous space because >> oncheck -pe shows that I have 230360798 pages free.>> >> Any help should be great. >> >> Thanks and regards >> >> Emiliano Romero >> >> >> >> >> > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >> > > --000e0ce07a9c640c9204818cdf05 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > >