Need to implement new CRON job or modify the existing one in MDM PROD in coming days, which will send email of SQL statement running from past 1 minute.Currently there is one CRON job in PROD, which send below email.
there is one cron job which send email, when there is long running process....but its does not send sql information....
Required details.
1) Long_Running_Processes.ksh<DB2 Instance Name>
<Database Name>MDM
Websphere ESB's
NetQuotesVaries
Every 5/10minsIf a transaction has been executing against the specified database for longer than 60 seconds then an email is sent to the ASWTDBA mailbox.
2) CRON Job details�
ukncsaviv724:uklasdb2:/home/apps/admin/utils/uklasdb2/bin $ cat Long_Running_Processes.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 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
ukncsaviv724:uklasdb2:/home/apps/admin/utils/uklasdb2/bin $ Reply Forward