#!/bin/sh 
#
# Cmdline runQPTune

#
# See if SYBASE is defined.
#
if [ "$SYBASE" = "" ]
then
	echo "\$SYBASE environment is not set."
	exit 1
fi

#
# See if SYBASE_ASE is defined.
#
if [ "$SYBASE_ASE" = "" ]
then
	echo "\$SYBASE_ASE environment is not set."
	exit 1
fi

if [ "$1" = "" ]
then 
	echo "Usage: runQPTune app_script"
	exit 1
fi

SCRIPT="$1"
APP_NAME=`basename $1`

# ##### Followings are customizable variables #####

CONNECT_LOGIN="-Usa -P"
# CONNECT_SERVER="-S sunspot:15523/glm_appl_db"
# ISQL_CONNECT="$CONNECT_LOGIN -SSunspot1523"

# CONNECT_SERVER="-S QPSolaris1:4750/megaphone2"
# ISQL_CONNECT="$CONNECT_LOGIN -SQPSolaris1"

CONNECT_SERVER="-S linuxqpexec3:4816/master"
ISQL_CONNECT="$CONNECT_LOGIN -Slinuxqpexec3"

OUTPUT_DIR=$APP_NAME.output

# #################################################

QPT_CONNECT="$CONNECT_LOGIN $CONNECT_SERVER"

#
# Setup the path for the QPTune
#
PATH="$SYBASE/OCS-15_0/bin:$SYBASE/OCS-12_5/bin:$SYBASE/$SYBASE_ASE/qptune:$PATH"

if [ -d "$OUTPUT_DIR" ]; then echo "$OUTPUT_DIR exists"; else echo "Create $OUTPUT_DIR"; mkdir $OUTPUT_DIR; fi

if [ -d "$OUTPUT_DIR" ]; then echo "All output files are generted at $OUTPUT_DIR"; else echo "Please re-set \$OUTPUT_DIR to where you have written permission."; fi

echo "You are about to execute QPTune for application $SCRIPT. It will reset missing counters in sysstatistics and delete entries from sysquerymetricsgsysqueryplans table on the target server $CONNECT_SERVER. Continue? (Press any key to continue or press CTRL+C to quit.)"
read "key"

echo "#######################################################"
echo "Fix missing statistics                                 "
echo "#######################################################"

QPTune -A start_stats $QPT_CONNECT -v

isql $ISQL_CONNECT < $SCRIPT > $OUTPUT_DIR/$APP_NAME-missingstats-isql.out

QPTune -A collect_stats $QPT_CONNECT -m 0 -v -o $OUTPUT_DIR/$APP_NAME-missingstats.xml

QPTune -A fix_stats $QPT_CONNECT -m 0 -v -i $OUTPUT_DIR/$APP_NAME-missingstats.xml

#QPTune -A undo_fix_stats $QPT_CONNECT -m 0 -v -i $OUTPUT_DIR/$APP_NAME-missingstats.xml


echo "#######################################################"
echo "Run application in allrows_mix mode and collect metrics"
echo "#######################################################"

QPTune -A start -M allrows_mix $QPT_CONNECT -v

isql $ISQL_CONNECT < $SCRIPT > $OUTPUT_DIR/$APP_NAME-mix-isql.out

QPTune -A collect -M allrows_mix $QPT_CONNECT -v -o $OUTPUT_DIR/$APP_NAME-mix-collect.xml

echo "#######################################################"
echo "Run application in allrows_dss mode and collect metrics"
echo "#######################################################"

QPTune -A start -M allrows_dss $QPT_CONNECT -v
isql $ISQL_CONNECT < $SCRIPT > $OUTPUT_DIR/$APP_NAME-dss-isql.out

QPTune -A collect -M allrows_dss $QPT_CONNECT -v -o $OUTPUT_DIR/$APP_NAME-dss-collect.xml

echo "########################################################"
echo "Run application in allrows_oltp mode and collect metrics"
echo "########################################################"

QPTune -A start -M allrows_oltp $QPT_CONNECT -v
isql $ISQL_CONNECT < $SCRIPT > $OUTPUT_DIR/$APP_NAME-oltp-isql.out

QPTune -A collect -M allrows_oltp $QPT_CONNECT -v -o $OUTPUT_DIR/$APP_NAME-oltp-collect.xml

echo "################################################"
echo "Compare through the modes and get the best plans"
echo "################################################"

QPTune -A compare -M best $QPT_CONNECT -v -f $OUTPUT_DIR/$APP_NAME-mix-collect.xml,$OUTPUT_DIR/$APP_NAME-dss-collect.xml,$OUTPUT_DIR/$APP_NAME-oltp-collect.xml -o $OUTPUT_DIR/$APP_NAME-best.xml -s > $OUTPUT_DIR/$APP_NAME-compare.out 

echo "Would you like to proceed with plan fix by using the best plans from $OUTPUT_DIR/$APP_NAME-best.xml? (Press any key to continue or press CTRL+C to quit.)"
read "key"

echo "#################################################"
echo "Fix the server with the best plans"
echo "#################################################"

QPTune -A fix $QPT_CONNECT -i $OUTPUT_DIR/$APP_NAME-best.xml -v > $OUTPUT_DIR/$APP_NAME-fix.out

echo Server plans are fixed.

echo "#################################################################"
echo "Run application again by using the best plans and collect metrics"
echo "#################################################################"

isql $ISQL_CONNECT < $SCRIPT > $OUTPUT_DIR/$APP_NAME-afterfix-isql.out

QPTune -A collect -M best $QPT_CONNECT -v -o $OUTPUT_DIR/$APP_NAME-afterfix-collect.xml
echo Final collect finished.

echo "############################################################"
echo "Verify the application indeed runs with the best performance"
echo "############################################################"

QPTune -A compare -M best $QPT_CONNECT -v -f $OUTPUT_DIR/$APP_NAME-afterfix-collect.xml,$OUTPUT_DIR/$APP_NAME-best.xml -d 1,0 -o $OUTPUT_DIR/$APP_NAME-verify-fixwbest.xml > $OUTPUT_DIR/$APP_NAME-verify-fixwbest.out

echo "----- runQPTune finished successfully. Please check the output files generated under directory $APP_NAME.-----"


