#!/bin/ksh
# mon_stmt.ksh
# Raanon Reutlinger, IBM Israel, May 2000

display_syntax()
{
echo "\
SYNTAX: `basename $0` -c \"comment\" -d dbname -m mon_name [-o N] [-f]
      [-s N] [-sql] [-i Q] [-p] [-ps] [-q] [-r] [-b]
Summerize STATEMENT statistics captured from an Event Monitor.
-c:   Comment placed on first line of Summary output file (required)
-d:   Database name (required)
-m:   Monitor name (required)
-o:   Save output in dir N under \$RESULTS
(0=Current dir; -1=Not Saved,default)
-f:   Save each SQL stmt to a different file in the \$QUERIES dir
-s:   Display N characters of SQL stmt (-1=all, default)
-sql: Only display SQL statements, without statistics
-i:   Include info in output, where Q could contain any of the following
       S=Start-Time ; O=Operation ; T=Timing ; R=Row-Counts; X=Sort-Info
       N=None_of_the_above (default=SORTX)
-p:   mon_name is a PIPE; Don't save the summary to an output file.
-ps:  mon_name is a PIPE; Save the summary to an output file.
       Summary output is only visible when the event monitor closes.
-q:   Quiet (default is to display output)
-r:   Don't read the monitor files, reuse existing output (extraction)
-b:   DONT save prior (old) results to bak directory (default=save)
(Not saved anyway unless -o is greater than 0)
Notes:
Value for -d is part of monitor output filename.
Values for -s, -f, -sql and -i are part of Summary output filename.
Pipes: Make sure to run this utility BEFORE starting the event monitor
       (prompts given).  For -ps only flushed summary output is
               visible in real-time (instructions given). So for real-time
       viewing, use -p, which doesn't save summary to a file.
In most cases, best viewed in 132 column window.
"
}

# Constants

QUERIES=~/queries
RESULTS=~/results
RESULTS_FILE="`basename $0 .ksh`"
RES_EXT=".out"
SUM_EXT=".sum"
AWKSCRIPT="`dirname $0`/`basename $0 .ksh`.awk"

# Defaults

QUIET=0
DISPLAY_SQL=-1
SAVE_SQL=0
SQL_ONLY=0
RESULTS_DIR=-1# -1 defaults to not saved
REUSE_OUT=0
SAVE_OLD_RESULTS=1
PARSE_ERROR=""
PARAMS=$*

INCLUDE_OPT="SORTX"
PIPE=0
PIPE_SAVE=0

# Parse parameters

while [ "$1" != "" ]
do
case "$1" in
"-c") shift; COMMENT=$1; shift;;
"-d") shift; DB_NAME=$1; shift;;
"-m") shift; MON_NAME=$1; shift;;
"-o") shift; RESULTS_DIR=$1; shift;;
"-f") shift; SAVE_SQL=1;;
"-s") shift; DISPLAY_SQL=$1; shift;;
"-sql") shift; SQL_ONLY=1;;
"-i") shift; INCLUDE_OPT=$1; shift;;
"-p") shift; PIPE=1;;
"-ps") shift; PIPE=1; PIPE_SAVE=1;;
"-q") shift; QUIET=1;;
"-r") shift; REUSE_OUT=1;;
"-b") shift; SAVE_OLD_RESULTS=0;;
*) shift; PARSE_ERROR="Invalid Param";;
esac
done

# Verify parameters

[ "$COMMENT" = "" ] && \
PARSE_ERROR="${PARSE_ERROR} -Comment is required"

[ "$DB_NAME" = "" ] && \
PARSE_ERROR="${PARSE_ERROR} -Database name is required"

[ "$MON_NAME" = "" ] && \
PARSE_ERROR="${PARSE_ERROR} -Monitor Name is required"

[ $DISPLAY_SQL -ge -1 ] 2>/dev/null || \
PARSE_ERROR="${PARSE_ERROR} -Invalid number following -s param"

[ $RESULTS_DIR -ge -1 ] 2>/dev/null || \
PARSE_ERROR="${PARSE_ERROR} -Invalid number following -o param"

echo "$PARAMS" | awk '/-sql/ && /-i/{exit -1}' || \
PARSE_ERROR="${PARSE_ERROR} -Cant combine -sql with -i"

echo "$PARAMS" | awk '/-p[ $]/ && /-ps/{exit -1}' || \
PARSE_ERROR="${PARSE_ERROR} -Cant combine -ps with -p"

echo "$PARAMS" | awk '/-p[ $]/ && /-q/{exit -1}' || \
PARSE_ERROR="${PARSE_ERROR} -Cant combine -q with -p (only with -ps)"

echo "$PARAMS" | awk '/-o -1/ && /-r/{exit -1}' || \
PARSE_ERROR="${PARSE_ERROR} -Cant combine -r with -o -1"

echo "$PARAMS" | awk '/-o -1/ && /-q/{exit -1}' || \
PARSE_ERROR="${PARSE_ERROR} -Cant combine -q with -o -1"

[ $SQL_ONLY -eq 1 ] && INCLUDE_OPT="N"

echo "$INCLUDE_OPT" | awk '/[^SORTX]/ && ($0 != "N"){exit -1}' || \
PARSE_ERROR="${PARSE_ERROR} -Invalid value for -i param"

if [ "$PARSE_ERROR" != "" ]
then
echo ""
echo $PARSE_ERROR
echo ""
display_syntax
exit
fi

if [ $SAVE_SQL -eq 1 ]
then
# Get last query file number
mkdir $QUERIES 2>/dev/null
LAST_QUERY=`ls $QUERIES | sort -n | tail -1`
LAST_QUERY=`basename $LAST_QUERY .sql`
fi

