#!/bin/ksh

###########################################
#Author: hongyan_huo@persistent.com
#
#Prerequisites: 
# 1. copy this script to the same directory that contains db2mon.sh, user to execute the script must have rw permission to this dir
# 2. db2 EXPLAIN tables must be created. if not yet, run the following commands as db2 administrator
#           db2 connect to <dbname>
#           db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))"
# 3. test db2mon and make sure it runs properly alone: ./db2mon.sh <dbname> > report.out. Look for errors in report.out and fix them
#
#Usage:
# issue command as db2 administrator: ./db2mon_auto.sh <dbname> <number_of_db2monCalls>
# for example, to run db2mon.sh 5 times against database RMDB: ./db2mon_auto.sh RMDB 5
#       number_of_db2monCalls: try to start a value between 3 and 10
#
#Output files will be generated under a newly created directory called report_db2mon<datetime>
###########################################



#ToDo: add syntax check from command line"

echo "==========================start to collect db2mon data===================================="
echo "db2mon started at: "`date`
dirname="report_db2mon"`date +%Y%m%d_%H%M%S`
mkdir $dirname
filename="report_db2mon"`date +%Y%m%d_%H%M%S`".out"

x=0
while [ $x -lt $2 ]
do
  #echo `date` >> $dirname/$filename
  ./db2mon.sh $1 >> $dirname/$filename
  x=$(( $x + 1 ))
  sleep 5
done
echo "db2mon ended at: "`date`

cd $dirname

############################################
##First part, parse Currently executing SQLs. Queries in these section represent long running queries for which the results
## may have yet returned. These queries are often not easy to get in db2top or queryStats because the ongoing execution time is counted as 0
## in these tools.  
echo "==========================start to process db2mon data===================================="
awk 'BEGIN { format = "%68s %s\n"
             printf format, "EXECUTABLE_ID", "ELAPSED_TIME_SEC"
             printf format, "-------------------------------------------------------------------", "----------------" }
     {i=(1+(i%4)); if (buf[i]&& $0 ~ "^ELAPSED_TIME_SEC"){split(buf[i], a, " "); 
                                                          printf format, a[2], $2 }; buf[i]=$0}' $filename >> tmpfile_db2mon.csv
sleep 1

#Construct an array to store unique executable_id - the one with the max elapsed time captured,
# then test if its elapsed_time equals or exceeds 3 seconds.
awk '{if(elapsedt[$1]<$2)elapsedt[$1]=$2} END{for (id in elapsedt) {if (elapsedt[id]+1>=4){print id}}}' tmpfile_db2mon.csv >> tmpfile2_db2mon.csv

#Process each executable_id to generate its explain plan and the binding variable values
if [ -f "tmpfile2_db2mon.csv" ]; then
    echo "======================start to generate explain plans and parameter files for long running queries======================="
    set -A arr
    i=0
    db2 connect to $1
    while IFS= read -r line || [ -n "$line" ]; do
       arr[i]="$line"
       echo ${arr[$i]}
       db2 "CALL EXPLAIN_FROM_SECTION(${arr[$i]}, 'M', NULL, 0, NULL, ?, ?, ?, ?, ?)"
       filename1=`echo ${arr[$i]} | cut -d"'" -f 2`".expln"
       db2exfmt -d $1 -1 -o $filename1

       filename1=`echo ${arr[$i]} | cut -d"'" -f 2`".txt"
       db2 "export to $filename1 of del messages genparam.msg select XMLPARSE(DOCUMENT max_coord_stmt_exec_time_args) FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,${arr[$i]},NULL,-1))"
       i=$((i+1))
    done < tmpfile2_db2mon.csv
    db2 disconnect $1
fi

echo "======================Potential long running queries captured in this report===========================" >> $filename
cat tmpfile_db2mon.csv >> $filename
rm -rf tmpfile2_db2mon.csv
rm -rf tmpfile_db2mon.csv

########################################
##Second part: parsing information from Top SQLs section 
echo "=============start to generate explain plans and parameter files for top SQLs in package cache=================" 
awk '/^ Top SQL statements by execution time /{for(i=1;i<=11;i++){getline; if(i>4) print $2, $3, $4}}' $filename >> tmpfile3_db2mon.csv
echo "EXECUTABLE_ID                                        " >> tmpfile4_db2mon.csv
echo "-----------------------------------------------------" >> tmpfile4_db2mon.csv
awk '/^ Statement & plan identifiers - top statements by execution time/{for(i=1;i<=14;i++){getline; if(i>9) print $2}}' $filename >> tmpfile4_db2mon.csv

paste tmpfile4_db2mon.csv tmpfile3_db2mon.csv >> tmpfile_db2mon.csv

if [ -f "tmpfile_db2mon.csv" ]; then
    echo "======================start to generate explain plans and parameter files for top SQLs======================="
    set -A arr
    i=0
    db2 connect to $1 
    while IFS= read -r line || [ -n "$line" ]; do
       arr[i]="$line"
       #echo ${arr[$i]}
       if [ "$i" -gt "1" ]; then
          echo $i
          filename1=`echo ${arr[$i]} | cut -d"'" -f 2`".expln"
          #some queries may already be processed in part 1, if so then skip them
          if [ ! -f $filename1 ] ; then
             exec_id=`echo ${arr[$i]} | cut -d" " -f 1`
             db2 "CALL EXPLAIN_FROM_SECTION($exec_id, 'M', NULL, 0, NULL, ?, ?, ?, ?, ?)"
             db2exfmt -d $1 -1 -o $filename1

             filename1=`echo ${arr[$i]} | cut -d"'" -f 2`".txt"
             db2 "export to $filename1 of del messages genparam.msg select XMLPARSE(DOCUMENT max_coord_stmt_exec_time_args) FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,$exec_id,NULL,-1))"
          fi
       fi
       i=$((i+1))
    done < tmpfile_db2mon.csv
    db2 disconnect $1
fi

echo "" >> $filename
echo "===========================Top 5 SQLs captured from package cache in this report===========================" >> $filename
cat tmpfile_db2mon.csv >> $filename
rm -rf tmpfile*csv