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

display_syntax()
{
echo "\
SYNTAX: `basename $0` -c \"comment\" -d dbname [-u user/pwd] -q queryNum -o N
      [-ir rows] [-if rows] [-e m] [-v] [-b]
Execute db2batch tool which executes the SQL found in a file and reports on
execution times and snapshot information.
-c:   Comment placed on first line of Summary output file (required)
-d:   Database name (required)
-u:   Userid and password seperated by slash (default=db2inst1/db2inst1)
-q:   Query number to execute. File with .sql extension must exist in
\$QUERIES directory (required)
-o:   Save output in dir N under \$RESULTS (required)
(0=Current dir; -1=Not Valid; no default)
-ir:  Rows to return to output file (-1=all,default=10)
-if:  Rows to fetch even if not returned (-1=all,default)
-e:   Explain level: 0=ExecuteOnly; 1=ExplainOnly; 2=Explain&Execute
(default=2)
-v:   Verbose (default is NOT to display output)
-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 output and summary filename.
"
}

# Constants

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

# Defaults

QUIET=1
RESULTS_DIR=""# no default
SAVE_OLD_RESULTS=1
PARSE_ERROR=""
PARAMS=$*

USERPASS="db2inst1/db2inst1"
QRYNUM=""
IROWS=10
IFETCH=-1
EXPLAIN=2

# Parse parameters

while [ "$1" != "" ]
do
case "$1" in
"-c") shift; COMMENT=$1; shift;;
"-d") shift; DB_NAME=$1; shift;;
"-u") shift; USERPASS=$1; shift;;
"-q") shift; QRYNUM=$1; shift;;
"-ir") shift; IROWS=$1; shift;;
"-if") shift; IFETCH=$1; shift;;
"-e") shift; EXPLAIN=$1; shift;;
"-o") shift; RESULTS_DIR=$1; shift;;
"-v") shift; QUIET=0;;
"-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"

[ "$QRYNUM" = "" ] && \
PARSE_ERROR="${PARSE_ERROR} -Query number is required" || \
[ $QRYNUM -ge 0 ] 2>/dev/null || \
PARSE_ERROR="${PARSE_ERROR} -Invalid number following -q param"

[ "$RESULTS_DIR" = "" ] && \
PARSE_ERROR="${PARSE_ERROR} -Output directory number is required" || \
[ $RESULTS_DIR -ge 0 ] 2>/dev/null || \
PARSE_ERROR="${PARSE_ERROR} -Invalid number following -o param"

echo "$USERPASS" | awk -F/ '($1 == "") || ($2 == ""){exit -1}' || \
PARSE_ERROR="${PARSE_ERROR} -Invalid user/pass following -u"

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

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

[ $EXPLAIN -ge 0 -a $EXPLAIN -le 2 ] 2>/dev/null || \
PARSE_ERROR="${PARSE_ERROR} -Invalid number following -e param"

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

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

RES_EXT="_${DB_NAME}${RES_EXT}"
SUM_EXT="_${DB_NAME}${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 [ $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
cp $RES_OUT $RES_BAK 2>/dev/null && echo "[Created: $RES_BAK]"
cp $SUM_OUT $SUM_BAK 2>/dev/null && echo "[Created: $SUM_BAK]"
[ -f $RES_BAK ] && \
echo "[Modifying: $RES_BAK]" && \
`dirname $0`/db2bench_prefix.ksh $RES_BAK

fi
fi

[ $QUIET -eq 1 ] && VERBOSE="off" || VERBOSE="on"

# BEGIN .......

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

echo "[Creating: $SUM_OUT]"
echo "[Creating: $RES_OUT]"
echo ""

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

echo "-- $COMMENT">  $RES_OUT
echo "-- ----------------------------------------">> $RES_OUT
echo "-- Invocation: $0 $PARAMS">> $RES_OUT
echo "--             `date`">> $RES_OUT
echo "-- ">> $RES_OUT

eval echo db2batch \
-d $DB_NAME \
-f $QUERIES/${QRYNUM}.sql \
-r ${RES_OUT},${SUM_OUT} \
-a db2inst1/db2inst1 \
-c off \
-i complete \
-o r $IROWS  f $IFETCH  p 5  e $EXPLAIN \
-v $VERBOSE$Q_OUTPUT
eval echo ""$Q_OUTPUT

echo db2batch \
-d $DB_NAME \
-f $QUERIES/${QRYNUM}.sql \
-r ${RES_OUT},${SUM_OUT} \
-a db2inst1/db2inst1 \
-c off \
-i complete \
-o r $IROWS  f $IFETCH  p 5  e $EXPLAIN \
-v $VERBOSE>> $RES_OUT
echo "">> $RES_OUT

db2batch \
-d $DB_NAME \
-f $QUERIES/${QRYNUM}.sql \
-r ${RES_OUT}.tmp,${SUM_OUT}.tmp \
-a db2inst1/db2inst1 \
-c off \
-i complete \
-o r $IROWS  f $IFETCH  p 5  e $EXPLAIN \
-v $VERBOSE

[ ! -f ${RES_OUT}.tmp ] && echo "Error! File not created!
        RES_OUT=${RES_OUT}.tmp" &&
exit

cat ${RES_OUT}.tmp>> $RES_OUT && \
rm  ${RES_OUT}.tmp

cat ${SUM_OUT}.tmp>> $SUM_OUT && \
rm  ${SUM_OUT}.tmp

# Prefix selected lines with filename
echo ""
echo "[Modifying: $RES_OUT]"
`dirname $0`/db2bench_prefix.ksh $RES_OUT

#db2batch -d tpc -f queries/2.sql -a db2inst1/db2inst1 -c off -i complete -o r 2 f -1 p 5 e 0 -v off | awk '/Summary of Results/{doprint=1}doprint||/seconds *$/{print}'