#-------------------------------------------------------------------------------
# Function to syntax check a SQL file using the explain facility.
# This has the advantage it will also check for runtime errors, e.g. referenced table does not exist.
# A successful explain with EXPLAIN MODE=EXPLAIN will return a warning message that the statement was
# not executed (rc 2).
# Additionally EXPLAIN the query and display just the timeron cost estimate.
# Note the caller must establish a database connection.
# The input file must use semi-colon statement terminators.
# Parameters $1 full path and name of SQL file, $2 debug switch (ksh set: +x for off, -x for on). 
#-------------------------------------------------------------------------------

PARSESQL() {

if [[ $# -eq 2 ]]
then
   set $2 ; DEBUG_SWITCH=$2
   SQLFILE=$1
else
   MESSAGE "PARSESQL() Usage! I said: PARSESQL sqlfile(full path and name) debug(+x or -x)" PLAIN E +x
   return 1
fi

typeset -i RC 
RUN_DB2CLP "set current explain mode EXPLAIN" 0 NONE nocompress noattach $DEBUG_SWITCH
RC=$? ; [[ $RC -gt 0 ]] && return $RC  
RUN_DB2FILE $SQLFILE "-tvf" 2 NONE nocompress $DEBUG_SWITCH

RC=$? ; [[ $RC -gt 2 ]] && return $RC  

db2exfmt -d $DBALIAS -f O -w -1 -n % -s % -# 0 -o $LOGPATH/db2exfmt.${LOGDATE}.out 2> /dev/null
RC=$? ; [[ $RC -gt 0 ]] && return $RC  
COST=$(egrep "Cumulative Total Cost:" $LOGPATH/db2exfmt.${LOGDATE}.out | head -1 | awk  '{print "Estimated Execution Cost: ",$4}')
RESET_OUTPUT                                # display message to stdout, not the logfile 
MESSAGE "\n PARSESQL() $COST \n" PLAIN I +x 
REDIRECT_OUTPUT $LOG $DEBUG	            # now redirect stdout back to the logfile
RUN_DB2CLP "set current explain mode NO     " 0 NONE nocompress noattach $DEBUG_SWITCH
RC=$? ; [[ $RC -gt 0 ]] && return $RC  
}
