#!/usr/bin/ksh
######################################################################################################
# Author: Felipe Alkain de Souza
#
# Script Name: extend_tablespaces.sh
#
# Functionality: This script checks DB2 tablespaces size 
# and automatically extend them if necessary
#
# Usage: ./extend_tablespaces.sh -d <database_name> -t <threshold> -p <percentage_available_desired>
#
# Example: If you have a utilization threshold of 90% and want to bring the tablespace
# to 70% of utilization, you should execute this script like this:
#
# ./extend_tablespaces.sh -d sampledb -t 90 -p 30
#
######################################################################################################

. $HOME/sqllib/db2profile


#########################################
# Getting tablespace utilization
#########################################

rm -f ~/list.out

touch ~/list.out

db2 connect to $2 |grep "Local database alias" |wc -l > ~/connection.out

CONN=`cat ~/connection.out`

if [ $CONN -eq 0 ]
then
        echo " The database did not connect or the database name is wrong "

        exit 3
fi

db2 select "TABLESPACE_ID as id, SUBSTR(TABLESPACE_NAME, 1, 20) as name, \
INT(PAGE_SIZE) as PAGE_SIZE, INT(TOTAL_PAGES) as TOTAL_PAGES, \
INT(USED_PAGES) as USED_PAGES, \
INT(DECIMAL(TOTAL_PAGES,10,2)-DECIMAL(USED_PAGES,10,2)) as FREE_PAGES,\
'      ' CONCAT (SUBSTR(CHAR(DECIMAL(USED_PAGES, 10, 2)/ \
DECIMAL(TOTAL_PAGES,10,2)*100),9,5)) CONCAT '%' as PERCENT_USED, \
INT(NUM_CONTAINERS) as N_CONTAINERS \
from table (snapshot_tbs_cfg('$2', 0)) as t \
where TABLESPACE_TYPE=0 order by PERCENT_USED desc" > ~/full_tbspace.out

cat ~/full_tbspace.out |awk '{print $2, "%"$7}' |grep [0-9] > ~/tspace.out

cat ~/tspace.out |awk -F% '{print $1}' > ~/Name.out

cat ~/tspace.out |awk -F% '{print $2}' |cut -c1,2 > ~/Utilization.out

A=`cat ~/tspace.out | wc -l`

letra=p

count=1

while [ $A -ge $count ]
do
Perc=`sed -n $count$letra ~/Utilization.out`
if [ $Perc -ge $4 ]
	then

		grep `sed -n $count$letra ~/Name.out` ~/full_tbspace.out |awk '{print $5}' > ~/used_pages.txt
		
		USED_PAGES=`cat ~/used_pages.txt` 
		
		FREE=`echo "100-$6" |bc`
		
		grep `sed -n $count$letra ~/Name.out` ~/full_tbspace.out |awk '{print $4}' > ~/total_pages.txt
		
		TOTAL_PAGES=`cat ~/total_pages.txt`
		
		echo "(($USED_PAGES*100)/$FREE)-$TOTAL_PAGES" |bc > ~/partial_total.txt
		
		grep `sed -n $count$letra ~/Name.out` ~/full_tbspace.out |awk '{print $8}' > ~/num_container.txt
		
		PARC_TOTAL=`cat ~/partial_total.txt`
		
		NUM_CONT=`cat ~/num_container.txt`
		
		grep `sed -n $count$letra ~/Name.out` ~/full_tbspace.out |awk '{print $3}' > ~/page_size.txt
		
		PAGE_SIZE=`cat ~/page_size.txt`
		
		echo "($PARC_TOTAL*$PAGE_SIZE/1024/1024)" |bc > ~/req_space.txt
		
		REQ_SPACE=`cat ~/req_space.txt`
		
		FS_SPACE=`df -m $HOME |awk '{print $3}' |tail -1`

		echo "\nThe tablespace `sed -n $count$letra ~/Name.out` need to be extended. It is required $REQ_SPACE MB of disk space and you have $FS_SPACE MB available now."
		
		echo "\nDo you authorize to extend the tablespace (y/n)?"; read ANSWER
		
		if [ $ANSWER = "y" ]
		then	
		
			if [ $NUM_CONT -gt 1 ]
			then
		
			echo "$PARC_TOTAL/$NUM_CONT" |bc > ~/increase_pages.txt
			
			INCREASE=`cat ~/increase_pages.txt`

			echo "db2 connect to $2; db2 \"alter tablespace `sed -n $count$letra ~/Name.out` extend (all $INCREASE)\"" |sh > /dev/null
			
			else		
		
				grep `sed -n $count$letra ~/Name.out` ~/full_tbspace.out |awk '{print $1}' > ~/tbspc_cont.txt			
			
				CONTAINER=`cat ~/tbspc_cont.txt`
			
				db2 list tablespace containers for $CONTAINER |grep "Name" |awk -F"= " '{print $2}' > ~/container_file.txt
			
				CONT_FILE=`cat ~/container_file.txt`

				db2 connect to $2 > /dev/null
			
                        	echo "db2 connect to $2; db2 \"alter tablespace `sed -n $count$letra ~/Name.out` extend (file '$CONT_FILE' $PARC_TOTAL)\"" |sh > /dev/null


			fi
			
			
		else

        echo " The tablespace `sed -n $count$letra ~/Name.out` is about `sed -n $count$letra ~/Utilization.out`% of utilization , please check." >> ~/list.out
		
		fi
	fi
	
let "count=$count+1"
done

if [ `cat ~/list.out | wc -l` -ge 1 ]
then
		echo "#########"
		echo "#WARNING#"
		echo "#########"
		echo "\nThe following tablespaces under $2 database crossed the specified threshold of $4%:"
        echo >> ~/list.out
                cat ~/list.out

fi

echo "\nCheck below the current tablespace utilization after script execution: "

db2 select "SUBSTR(TABLESPACE_NAME, 1, 20) as name, \
'      ' CONCAT (SUBSTR(CHAR(DECIMAL(USED_PAGES, 10, 2)/ \
DECIMAL(TOTAL_PAGES,10,2)*100),9,5)) CONCAT '%' as PERCENT_USED \
from table (snapshot_tbs_cfg('$2', 0)) as t \
where TABLESPACE_TYPE=0 order by PERCENT_USED desc"


#########################################
# Removing script files
#########################################

rm -f ~/tspace.out
rm -f ~/full_tbspace.out
rm -f ~/connection.out
rm -f ~/Utilization.out
rm -f ~/Name.out
rm -f ~/increase_pages.txt
rm -f ~/used_pages.txt
rm -f ~/total_pages.txt
rm -f ~/req_space.txt
rm -f ~/partial_total.txt
rm -f ~/page_size.txt
rm -f ~/num_container.txt
rm -f ~/tbspc_cont.txt
rm -f ~/container_file.txt

exit 0