Re: Explanation of tbcheck -pt output

Whoops - pressed the SEND button before I was done. Hope this info helps. Allen Jantzen, DBA Ned Davis Research allenj wrote: > > Jinu.Joseph@citicorp.com wrote: > > > > 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 > > ... > > ... > > Space for a database table is preallocated. When you first create a > table, you indicate the extent size and the next extent size. The > first is how much space to allocate up front for the table, and the > second is how big each successive allocation should be after the first > allocation gets used up. > > > > > Need the answer for the following questions: > > 1. Why is there a difference between pages allocated and pages used ? > > Pages allocated is how much space has actually allocated for the table, > based on extent size and next extent size. > > > 2. When monitoring on a daily basis only the value of data pages > > increases. Pages used stays constant. Why? > > Data pages will go up as you add rows to the table. Contrast this to > index pages. > > > 3. How do I find out the actual space usage of the table ? > > Base it on "Number of pages used". Remember that tbcheck -pt shows > output based on pages, not kilobytes. Depending on your platform, a > page is probably 2K, so multiply pages by 2 to get K. > > > 4. When is the next extent added to a tblspace and how does it compute > > the space required ? > > The next extent is added when the initial extent is used up. The engine > will try to allocate the amount of space indicated by next extent size > for > the table. > > > I am trying to develop a utily that will send out a warning when the > > tablespace reaches a threshold limit of say 80% > > Try something like this to calculate the percentage used: > > oncheck -pT $1 |> grep -e 'TBLSpace Report for' > -e ' TBLSpace Flags' > -e ' First extent size' > -e ' Next extent size' > -e ' Number of pages allocated' > -e ' Number of pages used' > -e ' Number of extents' > -e ' Number of rows' | > awk '{if(index($0,"allocated")){ > allocate=$NF; > print $0 > getline > print $0 > printf(" Percentage Used > %d\\n",($NF/allocate)*100) > }else > print $0 > }'