Re: Explanation of tbcheck -pt output

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 }'