Pending free pages is a big headache in db2 v9.7. This is with automatic storage tablespaces.
Pending free pages does not release space back to File system.
Following query gives the top 25 tablespaces based on TBSP_PENDING_FREE_PAGES in desc order.
select char(TBSP_NAME,20) TBSP_NAME, sum(TBSP_USED_PAGES)TBSP_USED_PAGES, sum(TBSP_FREE_PAGES)TBSP_FREE_PAGES, sum(TBSP_TOTAL_PAGES)TBSP_TOTAL_PAGES, sum(TBSP_PAGE_TOP) TBSP_PAGE_TOP, sum(TBSP_PENDING_FREE_PAGES)TBSP_PENDING_FREE_PAGES FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t where TBSP_CONTENT_TYPE NOT IN ('USRTEMP','SYSTEMP') group by tbsp_name order by TBSP_PENDING_FREE_PAGES desc fetch first 25 rows only
So far, DB2 does not give any option which application is holding the pending free pages so that it can be killed. Raised this concern with IBM labs.
So far I was successful in the following ways
1. take the back of tablespace
2. db2_all "db2 connect to <dbname> ;db2 list tablespaces show detail;db2 terminate;" > /dev/null
3. Sometimes stopping eventmonitors also help
Below two methods I use to verify if the extent movement is in lock wait status.
1. db2 "select * from sysibmadm.SNAPLOCKWAIT"
Below two methods I use to verify if the extent movement is in lock wait status.
1. db2 "select * from sysibmadm.SNAPLOCKWAIT"
