Re: 136: ISAM error: no more extents even with 7 e

My problem is that I reached the max number of pages, so the only solution seems to be fragmentation. Regards! Emiliano Romero escribió: > 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 can also move the table to a DBSPACE with a larger page size like 4k or 8k that way you will fit more rows on a page and thus use fewer pages for the same number of records. --Dave On Thu, Mar 11, 2010 at 1:41 PM, Emiliano Romero <eromero@sitrack.com>wrote: > My problem is that I reached the max number of pages, so the only > solution seems to be fragmentation. > > Regards! > > Emiliano Romero escribió: > > 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. > > --00163641661d3d333704818b0918

Be careful. The limit of 255 rows per page has not changed. Larger page sizes do not raise the limit of rows per fragement. Larger page sizes get you larger rows on a page without any remainder pages, but not more rows. Cheers, Dick Snoke Executive IT Specialist IBM Software Group - ChannelWorks Tel: (404) 487-1595 Email: dsnoke@us.ibm.com From: "Informix DBA" <in4mixdba@gmail.com> To: ids@iiug.org Date: 03/11/10 02:02 PM Subject: Re: 136: ISAM error: no more extents even with.... [19316] Sent by: ids-bounces@iiug.org You can also move the table to a DBSPACE with a larger page size like 4k or 8k that way you will fit more rows on a page and thus use fewer pages for the same number of records. --Dave On Thu, Mar 11, 2010 at 1:41 PM, Emiliano Romero <eromero@sitrack.com>wrote: > My problem is that I reached the max number of pages, so the only > solution seems to be fragmentation. > > Regards! > > Emiliano Romero escribió: > > 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. > > --00163641661d3d333704818b0918 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

It will get you more rows as long as the rowsize is wider than 4 bytes which is the ONLY row size that can fit 255 rows on a 2K page! Now if you have an 8byte row you will waste half of each page when using 8K pages, but a 4K pagesize WILL double the number of rows per page versus a 2K pagesize and therefore halve the number of pages required. 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 3:57 PM, Richard Snoke <dsnoke@us.ibm.com> wrote: > Be careful. The limit of 255 rows per page has not changed. Larger page > sizes do not raise the limit of rows per fragement. Larger page sizes get > you larger rows on a page without any remainder pages, but not more rows. > > Cheers, > Dick Snoke > Executive IT Specialist > IBM Software Group - ChannelWorks > > Tel: (404) 487-1595 > Email: dsnoke@us.ibm.com > > From: > "Informix DBA" <in4mixdba@gmail.com> > To: > ids@iiug.org > Date: > 03/11/10 02:02 PM > Subject: > Re: 136: ISAM error: no more extents even with.... [19316] > Sent by: > ids-bounces@iiug.org > > You can also move the table to a DBSPACE with a larger page size like 4k > or > 8k that way you will fit more rows on a page and thus use fewer pages for > the same number of records. > > --Dave > > On Thu, Mar 11, 2010 at 1:41 PM, Emiliano Romero > <eromero@sitrack.com>wrote: > > > My problem is that I reached the max number of pages, so the only > > solution seems to be fragmentation. > > > > Regards! > > > > Emiliano Romero escribió: > > > 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. > > > > > > --00163641661d3d333704818b0918 > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015173fe48429d8c404818ced6a