#!/usr/bin/ksh
###########################################################################
#
# Author: Felipe Alkain de Souza
#
# Functionality: Calculate all user schemas size under a specified database
#
# Usage: <script_name> -db <database_name> -scale <KB, MB or GB>
#
# Obs: It's recommended to perform a runstats before executing this script
#
###########################################################################


. $HOME/sqllib/db2profile

db2 connect to $2 > /dev/null

db2
"select distinct(tabschema) from syscat.tables where tabschema not like
'SYS%'" |egrep -v "TABSCHEMA|-" |grep -v record >
/tmp/schema_names.txt

count_total=`wc -l /tmp/schema_names.txt | awk '{print $1-2}'`

letra=p

count_cur=2

rm -f /tmp/output_file.txt

touch /tmp/output_file.txt

echo >> /tmp/output_file.txt

db2
"call get_dbsize_info(?,?,?,0)" |grep -p DATABASESIZE |tail -2 |awk -F:
'{print "TOTAL DATABASE SIZE: " $2/1024/1024/1024" GB"}' |head -1
>> /tmp/output_file.txt

while [ $count_total -ge $count_cur ]
do

schema_name=`sed -n $count_cur$letra /tmp/schema_names.txt`

echo >> /tmp/output_file.txt

echo $schema_name ":" >> /tmp/output_file.txt

if [ $4 == GB ]
then

db2
"select sum(a.fpages), b.pagesize from syscat.tables a,
syscat.tablespaces b where a.tabschema='$schema_name' and
a.tbspace=b.tbspace group by b.pagesize" |egrep -iv "e|--" |awk '{print
$1*$2}' |awk '{total=$1+total} END {print total/1024/1024/1024 " GB"}'
>> /tmp/output_file.txt

elif [ $4 == MB ]
then

db2
"select sum(a.fpages), b.pagesize from syscat.tables a,
syscat.tablespaces b where a.tabschema='$schema_name' and
a.tbspace=b.tbspace group by b.pagesize" |egrep -iv "e|--" |awk '{print
$1*$2}' |awk '{total=$1+total} END {print total/1024/1024 " MB"}'
>> /tmp/output_file.txt

else

db2 "select
sum(a.fpages), b.pagesize from syscat.tables a, syscat.tablespaces b
where a.tabschema='$schema_name' and a.tbspace=b.tbspace group by
b.pagesize" |egrep -iv "e|--" |awk '{print $1*$2}' |awk
'{total=$1+total} END {print total/1024 " KB"}' >>
/tmp/output_file.txt

fi

let "count_cur=$count_cur+1"

done

echo >> /tmp/output_file.txt

cat /tmp/output_file.txt

#mail -s "Schema size" mail@host < /tmp/output_file.txt