Free Extents

I am running IDS 9.40 on a Unix box. I have a dbspace configured with 5 chunks. In this case we will call them Chunk #5,6,7,8 and 9. This particular dbspace is dedicated to one large table. I recently deleted millions of rows from this table. My question is, how do I determine the available space free within the dbspace if extents have already been allocated to a dbspace. onstat -d does not update the free space because the extents were already allocated. oncheck -pe does not show any free space. oncheck -pT shows me 4 extents used divided between 3 of the chunks (#5,6 & 9 above).sysmaster.sysextents shows me 4 extents are allocated to the table divided between 3 of the chunks. Whereas I can see 4 extents are used within 3 chunks I cannot figure out how much free space is within each chunk. Any help would be appreciated.

It's a conundrum allright. Just because you've deleted data from a page does not mean that that page frees up. You have 10 rows on a full page. You delete rows 5 and 6. That space cannot be used until the page is compressed and rewritten (so that rows 7,8,910 become (effectively) 5,6,7,8). You have 10 rows on a page, you delete 8, the next time the engine reads that page, it determines that it could compress the page and it does so, freeing up room for 8 more rows on that page. (you can see this activity with onstat -p under the 'compress' heading) (Mind you this is with fixed rowsizes - things get even more interesting with variable length rows (i.e. rows which contain varchars)). However, once a page is allocated (in an extent) to a table, it cannot be de-allocated as such. The only way to accomplish this is to re-write the table - either via unload/drop/recreate/reload, alter index to cluster or the equivalent. In other words, there is no way to accurately tell you how much free space you have in a table until it is reorganized. You can estimate the amount of space free which can be useful for fixed length rows and to some degree useful with variable length rows. Some places you may want to look: sysmaster:sysptnhdr - which has pages allocated/used etc. Join to it either with the partnum from yourdatabase:systables or yourdatabase:sysfragments for fragmented tables. You can take the rowsize from systables and multiply this times nrows to get an idea of rows - there are some decent spreadsheets around which can take this info and turn it into an accurate size number. Note that nrows is updated by update statistics. One of the reasons I am keen on fragmentation is the ability to drop old data and immediately have that space available for re-use - which would make your question easier to answer. cheers j. >From: KATHY DUCHENE <kathy.duchene@state.mn.us> >Date: 2007/09/13 Thu PM 01:45:22 CDT >To: ids@iiug.org >Subject: Free Extents [9953] >I am running IDS 9.40 on a Unix box. > >I have a dbspace configured with 5 chunks. In this case we will call them >Chunk #5,6,7,8 and 9. This particular dbspace is dedicated to one large table. > >I recently deleted millions of rows from this table. My question is, how do I >determine the available space free within the dbspace if extents have already >been allocated to a dbspace. > >onstat -d does not update the free space because the extents were already >allocated. >oncheck -pe does not show any free space. >oncheck -pT shows me 4 extents used divided between 3 of the chunks (#5,6 & 9 >above).>sysmaster.sysextents shows me 4 extents are allocated to the table divided >between 3 of the chunks. > >Whereas I can see 4 extents are used within 3 chunks I cannot figure out how >much free space is within each chunk. Any help would be appreciated. > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.