Cover V06, I02
Article
Listing 1
Listing 2
Listing 3

feb97.tar


Listing 2: Database space script

#!/bin/sh

#################################################################
# Database Space Script:
#    /oem/sun/drm/ds_scripts/check.database.space
#
# This script will check the space of all databases on all Sybase
# servers named SUNSYB## in the interfaces file except for Sybase
# system databases.
#################################################################

SCRIPTHOME="/oem/sun/drm/ds_scripts"
export SCRIPTHOME

HDIR="/mounts/lrd01/data/sybadm"      # Work and Report directory
export HDIR

DATE=`date +%m%d%y`.`date +%I%M%p`
export DATE

LOG="$HDIR/check.database.space.$DATE"     # Database space report
export LOG

# Clean up reports older than 5 days

find $HDIR -name check.database.space\* -mtime +5 -print -exec \
rm -f {} \;

# Clean up any temporary work files that may still exist

if [ -f $HDIR/check.database.space.*.temp ]
then
rm $HDIR/check.database.space.*.temp
fi

# Report Headers

date +"DATE: %D"                    >  $LOG.temp
date +"TIME: %T"                    >> $LOG.temp
echo "                  DATABASE SPACE REPORT \n \n"    >> $LOG.temp

echo "*******************************************" >> $LOG.temp
echo "******************************************* \n \n" >> $LOG.temp

echo "System databases are excluded from this report: \n" >> $LOG.temp

echo "          master"                 >> $LOG.temp
echo "          model"                  >> $LOG.temp
echo "          pubs2"                  >> $LOG.temp
echo "          sybsecurity"            >> $LOG.temp
echo "          sybsystemprocs"         >> $LOG.temp
echo "          tempdb \n \n"           >> $LOG.temp

echo "*******************************************" >> $LOG.temp
echo "******************************************* \n \n" >> $LOG.temp

# Set up Sybase environment variables.   The following variables are
# contained in the /etc/skel/.profile.sybase.path:
#    SYBNO="07"
#    DSQUERY=SUNSYB07
#    PATH=/dbs/sybase/sybase.11.0.1/bin:$PATH
#    SYBASE=/dbs/sybase/sybase.11.0.1
#    export PATH SYBASE DSQUERY SYBNO


. /etc/skel/.profile.sybase.path

# Read Sybase interfaces file and extract Sybase server name and
# corresponding UNIX hostname.   The result is a list of Sybase
# SQL Servers and UNIX Servers, i.e.:
#    SUNSYB07            dspu005

grep "^## SUNSYB" $SYBASE/interfaces | \
cut -f2,4 -d" " > $HDIR/check.database.space.server.list.temp

exec < $HDIR/check.database.space.server.list.temp

COUNTER1="0"

while read sybase unix
do

if [ -n "$sybase" ]
then

COUNTER1=`expr $COUNTER1 + 1`

eval SYB$COUNTER1="$sybase"
export eval SYB$COUNTER1

eval UNIX$COUNTER1="$unix"
export eval UNIX$COUNTER1

fi

done

until [ "$COUNTER1" = 0 ]
do

UNIXNAME=`eval echo UNIX$COUNTER1 | sed 's/^/$/'`
eval echo $UNIXNAME
export UNIXNAME

SYBNAME=`eval echo SYB$COUNTER1 | sed 's/^/$/'`
eval echo $SYBNAME
export SYBNAME

# Database space report break on each UNIX Server name change

echo "\n************************************** \n" >> $LOG.temp
eval echo "UNIX Server: $UNIXNAME"                 >> $LOG.temp
echo "\n************************************** \n" >> $LOG.temp

# Create and execute the SQL to list all databases on each system.

echo "sp_helpdb"    >  $HDIR/check.database.space.sql.temp
echo "go"        >> $HDIR/check.database.space.sql.temp

cat /home/sybadm01/sajobs | isql -Usa -S`eval echo $SYBNAME` \
-i$HDIR/check.database.space.sql.temp \
-o$HDIR/check.database.space.list.temp

# Edit out all extraneous information except database id.   The
# result is a list of all databases that exist on that Sybase
# SQL Server.

cat $HDIR/check.database.space.list.temp | \
cut -f1 | \
awk '$1 !~ /name/ && $1 !~ /-/ && $1 !~ /\(/ { print $1 }' | \
sed "/^$/d" > $HDIR/check.database.space.list.ed.temp

exec < $HDIR/check.database.space.list.ed.temp

while read DB
do

export DB

# Create and execute the SQL to list the space allocations for
# each database on this Sybase SQL Server.   All of the Sybase
# system databases are excluded from these queries.

case "$DB"
in

master|model|pubs2|sybsecurity|sybsystemprocs|tempdb) ;;

*)

echo "use $DB"        >  $HDIR/check.database.space.sql.temp
echo "go"             >> $HDIR/check.database.space.sql.temp
echo "sp_helpdb $DB"  >> $HDIR/check.database.space.sql.temp
echo "go"             >> $HDIR/check.database.space.sql.temp

cat /home/sybadm01/sajobs | \
isql -Usa -S`eval echo $SYBNAME` \
-i$HDIR/check.database.space.sql.temp >> $LOG.temp

;;

esac

done

COUNTER1=`expr $COUNTER1 - 1`

done

# Edit Database space report and remove all extraneous
# Sybase messages.

cat $LOG.temp                | \
sed "/^Password/d"           | \
sed "/^Msg 916/d"            | \
sed "/^Line/d"               | \
sed "/return status = 0/d"   | \
sed "/^Server/d"         > $LOG

# Add an end-of-report statement to report.


echo "*******************************************" >> $LOG.temp
echo "******************************************* \n \n" >> $LOG.temp

echo "                                 END OF REPORT \n" >> $LOG

echo "*******************************************" >> $LOG.temp
echo "******************************************* \n \n" >> $LOG.temp

# Change permissions on reports so users can access them on UNIX.

chmod 555 $LOG

# Print Database space report.   This report can also be
# mailed to the appropriate users.

lp -dprt009 $LOG

# Delete all temporary work files.

if [ -f $HDIR/check.database.space.*.temp ]
then
rm $HDIR/check.database.space.*.temp
fi

# End of File