RESULTS_FILE=`echo $MON_NAME | tr [a-z] [A-Z]`
DB_NAME=`echo $DB_NAME | tr [a-z] [A-Z]`

RES_EXT="_${DB_NAME}${RES_EXT}"
SUM_EXT="_${DB_NAME}_${DISPLAY_SQL}${SAVE_SQL}${SQL_ONLY}${INCLUDE_OPT}${SUM_EXT}"

if [ $RESULTS_DIR -gt 0 ]
then
RES_OUT=${RESULTS}/${RESULTS_DIR}/${RESULTS_FILE}${RES_EXT}
SUM_OUT=${RESULTS}/${RESULTS_DIR}/${RESULTS_FILE}${SUM_EXT}
RES_BAK=${RESULTS}/${RESULTS_DIR}/bak/${RESULTS_FILE}${RES_EXT}.$$
SUM_BAK=${RESULTS}/${RESULTS_DIR}/bak/${RESULTS_FILE}${SUM_EXT}.$$
else
RES_OUT=${RESULTS_FILE}${RES_EXT}
SUM_OUT=${RESULTS_FILE}${SUM_EXT}
fi

if [ $REUSE_OUT -eq 1 -a ! -f $RES_OUT ]
then
echo "Can't reuse $RES_OUT - Missing"
exit
fi

if [ $RESULTS_DIR -gt 0 ]
then
mkdir $RESULTS 2>/dev/null
mkdir $RESULTS/$RESULTS_DIR 2>/dev/null
if [ $SAVE_OLD_RESULTS -eq 1 ]
then
mkdir $RESULTS/$RESULTS_DIR/bak2>/dev/null
[ $REUSE_OUT -eq 0 ] && \
cp $RES_OUT $RES_BAK 2>/dev/null && echo "[Created: $RES_BAK]"
cp $SUM_OUT $SUM_BAK 2>/dev/null && echo "[Created: $SUM_BAK]"
fi
fi

TMP_SQL="${QUERIES}/sql.$$.tmp"

# Clean up previous aborts (trap didn't work :(
rm ${QUERIES}/sql.[0-9]*.tmp 2>/dev/null

export QUERIES DISPLAY_SQL SAVE_SQL SQL_ONLY LAST_QUERY TMP_SQL
export INCLUDE_OPT

# BEGIN .......

[ $QUIET -eq 1 ] && Q_OUTPUT=">> $SUM_OUT" || Q_OUTPUT="| tee -a $SUM_OUT"
rm $SUM_OUT 2>/dev/null

if [ $RESULTS_DIR -ge 0 ]
then
R_OUTPUT="| tee $RES_OUT"

# with -p, Summary output not saved
if [ $PIPE -eq 1 -a $PIPE_SAVE -eq 0 ]
then
Q_OUTPUT=""
echo "[Summary Output Not Saved]"
else
echo "[Creating: $SUM_OUT]"
fi
else
R_OUTPUT=""
Q_OUTPUT=""
echo "[No Output Saved]"
fi

eval echo "-- $COMMENT"$Q_OUTPUT
eval echo "-- ----------------------------------------"$Q_OUTPUT
eval echo "-- Invocation: $0 $PARAMS"$Q_OUTPUT
eval echo "--             `date`"$Q_OUTPUT
eval echo "-- "$Q_OUTPUT

if [ $PIPE -eq 0 -o $REUSE_OUT -eq 1 ]
then
if [ $RESULTS_DIR -eq -1 ]
then
echo db2evmon $DB_NAME $MON_NAME
echo ""
db2evmon $DB_NAME $MON_NAME | awk -f $AWKSCRIPT
else
if [ $REUSE_OUT -eq 0 ]
then
eval echo db2evmon $DB_NAME $MON_NAME$Q_OUTPUT
echo ""
echo "[Creating: $RES_OUT]"
db2evmon $DB_NAME $MON_NAME > $RES_OUT
else
echo ""
echo "[Reusing: $RES_OUT]"
fi

echo ""
eval awk -f $AWKSCRIPT $RES_OUT $Q_OUTPUT
fi
else
echo "\
--------------------------------------------------------------------
Start the event monitor to begin summarizing (filtering) as follows:
  Use: db2 SET EVENT MONITOR $MON_NAME STATE 1
--------------------------------------------------------------------"
#  or: . `dirname $0`/mon_state.ksh $MON_NAME 1
if [ $PIPE_SAVE -eq 0 ]
then
# Gives better real-time display without pipe after awk
echo ""
echo db2evmon $DB_NAME $MON_NAME
echo ""
eval db2evmon $DB_NAME $MON_NAME $R_OUTPUT | awk -f $AWKSCRIPT
else
echo "\
Only when all pipe buffers are flushed will be output be visible.
To flush event monitor records manually
  Use: db2 FLUSH EVENT MONITOR $MON_NAME BUFFER
Closing the Event Monitor also flushes all records.
  Use: db2 SET EVENT MONITOR $MON_NAME STATE 0
--------------------------------------------------------------------"
#  or: . `dirname $0`/mon_state.ksh $MON_NAME 0
eval echo ""$Q_OUTPUT
eval echo db2evmon $DB_NAME $MON_NAME $Q_OUTPUT
eval echo ""$Q_OUTPUT
[ "$R_OUTPUT" != "" ] && echo "[Creating: $RES_OUT]"
eval db2evmon $DB_NAME $MON_NAME $R_OUTPUT | \
eval awk -f $AWKSCRIPT$Q_OUTPUT
fi
fi