Oncheck interpretation problems

Hi, I inherited a Informix 11.10 IDS and I'm trying to understand the configuration. I'm worried about one table and ran an oncheck on it. The numer of pages used is identical to the number of pages allocated. Is the table full? (Last week we couldn't insert more data without deleting older rows, although I would assume an extent should just be added when the first extent is full, as the dbspace has 300GB free). What about the extent below in the report? It seems unused. TBLspace Report for abba:abba.logbuch Physical Address 3:5 Creation date 11/25/2009 13:50:39 TBLspace Flags 902 Row Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 240 Number of special columns 3 Number of keys 5 Number of extents 1 Current serial value 245314179 Current SERIAL8 value 1 Current REFID value 1 Pagesize (k) 2 First extent size 16777215 Next extent size 4404000 Number of pages allocated 16777215 Number of pages used 16777215 Number of data pages 8139695 Number of rows 144052679 Partition partnum 3145730 Partition lockid 3145730 Extents Logical Page Physical Page Size Physical Pages 0 3:61368525 16777215 16777215 Index 464_2119 fragment partition dynabbadbs in DBspace dynabbadbs Physical Address 3:16 Creation date 11/25/2009 13:50:51 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 240 Number of special columns 0 Number of keys 1 Number of extents 2 Current serial value 1 Current SERIAL8 value 1 Current REFID value 1 Pagesize (k) 2 First extent size 908765 Next extent size 238550 Number of pages allocated 1385865 Number of pages used 1241519 Number of data pages 0 Number of rows 0 Partition partnum 3145741 Partition lockid 3145730 Extents Logical Page Physical Page Size Physical Pages 0 3:78145740 908765 908765 908765 3:91268473 477100 477100

16777215 is the maximum pages for a fragment... Earlier today a number of options were shown for someone encountering the same limitation. On Apr 21, 2012 5:26 PM, "RAPHAëL GODART" <raf.godart@gmail.com> wrote: > Hi, > > I inherited a Informix 11.10 IDS and I'm trying to understand the > configuration. I'm worried about one table and ran an oncheck on it. > > The numer of pages used is identical to the number of pages allocated. Is > the > table full? (Last week we couldn't insert more data without deleting older > rows, although I would assume an extent should just be added when the first > extent is full, as the dbspace has 300GB free). > > What about the extent below in the report? It seems unused. > > TBLspace Report for abba:abba.logbuch > > Physical Address 3:5 > > Creation date 11/25/2009 13:50:39 > > TBLspace Flags 902 Row Locking > > TBLspace contains VARCHARS > > TBLspace use 4 bit bit-maps > > Maximum row size 240 > > Number of special columns 3 > > Number of keys 5 > > Number of extents 1 > > Current serial value 245314179 > > Current SERIAL8 value 1 > > Current REFID value 1 > > Pagesize (k) 2 > > First extent size 16777215 > > Next extent size 4404000 > > Number of pages allocated 16777215 > > Number of pages used 16777215 > > Number of data pages 8139695 > > Number of rows 144052679 > > Partition partnum 3145730 > > Partition lockid 3145730 > > Extents > > Logical Page Physical Page Size Physical Pages > > 0 3:61368525 16777215 16777215 > > Index 464_2119 fragment partition dynabbadbs in DBspace dynabbadbs > > Physical Address 3:16 > > Creation date 11/25/2009 13:50:51 > > TBLspace Flags 802 Row Locking > > TBLspace use 4 bit bit-maps > > Maximum row size 240 > > Number of special columns 0 > > Number of keys 1 > > Number of extents 2 > > Current serial value 1 > > Current SERIAL8 value 1 > > Current REFID value 1 > > Pagesize (k) 2 > > First extent size 908765 > > Next extent size 238550 > > Number of pages allocated 1385865 > > Number of pages used 1241519 > > Number of data pages 0 > > Number of rows 0 > > Partition partnum 3145741 > > Partition lockid 3145730 > > Extents > > Logical Page Physical Page Size Physical Pages > > 0 3:78145740 908765 908765 > > 908765 3:91268473 477100 477100 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016368e1d5b856d3804be2b610a

Thanks for pointing this out. I have a few more questions. 1. How come Number of pages allocated = Number of pages used? Is it because the table got filled once? Why doesn't the numer of pages used decrease when I delete old records? 2. The Number of pages allocated is 16777215, the Number of pages used is 16777215. How come I can still insert data into the table. Is Number of data pages the actual number of pages that I can still use to insert data?

Yes. One of the few limits on an Informix table is that no single partition can contain more than 16,777,215 pages and you table now has exactly that many pages. You have two main options: 1. Fragment the table into multiple partitions (see FRAGMENT BY in the create table and alter table descriptions in the Guide to SQL Syntax manual): ALTER TABLE <tablename> INIT FRAGMENT BY <fragmentation expression>; 2. Create a dbspace with a wider page size and move the table there so that you will have fewer pages in the table's single partition. ALTER TABLE <tablename> INIT IN <dbspace with pagesize wider than 2K>; For both of these you may want to ALTER the table to TYPE(RAW) before moving its contents and then ALTER it back to TYPE(STANDARD) afterwards to avoid long transaction rollbacks. 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 Sat, Apr 21, 2012 at 3:25 AM, RAPHAëL GODART <raf.godart@gmail.com>wrote: > Hi, > > I inherited a Informix 11.10 IDS and I'm trying to understand the > configuration. I'm worried about one table and ran an oncheck on it. > > The numer of pages used is identical to the number of pages allocated. Is > the > table full? (Last week we couldn't insert more data without deleting older > rows, although I would assume an extent should just be added when the first > extent is full, as the dbspace has 300GB free). > > What about the extent below in the report? It seems unused. > > TBLspace Report for abba:abba.logbuch > > Physical Address 3:5 > > Creation date 11/25/2009 13:50:39 > > TBLspace Flags 902 Row Locking > > TBLspace contains VARCHARS > > TBLspace use 4 bit bit-maps > > Maximum row size 240 > > Number of special columns 3 > > Number of keys 5 > > Number of extents 1 > > Current serial value 245314179 > > Current SERIAL8 value 1 > > Current REFID value 1 > > Pagesize (k) 2 > > First extent size 16777215 > > Next extent size 4404000 > > Number of pages allocated 16777215 > > Number of pages used 16777215 > > Number of data pages 8139695 > > Number of rows 144052679 > > Partition partnum 3145730 > > Partition lockid 3145730 > > Extents > > Logical Page Physical Page Size Physical Pages > > 0 3:61368525 16777215 16777215 > > Index 464_2119 fragment partition dynabbadbs in DBspace dynabbadbs > > Physical Address 3:16 > > Creation date 11/25/2009 13:50:51 > > TBLspace Flags 802 Row Locking > > TBLspace use 4 bit bit-maps > > Maximum row size 240 > > Number of special columns 0 > > Number of keys 1 > > Number of extents 2 > > Current serial value 1 > > Current SERIAL8 value 1 > > Current REFID value 1 > > Pagesize (k) 2 > > First extent size 908765 > > Next extent size 238550 > > Number of pages allocated 1385865 > > Number of pages used 1241519 > > Number of data pages 0 > > Number of rows 0 > > Partition partnum 3145741 > > Partition lockid 3145730 > > Extents > > Logical Page Physical Page Size Physical Pages > > 0 3:78145740 908765 908765 > > 908765 3:91268473 477100 477100 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec5299a311217e804be3c6acf

See answers to your questions below: 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 Sat, Apr 21, 2012 at 5:01 AM, RAPHAëL GODART <raf.godart@gmail.com>wrote: > Thanks for pointing this out. I have a few more questions. > > 1. How come Number of pages allocated = Number of pages used? Is it because > the table got filled once? Why doesn't the numer of pages used decrease > when I > delete old records? > Because all pages that have been allocated to the table have data on them, or at least they once did. When you delete rows, the space on the pages is available for reuse by new rows but is not "unused" partly because there are likely other rows still present on the data pages that contained the deleted rows. Even if all of the rows on a page were deleted, the page is still 'used' since it marked as a data pages and so cannot be used for any other purpose. > > 2. The Number of pages allocated is 16777215, the Number of pages used is > 16777215. How come I can still insert data into the table. Is Number of > data > pages the actual number of pages that I can still use to insert data? > > You are inserting rows into the slots on the allocated/used pages that were vacated by deleting rows. The oncheck -pT report will tell you the number of pages that are completely full, partially full, and empty within the pages allocated to the table. Partially full means a least 1/3 empty. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba959ee6ac404be3c7cca

Many thanks for your answers. Oncheck -pT takes a long time (several minutes) without showing a report. My prehistoric production processes crash because oncheck seems to put a lock on the table. How long should an oncheck -pT take before showing the table report? (is it normal it takes several minutes?) Is there another way of knowing or estimating the free pages (or free space in partially used pages) in the table? (by means of a query for example).

Download my package, utils2_ak, fro the IIUG Software Repository and built it. In there is a utility, PrintfreeB.ec that will produce a report very similar to the oncheck -pT report with no locking. 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 Sun, Apr 22, 2012 at 10:03 AM, RAPHAëL GODART <raf.godart@gmail.com>wrote: > Many thanks for your answers. Oncheck -pT takes a long time (several > minutes) > without showing a report. My prehistoric production processes crash because > oncheck seems to put a lock on the table. > > How long should an oncheck -pT take before showing the table report? (is it > normal it takes several minutes?) > > Is there another way of knowing or estimating the free pages (or free > space in > partially used pages) in the table? (by means of a query for example). > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba75f4331de04be4a854c

Could option 3 be: unload the table, drop it, re-create, load back in, create indexes and update its statistics?.. This would free up the pages used by the deleted rows, plus optimize the index files.

.. I forgot to mention, re-create the dbspace with a larger page size. Also keep in mind that the maximum number of rows that a page can hold is 255 rows, so since your row size is 240, use this to figure out the best page size. Generally, the more rows you can fit in a page, the better, and larger page sizes perform better, but there's also a performance threshold when the page size is too large!