#!/bin/ksh 
. $HOME/.profile
#
#---------------------------------------------------------------------------
# Display report on tablespace information
# Figure out size of the database
#
# Command format:
#   tspace database_name report
#   Example: tspace testdb report (Will write report to disk and to screen)
#   Example: tspace testdb        (Will just display output to screen)
# 	
# Note: report option will also send errors to dft_mail_id or page you 	
#---------------------------------------------------------------------------

#===========================================================================
# Functions
#===========================================================================
Fatal_Error ( )                        # Handle errors
{
   exit
}

#===========================================================================
# Parse list of arguments - Call function to handle error
#===========================================================================
# check for the database parameter
if [[ -z $1 ]] ; then
   error_msg="Error: No databases listed"
   Fatal_Error
else
   database=$1
   typeset -u DATABASE=$database
fi

if [ -z "$2" ] ; then 
  rpt_flag='N'
else
  typeset -u report=$2
  if [ $report = "REPORT" ] ; then 
    rpt_flag='Y'
  else
    rpt_flag='N' 
  fi                          
fi

#===========================================================================
# Variables
#===========================================================================
integer daily
daily=`date +%j`
dateh=$(date)
server=`uname -n`

datetime=$(date +"Date: %D  Time: %T")                 # Timestamp for errors
integer total
typeset -i psize
psize=0

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
# Variables you need to change
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
rpt_dir=$HOME                     # Tools Directory
rpt_name="database_size.out"
rpt_name=tspace.$server.$DATABASE.d$daily          # Name of report
rpt_out=$rpt_dir/$rpt_name

thresh_rpt_name=tspace.$server.$DATABASE.THRESH        # Name of threshold report
thresh_rpt_out=$rpt_dir/$thresh_rpt_name
thresh_counter='0'

err_rpt_name=tspace.$server.$DATABASE.ERROR        # Name of error report
err_rpt_out=$rpt_dir/$err_rpt_name

pct='80'  # Issue warning if tablespace is over this percentage full

rpt_retained='60' # Delete reports on disk older than rpt_retained days old

#===========================================================================
# Check if .environment variable is set to override percentage full threshold
#===========================================================================

if [[ $tspace_percent_full != "" ]] ; then
  pct=$tspace_percent_full
fi

if [[ $tspace_rpt_retain != "" ]] ; then
  rpt_retained=$tspace_rpt_retain
fi

#===========================================================================
# Connect to database
#===========================================================================
sqlcode=$(db2 +o -ec connect to $DATABASE)
if [[ $sqlcode != 0 ]]; then
  error_msg="Unable to connect to database $DATABASE - rc = $sqlcode"
  echo $error_msg
 
  if [ $rpt_flag = Y ] ; then 
    echo "$dateh" >  $rpt_out
    echo "Server:" $server"    Database:" $DATABASE >> $rpt_out
    echo " " >> $rpt_out
    echo $error_msg >> $rpt_out
    mail -s "DB2: tspace $server $DATABASE - tspace" $dft_mail_id < $rpt_out
  fi

  Fatal_Error
fi ; 

#===========================================================================
#
# Tablespace ID = 2
# Name = USERSPACE1
# Type = System managed space
# Contents = Any data
# State = 0x0000
# Detailed explanation:
# Normal
# Total pages = 561784
# Useable pages = 561784
# Used pages = 561784
# Free pages = Not applicable
# High water mark (pages) = Not applicable
# Page size (bytes) = 4096
# Extent size (pages) = 32
# Prefetch size (pages) = 32
# Number of containers = 1
# Minimum recovery time = 2001-02-09-22.28.15.000000
# 
#===========================================================================
#set -xv trace 

#===========================================================================
# Print Header 
#===========================================================================
echo $dateh  
echo "Server:" $server"       Database:" $DATABASE
echo ' ' 
echo "Tab                          Page             Num     Total      Used     Free  Percent"
echo ' Id TableSpace Name   Type   Size     State   Cont    Pages     Pages     Pages    Used'
echo '--- ---------------   ----   -----  --------  ----  ------- --------- --------- -------'

if [ $rpt_flag = Y ] ; then  
  echo $dateh > $rpt_out                  
  echo "Server:" $server"       Database:" $DATABASE >> $rpt_out
  echo ' '  >> $rpt_out
  echo "Tab                          Page             Num     Total      Used     Free  Percent" >> $rpt_out
  echo ' Id TableSpace Name   Type   Size     State   Cont    Pages     Pages     Pages    Used' >> $rpt_out
  echo '--- ---------------   ----   -----  --------  ----  ------- --------- --------- -------' >> $rpt_out
fi
 
#===========================================================================
# Issue list tablespace command and parse the output
#===========================================================================
db2 list tablespaces show detail | while read line  ; do
  w1=$(echo "$line" | cut -d" " -f1)

