RE: Explanation of tbcheck -pt output

> 2. When monitoring on a daily basis only the value of data pages > increases. Pages used stays constant. Why? I have been told by Informix that the "number of pages used" is a "high-water" mark, i.e., this is the most pages that have been used since the table was created/reorg'd. So if the table gets very large, and then someone does a lot of deletes, this value will still reflect the "large" value. Don't know why this is so. Seems that the data/index pages used *do* go up and down... HTH, Paul Mosser > -----Original Message----- > From: Csom Gyula [mailto:Csom@interface.hu] > Sent: Saturday, December 16, 2000 7:01 PM > To: 'Jinu.Joseph@citicorp.com'; informix-list@iiug.org > Subject: RE: Explanation of tbcheck -pt output > > > Hi, > > 1. Generally when an extent is added to a tablespace all > pages are allocated > but > none of them is used - well except one (so called "bitmap > page") which is > used > for tracking data pages. The database allocates the pages but > yet doesn't > use > them. Does that make sense? The idea behind this is that an > extent is always > a > continous space of the HDD thus access is much faster... > 2. No idea. To my understanding "Pages used" should also increase... > 3. Question of what you are interested about. If you wanna > know the number > of > allocated pages the answer is "Number of pages allocated". If > you wanna know > how much the table actually "uses" (eg. stores data there) > the answer is > "Number of pages used". > 4. Extent is automatically added to the tablespace when there > are no more > free > pages, eg. all allocated pages are used, or in other words > "Number of pages > allocated= Number of pages used". The space is computed from > the "Next > extent size" parameter and the number of extents. The default value of > the "Next extent size" is 8, but it could be also set in the > CREATE TABLE > statement. For instance: > > CREATE TABLE mytable(...) EXTENT SIZE 1000 NEXT SIZE 500;> > Note: in the CREATE TABLE statement, rather then pages, KByte > is used to > measure extent size. Thus, (on a 2K machine) mytable's next > extent size will > > be 250 pages. > > When mytable is created 500 pages are allocated and none is > used except > one. When the tablespace fills up the database allocates more > 250 pages. > Then > again when the 750 pages fill up the database allocates more > 250 pages. etc. > Note: with every 16th extent, the size is doubled. Thus for > mytable: 1st > extent > size is 500 pages, 2nd extent size is 250 pages..., 17th > extent size is 500 > pages..., 33rd extent size is 1000 pages..., 49th extent size is 2000 > pages..., etc. > > Good luck with your utility:) > > Csomi > > -----Original Message----- > From: Jinu.Joseph@citicorp.com [mailto:Jinu.Joseph@citicorp.com] > Sent: Saturday, December 16, 2000 9:47 PM > To: engines@iiug.org; informix-client-tools@iiug.org; > informix-list@iiug.org; linux-informix@iiug.org > Subject: Explanation of tbcheck -pt output > > > Hi, > The following is the output of the command tbcheck -pt for a > table in my > database : > ... > ... > Maximum row size 1549 > Number of special columns 5 > Number of keys 6 > Number of extents 32 > Current serial value 1 > First extent size 460800 > Next extent size 184320 > Number of pages allocated 5312017 > Number of pages used 5246787 > Number of data pages 3134215 > Number of data bytes 554916459 > Number of rows 3134215 > ... > ... > > Need the answer for the following questions: > 1. Why is there a difference between pages allocated and pages used ? > 2. When monitoring on a daily basis only the value of data pages > increases. Pages used stays constant. Why? > 3. How do I find out the actual space usage of the table ? > 4. When is the next extent added to a tblspace and how does it compute > the space required ? > > I am trying to develop a utily that will send out a warning when the > tablespace reaches a threshold limit of say 80% > > Thanks in advance > Jinu Joseph >