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
|