Script to check High Water mark Reclaimable Tablespace

[former_member182505]

Hi Friends

This is my first blog i need to share some important information for db2. We are are facing File system space issue and using High Water Mark concept to

reduce the space and doing calculation for the same here below check the below script to find the tablespace where we can perform the HWM and how much

space we can reclaim . All the feedback and suggestions are welcome.

#!/usr/bin/ksh

#su - db2<SID>

db2 connect to <SID>

GFILE=/tmp/hwmCheck.log

>$GFILE

echo $SAPSYSTEMNAME >> $GFILE

echo "=============================================" >> $GFILE

echo " Total Reclaimabile Space for <SID> (Hostname) "> $GFILE

echo "=============================================" >> $GFILE

db2 "SELECT  varchar(TBSP_NAME,20) as TableSpace , ((((TBSP_PAGE_TOP * TBSP_PAGE_SIZE)/1000) - ((TBSP_USED_PAGES * TBSP_PAGE_SIZE)/1000)) / 1024 )   as Reclaimable_MB from TABLE(MON_GET_TABLESPACE

(

'',-2))  where TBSP_TYPE = 'DMS'  and (((((TBSP_PAGE_TOP * TBSP_PAGE_SIZE)/1000) - ((TBSP_USED_PAGES * TBSP_PAGE_SIZE)/1000)) / 1024 ) / 1024 ) > 0 and TBSP_AUTO_RESIZE_ENABLED = 1" >> $GFILE

TOTAL=$(db2 "SELECT   sum(((((TBSP_PAGE_TOP * TBSP_PAGE_SIZE)/1000) - ((TBSP_USED_PAGES * TBSP_PAGE_SIZE)/1000)) / 1024 ) / 1024)  from TABLE (MON_GET_TABLESPACE('',-2)) where TBSP_TYPE = 'DMS' and

(((((TBSP_PAGE_TOP * TBSP_PAGE_SIZE)/1000) - ((TBSP_USED_PAGES * TBSP_PAGE_SIZE)/1000)) / 1024 ) / 1024 ) > 0 and TBSP_AUTO_RESIZE_ENABLED = 1")

TOTAL=$( echo $TOTAL | awk '{ print $3 }')

echo "" >> $GFILE

echo "=============================================" >> $GFILE

#echo "Script Written By : Sadiq Iqbal" >> $GFILE

#print $TOTAL

print $GFILE

cat $GFILE

#cat $GFILE|mail -s "<SID> - Total Reclaimable GB : `echo $TOTAL | awk ' {print $3 }'`"  -c "sadiqiqbal4u@gmail.com" sadiqiqbal4u@gmail.com

mail -s "<SID> - Total Reclaimable GB : $TOTAL"sadiqiqbal4u@gmail.com  < $GFILE

#rm $GFILE

db2 disconnect <SID>

db2 terminate

exit

Note:-We  can get the tablespace list on /tmp/hwmCheck.log

Thanks

Sadiq