The script below is used to generate and then run Database housekeeping for the specified databases.

It is a good way to ensure housekeeping is run regulary for smaller databases.

This has been tested on Ingres 2.6/Solaris.

Note – the sysmod will fail if there are sessions attached to the database. I have another script that can be run to remove sessions from a database.

The email address at the end of the script should be changed to email the log file to the appropriate DBA for checking.

#!/bin/ksh
############################################################################
#
# do_db_housekeeping.ksh
#
# Script to create and run housekeeping scripts for ingres databases
#
# Description: This script will use copydb to generate housekeeping script
# and then run against database. Also perform optimizedb
# and attempt a sysmod.
# Good for smaller databases.
# Usage: do_db_houskeeping.ksh …
#
############################################################################
#
# Set local vars
II_SYSTEM=/ingres
LD_LIBRARY_PATH=/usr/lib:/ingres/ingres/lib
PATH=$II_SYSTEM/ingres/bin:$II_SYSTEM/ingres/utility:$PATH
export II_SYSTEM LD_LIBRARY_PATH PATH
LOG_DIR=/tmp
LOG_FILE=$LOG_DIR/do_db_housekeeping.$$
#
# Write a output to log file
#
if [[ $LOGNAME != “ingres” ]]
then
echo ‘ERROR: Must be run as user ingres’
exit
fi
if [[ -z $1 ]]
then
echo ‘ERROR – Usage: do_copyckp_to_remote.ksh ‘
exit
fi
cd $HOME
#
# For each database generate and run housekeeping
#
echo “Starting do_db_houskeeping at `date`” > $LOG_FILE
for arg; do

# Find DBA for database
DATABASE=$arg
DBA=`infodb $DATABASE | grep “Database :” | sed -e ‘s#,# #’ | sed -e ‘s#)# #’ | awk ‘{ print $4 }’`

echo “Generating script for $DATABASE database with a dba of $DBA” >> $LOG_FILE

copydb $DATABASE -u$DBA -with_modify -with_index -relpath -no_loc -infile=$DATABASE.sql >> $LOG_FILE

# Now run housekeeping script
sql $DATABASE -u$DBA < $DATABASE.sql >> $LOG_FILE

# Now run a optimizedb -zk
optimizedb -u$DBA -zk $DATABASE >> $LOG_FILE

# Now run a sysmod
sysmod $DATABASE >> $LOG_FILE

done
echo “do_db_housekeeping.ksh completed at `date` ” >> $LOG_FILE
#
mailx -s “do_db_housekeeping.ksh log” yourdba@youremail < $LOG_FILE #