# If line is null then set it to something so test command does not fail
  if [ -z "$w1" ]; then
    unset w1
    w1='jnko'
  fi

  if [ $w1 =  "Tablespace" ]; then 
    tabid=$(echo "$line" | cut -d'=' -f2)
  fi

  if [ $w1 =  "Name" ]; then 
    name=$(echo "$line" | cut -d'=' -f2)
  fi

  if [ $w1 =  "Type" ]; then 
   type=$(echo "$line" | cut -d'=' -f2)    # Returns System managed space
   type1=$(echo "$type" | cut -c1-7)
     if [ $type1 = "System" ] ; then
      Ttype='SMS'
     else
      Ttype='DMS' 
     fi
  fi

  if [ $w1 = "State" ]; then
    state=$(echo "$line" | cut -d'=' -f2)
  fi

  if [ $w1 =  "Total" ]; then 
    total=$(echo "$line" | cut -d'=' -f2)
  fi

  if [ $w1 =  "Used" ]; then 
    used=$(echo "$line" | cut -d'=' -f2)
  fi

  if [ $w1 =  "Free" ]; then 
   free=$(echo "$line" | cut -d'=' -f2)
  fi

  if [ $w1 =  "Page" ]; then 
    page=$(echo "$line" | cut -d'=' -f2)
  fi

  #------------------------------------------------------------
  # "Number" is the last line returned for each tablespace
  # Calculate total number of pages for tablespace
  #------------------------------------------------------------
  if [ $w1 =  "Number" ]; then 
    cont=$(echo "$line" | cut -d'=' -f2)
    (( psize=$psize + ($page*$used) ))
    (( psizeK=$psize/1024 ))

    #-------------------------------------------------
    # Print information on tablespace to screen
    #-------------------------------------------------
    # If tablespace is SMS - reset variables to "-"
    if [ $Ttype = 'SMS' ]; then
      unset free ; free='-'
      unset used ; used='-'
      unset pcnt ; pcnt='-'
    else
    # Get percent used of database
      x=$(printf "%s\n" 'scale = 4; '$used'/'$total' * 100' | bc)
      pcnt=${x%??}  # Lob off last two zeroes
    fi

    #-------------------------------------------------
    # Check DMS tablespace for free space             
    #-------------------------------------------------
    if [ $Ttype = 'DMS' -a $rpt_flag = "Y" ]; then
      wnum=${pcnt%???}  # make percent a whole number
      if [ -z "$wnum" ]; then  # wnum may be 0.60
        wnum='0'     
      fi 
      if [ $wnum -gt "$pct" ]; then
        if ((thresh_counter == 0)) then
          echo "$dateh" >  $thresh_rpt_out
          echo "Server:" $server"    Database:" $DATABASE >> $thresh_rpt_out
          echo "Tablespaces over " $pct" pct full:"
          echo " " >> $thresh_rpt_out
        fi 
        ((thresh_counter = thresh_counter + 1)) 
        echo "Table ID: $tabid   Tablespace name: $name   Percent full: $pcnt" >> $thresh_rpt_out
      fi
    fi

    #-------------------------------------------------
    # Check tablespace state
    #-------------------------------------------------
    if [ $state != '0x0000' -a $rpt_flag = "Y" ]; then
        echo "$dateh" >  $err_rpt_out
        echo "Server:" $server"    Database:" $DATABASE >> $err_rpt_out
        echo " " >> $err_rpt_out
        echo "Table ID: $tabid   Tablespace name: $name   Bad tablespace state: $state" >> $err_rpt_out
        mail -s "DB2: tspace $server $DATABASE - tspace" $dft_email_id < $err_rpt_out
#       mail -s "DB2: tspace $server $DATABASE - tspace" $dft_page_id < $err_rpt_out
    fi 

    #-------------------------------------------------
    # Print out formatted line
    #-------------------------------------------------
    printf "%3s %-18s %3s %6s %10s %4s %9s %9s %9s %7s\n" $tabid $name $Ttype $page $state $cont $total $used $free $pcnt

    if [ $rpt_flag = 'Y' ]; then   # Write output to file
      printf "%3s %-18s %3s %6s %10s %4s %9s %9s %9s %7s\n" $tabid $name $Ttype $page $state $cont $total $used $free $pcnt >> $rpt_out
    fi

  fi    # if [ $w1 =  "Number" ]

done    # db2 list tablespaces show detail | while read line

#---------------------------------------------------------------------------
# Print out size of database
#---------------------------------------------------------------------------
echo ' '
echo '   Database size in bytes  '$psize
echo '   Database size in K:  '$psizeK

if [ $rpt_flag = 'Y' ]; then  
  echo ' ' >> $rpt_out
  echo '   Database size in bytes  '$psize >> $rpt_out
  echo '   Database size in K:  '$psizeK >> $rpt_out
fi 

#===========================================================================
# Remove reports older than $rpt_retained
#===========================================================================
if [ $rpt_flag = 'Y' ]; then   
  echo " " >> $rpt_out
  echo "List and remove report files (from disk) over $rpt_retained days old" >> $rpt_out
  find $rpt_dir -name "tspace.$server.$DATABASE.d*" -mtime +$rpt_retained -type f -exec ls {} \; >> $rpt_out
  find $rpt_dir -name "tspace.$server.$DATABASE.d*" -mtime +$rpt_retained -type f -exec rm {} \;
  chmod 660 $rpt_out
fi 

#===========================================================================
# If rpt_flag = 'Y' and thresh_counter is greater than 0 send a report of tablespaces with thresholds exceeded
#===========================================================================
if [ $rpt_flag = 'Y' ]; then
  if ((thresh_counter > 0)) then
        mail -s "DB2: tspace $server $DATABASE - tspace" $dft_email_id < $thresh_rpt_out
#       mail -s "DB2: tspace $server $DATABASE - tspace" $page_id < $thresh_rpt_out
  fi
fi