Shell Programming and Scripting

#!/bin/ksh
 
thisFile="$(whence ${0})"
thisDir="${thisFile%/*}"
 
# Environment Setup
. ${thisDir}/../lib/env.ksh
 
# Include Common Functions
. ${thisDir}/../lib/func.ksh
 
# Initialise Logging
LOGFILE=`LogFileName ${1}.${2}`
exec 2>&1
exec 1> ${LOGFILE}
 
# Output a HEADER section to the logfile
LogHeader
 
 
# Two parameters expected. INSTANCENAME, DATABASENAME
if [ $# -ne 2 ]
then
        echo "Usage: `basename $0` <Instance Name> <DB Name>"
        LogFooter
        exit 1
fi
 
# Set some variables for the parameters passed to this script
INSTANCE=${1}
DBNAME=${2}
 
# Check if this DB2 instance is running on this server
RUNNING=`CheckRunningInstance ${INSTANCE}`
if [ ${RUNNING} -eq 1 ]
then
        echo "Instance: ${INSTANCE} is not running on this server"
        LogFooter
        exit 1
fi
 
# Set Env for DB2 instance
SetDB2Env ${INSTANCE}
 
# Connect to DB2 database
ConnectDB2 ${DBNAME}
 
# Run SQL to check if any processes have been running for longer than the specified number of seconds
db2 -x "SELECT  SNAPSHOT_TIMESTAMP, AGENT_ID, APPL_STATUS, CLIENT_PID, APPL_NAME, APPL_ID, AUTH_ID, \
                CLIENT_NNAME, EXECUTION_ID, \
                TIMESTAMPDIFF(2,char(current timestamp - STATUS_CHANGE_TIME)) WAIT_TIME_SECONDS \
        FROM TABLE( SNAPSHOT_APPL_INFO( '${DBNAME}', -1 )) as SNAPSHOT_APPL_INFO \
        WHERE APPL_STATUS = 3 \
        AND   TIMESTAMPDIFF(2,char(current timestamp - STATUS_CHANGE_TIME)) > 60" > ${TEMPDIR}/$$.tmp1
 
if [ $? -ne 0 -a $? -ne 1 ]
then
        echo "Error selecting long running process information"
        LogFooter
        exit 1
fi
 
# Flag file to check if an email has already been sent
EMAILCHECKFILE=${ETCDIR}/Email_LongRunningProcess.${INSTANCE}.${DBNAME}.chk
 
# Any long running processes?
LONG=`wc -l ${TEMPDIR}/$$.tmp1 | awk '{print $1;}'`
echo "LONGRUNNINGCOUNT = ${LONG}"
if [ ${LONG} -gt 0 ]
then
        echo "Long Running processes found. Starting monitoring."
        cp ${TEMPDIR}/$$.tmp1 ${DATADIR}/snapshotapplinfoLR.${INSTANCE}.${DBNAME}.${DATESTAMP}.${TIMESTAMP}.txt
 
        # Get a snapshot of the database locks
        SNAPSHOTFORLOCKSFILE=${DATADIR}/snapshotforlocksLR.${INSTANCE}.${DBNAME}.${DATESTAMP}.${TIMESTAMP}.txt
        db2 -x "get snapshot for locks on ${DBNAME}" > ${SNAPSHOTFORLOCKSFILE}
 
        # Check if email has been sent already the last time this ran
        if [ ! -f ${EMAILCHECKFILE} ]
        then
                # Write info to a file containing the body of the email to be sent
                echo "\
${DATESTAMP} ${TIMESTAMP} - Long Running processes found. \
Check the data files in ${DATADIR} on `hostname` for details." > ${TEMPDIR}/$$.tmp2
                echo "\
The processes below have been running for LONGER than 60 seconds." >> ${TEMPDIR}/$$.tmp2
                echo "" >> ${TEMPDIR}/$$.tmp2
                cat ${TEMPDIR}/$$.tmp1 >> ${TEMPDIR}/$$.tmp2
                echo "" >> ${TEMPDIR}/$$.tmp2
                cat ${SNAPSHOTFORLOCKSFILE} >> ${TEMPDIR}/$$.tmp2
 
                # Send an email
                cat ${TEMPDIR}/$$.tmp2 | mailx -s "${INSTANCE} ${DBNAME} has long running processes." ${EMAILALERTS}
                touch ${EMAILCHECKFILE}
        fi
 
        # Run script to collect all relevant diagnostic information
        ${BINDIR}/Data_Collection.ksh ${INSTANCE} ${DBNAME}
else
        # Remove the file that is used to check that en email has been sent
        rm -f ${EMAILCHECKFILE}
fi
 
 
# Tidy up temp files
rm -f ${TEMPDIR}/$$.*
 
# Output Footer section to logfile
LogFooter
 
ukncsaviv724:uklasdb2:/home/apps/admin/utils/uklasdb2/bin $
 
 
ukncsaviv724:uklasdb2:/home/apps/admin/utils/uklasdb2/bin $ cat Data_Collection.ksh
#!/bin/ksh
 
thisFile="$(whence ${0})"
thisDir="${thisFile%/*}"
 
# Environment Setup
. ${thisDir}/../lib/env.ksh
 
# Include Common Functions
. ${thisDir}/../lib/func.ksh
 
# Initialise Logging
LOGFILE=`LogFileName ${1}.${2}`
exec 2>&1
exec 1> ${LOGFILE}
 
# Output a HEADER section to the logfile
LogHeader
 
 
# Two parameters expected. INSTANCENAME, DATABASENAME
if [ $# -ne 2 ]
then
        echo "Usage: `basename $0` <Instance Name> <DB Name>"
        LogFooter
        exit 1
fi
 
# Set some variables for the parameters passed to this script
INSTANCE=${1}
DBNAME=${2}
 
# Check if the DB2 instance is running on this server
RUNNING=`CheckRunningInstance ${INSTANCE}`
if [ ${RUNNING} -eq 1 ]
then
        echo "Instance: ${INSTANCE} is not running on this server"
        LogFooter
        exit 1
fi
 
# Set Env for DB2 instance
SetDB2Env ${INSTANCE}
 
# Connect to DB2 database
ConnectDB2 ${DBNAME}
 
# Loop through each of the snapshot SQL files
for sqlfile in ${ETCDIR}/snapshot*.sql
do
        # Generate an output filename (Remove the .sql from the file)
        STRIPPEDsqlfile=`basename ${sqlfile}`
        OUTFILE=${DATADIR}/${STRIPPEDsqlfile}.${INSTANCE}.${DBNAME}.${DATESTAMP}.csv
        # A couple of temp files and sql files to hold the query and also the data as it is collected
        TEMPFILE1=${TEMPDIR}/$$.tmp1
        TEMPFILE2=${TEMPDIR}/$$.tmp2
        SQLFILE1=${TEMPDIR}/$$.sql1
        SQLFILE2=${TEMPDIR}/$$.sql2
 
        echo "EXPORT TO ${TEMPFILE1} OF DEL" > ${SQLFILE1}
        cat ${SQLFILE1} ${sqlfile} | sed "s/<<DBNAME>>/${DBNAME}/g" > ${SQLFILE2}
        db2 -t -f ${SQLFILE2}
 
        if [ -f ${OUTFILE} ]
        then
                cat ${OUTFILE} ${TEMPFILE1} > ${TEMPFILE2}
                mv -f ${TEMPFILE2} ${OUTFILE}
        else
                mv -f ${TEMPFILE1} ${OUTFILE}
        fi
done
 
# Tidy up any temp files
rm -f ${TEMPDIR}/$$.*
 
# Output Footer section to logfile
LogFooter