RUNSTATS() {
# Procedure to RUNSTATS a given schema/table combination.
# Uses a stats profile if one exists, otherwise sets the profile as well.
# Caller establishes the database connection through a call to CONNECT().
# 
# Arguments $1 schema name - will fold to upper case; this argument must be supplied but may use 
#                            the default schema $DBSCHEMA (if set in $INSTHOME/sqllib/userprofile)
#           $2 table name  - unqualified 
#           $3 ksh debug switch (+x or -x)
# e.g. RUNSTATS $DBSCHEMA EMPLOYEE +x. 
# Edit LOGDIR.
# Alex Levy, 19.11.2007 http://www.sustainablesoftware.net

 if [[ $# -eq 3 ]]
 then
    DEBUG=$3 ; set $DEBUG
    typeset -u SCHEMA=$1 TABLE=$2 
    TARGET=$SCHEMA.$TABLE
    MESSAGE "Call is RUNSTATS $*" PLAIN I $DEBUG 
 else
    MESSAGE "RUNSTATS() Bad Call! \n Usage RUNSTATS \$1 schema name \$2 table name \$3 ksh debug switch (+x or -x)\n" PLAIN E +x
    return 1 
 fi    
 
 LOGDIR=$INSTHOME/log  # edit this 
 DIR_CHK $LOGDIR $DEBUG  
 LOGDATE=$(date +"%Y%m%d:%H:%M:%S")

 typeset -i PROFILE=$(db2 -x "select count(*) from syscat.tables where tabschema = '${SCHEMA}' and tabname = '${TABLE}' and statistics_profile is not null and type = 'T' for fetch only")
 typeset -i RC=$?
 if [[ $RC -gt 0 ]]
 then
    MESSAGE "RUNSTATS() :Bad return code $RC trying to find stats profile for $TARGET. Exiting ..." PLAIN E $DEBUG 
    return $RC
 fi

 case $PROFILE in
      0) # runstats and set profile in one pass
         RUN_DB2CLP "runstats on table $TARGET on all columns with distribution on all columns and detailed indexes all allow read access set profile" 0 NONE nocompress noattach $DEBUG
         ;;
      1) # stats profile exists, let's use it
         RUN_DB2CLP "runstats on table $TARGET use profile" 0 NONE nocompress noattach $DEBUG
         ;;
      *) MESSAGE "RUNSTATS() Logic error in code, Alex please review" PLAIN I $DEBUG
         return 1
         ;;
 esac
}

