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

display_syntax()
{
echo "\
SYNTAX: `basename $0` -c \"comment\" -d dbname [-o N] [-f] [-s N] [-sql]
      [-t] [-w] [-q] [-r] [-b]
Summerize the Statement statistics captured by the Dynamic SQL Snapshot.
-c:   Comment placed on first line of Summary output file (required)
-d:   Database 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
-t:   DONT display Timing stats
-w:   DONT display Row Count stats
-q:   Quiet (default is to display output)
-r:   Don't get snapshot, reuse existing snapshot output file
-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 snapshot output filename.
Values for -d, -s, -f and -sql are part of Summary output filename.
Timing and Row Count statistics won't be shown if \"Not Collected\".
In most cases, best viewed in 80 or 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=$*

NO_TIMING=0;
NO_ROWS=0;
WINCOLS=`stty size | awk '{print $2}'`

# Parse parameters

while [ "$1" != "" ]
do
case "$1" in
"-c") shift; COMMENT=$1; shift;;
"-d") shift; DB_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;;
"-t") shift; NO_TIMING=1;;
"-w") shift; NO_ROWS=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"

[ $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 '/-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 ] && NO_TIMING=0 && NO_TIMING=0

echo "$PARAMS" | awk '/-sql/ && (/-t/ || /-w/){exit -1}' || \
PARSE_ERROR="${PARSE_ERROR} -Cant combine -sql with -t or -w"

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

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}${NO_TIMING}${NO_ROWS}${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 NO_TIMING NO_ROWS WINCOLS

# 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
echo "[Creating: $SUM_OUT]"
else
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 [ $RESULTS_DIR -eq -1 ]
then
echo db2 get snapshot for dynamic sql on $DB_NAME
echo ""
db2 get snapshot for dynamic sql on $DB_NAME | awk -f $AWKSCRIPT
else
if [ $REUSE_OUT -eq 0 ]
then
eval echo db2 get snapshot for dynamic sql on $DB_NAME $Q_OUTPUT
eval echo ""  $Q_OUTPUT
echo "[Creating: $RES_OUT]"
db2 get snapshot for dynamic sql on $DB_NAME > $RES_OUT
else
echo ""
echo "[Reusing: $RES_OUT]"
fi

eval echo ""  $Q_OUTPUT
eval awk -f $AWKSCRIPT $RES_OUT $Q_OUTPUT
fi