Hi All,
If the pages used in the tablespace are full ie now there is no space left for any new data. How to free this space? If I delete some rows, will DB2 free any space so that I can reuse it ? the tablespace is DMS.

Regards,

Abhijit

If you delete some rows from a tabl you will be able to use the space to insert records into that table ...

But this may not be good for performance ...

You'll have do a REORG for the table to release back pages to the tablespace

Sathyaram

Part of the answer depends on the "insert rule" of the table. The insert rule for the table is defined by the clustering index or whether the APPEND option is used on the table (which instructs DB2 to always put new rows at the end of the table). If no clustering index is defined, then new inserted rows go at the end.

So it is possible for DB2 to reuse space when rows are deleted, depending on where the deleted rows where, and where DB2 wants to put the new rows.

If rows are inserted into the middle of a table because of a clustering index, it is a good idea to define some percent free for the table, which tells DB2 to leave space throughout the table after a reorg.

It is also a good idea to define percent free for the indexes, which are always maintained by DB2 in their exact key sequence order. But the optimal amount of percent free needed for indexes may vary for each index, because some index entries are naturally (because of the application design) inserted at the end of the index, and others are inserted throughout the index.

[QUOTE][SIZE=1]Originally posted by Marcus_A
Part of the answer depends on the "insert rule" of the table. The insert rule for the table is defined by the clustering index or whether the APPEND option is used on the table (which instructs DB2 to always put new rows at the end of the table). If no clustering index is defined, then new inserted rows go at the end.

If a clustering index is not defined on a table and there is no insert rule on the table, and if rows are deleted from the table, on a subsequent insert, will DB2 not reuse the space available on a page(due to a delete)?

If there is no clustering index on the table, then rows are inserted at the end (same applies if APPEND option is used even if a clustering index exists). I believe that DB2 can re-use deleted rows at the end of the table, but I am not 100% sure.

I don't think it is exactly so. I believe what happens is DB2 first searches for free space within the used pages (using "free space lists"). If it can't find enough free space in the first few pages (how many - controlled by DB2MAXFSCRSEARCH registry variable) it then inserts data at the end of the table, and subsequent inserts will go there without searching for free space.

There's a good explanation of the process in the Admin. Guide: Performance, under "Space Management" section.

OK, you are correct if there is no APPEND defined on the table and there is no clustering index, then the FSCRs are looked at first.

n_i thank you - that's how I understood it and was a little confused. However, one correction(regained my foothold!)

the default FSCR is 5 - if it doesn't find space within the first 5 then it appends data until it fills 2 extents and then resumes searching FSCRs again where the last search left off. This process continues until it goes through all FSCRs for the table. Once it has gone through the entire table it keeps appending until a delete creates space in the table and the FSCR search resumes.

Is the algorithm completely different when clustering indexes are used? The manual says wrost-fit vs first-fit - what does that mean?

I believe "worst fit" means that the data is inserted into a page with most free space so that subsequent inserts with similar keys could also fit there.

The important thing to remember about tables with a clustering index defined is that DB2 first attempts to insert records on the same page as other records with similar index key values (using the clustering index key). If there is no space on that page, an attempt is made to put the row into empty space on nearby surrounding pages.

If there is still no success finding room on nearby pages, then the FSCR search algorithm is used, except that a worst-fit approach is used instead of a first-fit approach. This worst-fit approach tends to choose pages with more free space. This method establishes a new clustering area for rows with this key value.

Hi Sathyaram,
thanks for the solution..it works but only after I have to allocate a new container with few pages . One question is why I need to add this container to reorg the table?...why it is not allowing me to reorg the table when 100 % pages are used?..pl explain.

Regards,

abhijit

If you have percent free defined greater than 0, a reorg can make the table bigger than it was before the reorg. This is because percent free creates free space on each page to allow for inserts throughout the table as determined by the clustering index.

If you have no clustering index (and have determined it is not good to have one for this table), it might be better to use the APPEND clause on the table and set the percent free to 0. However, most applications do benefit from having a clustering index.