#!/bin/bash
#< Check Sybase DB Capacity
# Description:
#   Get some basic DB capacity information for our DB devices
#   Very basic script - no error checking - assumes a lot
#
# Exit codes:
#   0 - Always
#
# History:
#   01/05/06 - KWALDRON - Initial Version - 0.1


CMD_OUT=$(isql -Usa -Ppasswd -SFOODB -w200 <<EoF
sp_helpdb foodb
go
EoF
)

# This routine relies on fairly rigid formatting of the
# sp_helpdb meldb output
echo "${CMD_OUT}" | awk '{
  if ( $0 ~ /^ name/ ) {
    getline
    getline
    db_name=$1
    printf( "--> Database Capacity Report For [%s]\n", db_name );
  }
  if ( $0 ~ /^ device_fragments/ ) {
     getline
     getline
     while ( $0 !~ /^[         ]*$/ ) {
        if ( $4 !~ /^log$/ ) {
          free_space=$10 / 1024
          perc_avail=( free_space / $2 ) * 100
          printf( "<-- Device Fragment [%s]\tFragment Size [%5dMB]\tFree Space on Device [%5dMB]\tPercent Avail. [%0.2f%%]\n",
                  $1, $2, free_space, perc_avail );
          total_fragsize+=$2
          total_free+=free_space
        } else {
          log_dev=$1
          frag_size=$2
          total_fragsize+=$2
        }
        getline
     }
  }
  if ( $0 ~ /^ log only free/ ) {
     log_free=$NF / 1024
     total_free+=log_free
  }
}
END {
  printf( "<-- Device Fragment [%s]\tFragment Size [%5dMB]\tFree Log Space       [%5dMB]\tPercent Avail. [%0.2f%%]\n",
          log_dev, frag_size, log_free, ( log_free / frag_size ) * 100 );
  printf( "--> Overall DB Usage\n" )
  printf( "<-- Database [%s]\t\tTotal DB Size [%5dMB]\tTotal Free Space     [%5dMB]\tPercent Avail. [%0.2f%%]\n",
           db_name, total_fragsize, total_free, ( total_free / total_fragsize ) * 100 );
}'

exit 0