#!/bin/ksh

script_help()
{
notify MSG "
  =============================================================================
  Copyright (c) 2002-2007 IBM
All rights reserved. No part of this script may be copied or translated
in any form or by any means without prior written permission from IBM.
DO NOT MODIFY THIS SCRIPT LOCALLY
This script is part of a standard nationally maintained toolkit. Do not make
modifications to local copies.
=============================================================================
Description
=============================================================================
  Script name:    UDBStoreproc.ksh
  Purpose:  Extract the explain plans for packages mentioned in query
    It must be run by an instance owning ID.
    - must mention a out file path
    - runs the command against the instance(s)
    - Checks for errors in the output(s)
    - output is stored in the script log and optionally a separate
      report file in the log dir.
  Output:  $LOGDIR/$SCRIPTNAME.yyyymmdd - script log of all messages
      also may include the following depending on notify
      options specified;
        pageout status and errors
        pageout errors only
        mail status and errors
        mail errors only (default)
        screen display status and errors
  Syntax:  scriptname [-?]
 <instancename>:<dbname>[,<dbname>...]
          [<notify opt> [<notify opt>] ... ]
          [ -z STOR_PROC_SCHEMA] |
          [ -f OUTPATH]
          input parameters MUST include:
          one instance name : one database name
          input parameters MAY include in any order:
          -? to display header info to the screen and exit
          notify options, include
            -pa pageout of status and error messages
            -pe pageout of errors only
            -ma mail of status and error messages
            -me mail of errors only
            -s screen display of status and error messages (default)
            -lg send errors only to /usr/bin/logger
  Example : ./UDBStoreproc.ksh db2inst1:MYDB -z DB2ADMIN -f /home/db2inst1/explains  To run for a specific schema on a database
                or
           ./UDBStoreproc.ksh -a -f /home/db2inst1/explains To run for all dbs
                or
           ./UDBStoreproc.ksh db2inst1:MYDB  -f /home/db2inst1/explains To run for a specified database
"
}# =============================================================================
# History of Changes
# =============================================================================
# Date        Person    Version Comments
# 2018/10/31  M.Kumar  	1.00    Built on code delivered by Raghavendra Chiyodu


extract_local_parms()
{
  log "Extracting local input parms: ${*}"
  LocalParmList="${*}"
  NUMPARMS=${#*}
  SCHEMAMARKED=0
  OUTDIRMARKED=0
  ALLDBS=`echo $LocalParmList|grep -ie '-a'|wc -l`

   I=1

  while (( ${I} <= ${NUMPARMS} )) ; do
    PARM=$1
    case "$PARM" in
         -z)
        SCHEMA=$2
        SCHEMAMARKED=1
         shift
        ((I=${I}+1))
        ;;
        -f)
        OUTDIR=$2
        OUTDIRMARKED=1
        OUTDIRPRE=`ls -ldr $OUTDIR|wc -l`
         shift
        ((I=${I}+1))
        ;;
        *)
        NEWPARMLIST="${NEWPARMLIST} ${PARM}"
        ;;
esac
         shift
    ((I=${I}+1))

  done
  PARMLIST="${NEWPARMLIST}"
}



## Utility script

mainfunction ()
{
mytime=`date '+%Y%m%d%H%M'`
DB=$1
SCHEMA=`echo $2 |awk '{print toupper($0)}'`

db2 connect to $DB > /dev/null

db2 "describe table EXPLAIN_STATEMENT" >/dev/null
RC=$?
if [ $RC -gt 0 ] ;then
        log "Error Accessing plan tables"
        continue
fi


db2 -x " select '$DB $SCHEMA' , pkgname, a.routinename from syscat.routines a,syscat.routinedep b,syscat.packages c where a.SPECIFICNAME=b.ROUTINENAME and b.BNAME=c.PKGNAME  and a.routineschema='$SCHEMA'" |tr -s " "> $OUTDIR/$SCRIPTNAME.pkglist.out
RC=$?
if [ $RC -ne 0 ] ; then
        log "Error Accessing routines ... exiting "
        continue
fi
has_pks=`cat $OUTDIR/$SCRIPTNAME.pkglist.out|wc -l`
if [ $has_pks -eq 0 ] ; then
        log " The mentioned schema has no packages .. Continuing "
fi

cat  $OUTDIR/$SCRIPTNAME.pkglist.out |while read LINE
do

    DB=`echo "$LINE"|awk '{print $1}'`
    SCHEMA=`echo "$LINE"|awk '{print $2}'`
    PKG=`echo "$LINE"|awk  '{print $3}'`
    PROC=`echo "$LINE"|awk  '{print $4}'`
    REPORT=$OUTDIR/$SCRIPTNAME.$DB.${SCHEMA}_PROCPLANS.${mytime}.txt
    OUTFILE=$OUTDIR/"${PROC}".plan
    db2expln -database ${DB} -output "$OUTDIR/$SCRIPTNAME.$PROC.out" -schema "${SCHEMA}" -package ${PKG}
RC=$?
if [ $RC -ne 0 ] ; then
        log "Error extracting access plans"
        continue
else
        cat $OUTDIR/$SCRIPTNAME.$PROC.out >> $REPORT
fi
done

}

##################################################
# -----------------------------------------------------------------------------
# Standard initialization - be careful with order of commands here
# -----------------------------------------------------------------------------

# set INSTNAME and DBNAME to lowercase
typeset -l INSTNAME
typeset -l DBNAME

# Get the path the script is executing from
SCRIPTPATH=$(dirname ${0})


SCRIPTNAME=$(basename ${0})
DATE=$(date '+%Y%m%d%H%M')

# read in the Common routines
. ${SCRIPTPATH}/Common

# read in the global variables.
. ${SCRIPTPATH}/.param

# log the script start (at least we will get a log even if failure in extract_parms)
# this is the earliest we can do this as we need the LOGDIR, etc.
log "\n\nStarting script ${SCRIPTPATH}/${SCRIPTNAME}.\n============================================================"

CHOICE=DBSCHE
SCHEMAMARKED=0
ALLDBS=0
extract_parms ${*}

#Setting up local params



# ----------------------------------------

# ----------------------------------------
# UDB script specific initialization
# ----------------------------------------
# read in the Common routines
. ${SCRIPTPATH}/UDBcommon

TOTRC=0

CURINST=$(whoami)
set_db2profile $CURINST

# set the list of all instances on server
set_all_inst_list

# set the list of instance to process
set_inst_list

OS=$(uname -s)

# ----------------------------------------

if [[ -z $(env|grep TEMP) ]]; then
  TEMP="/tmp"
fi
log "TEMP var set to $TEMP"

for INST in ${INSTLIST}; do
  INSTNAME=$(print ${INST} | awk -F: '{print $1}')
  print ${ALLINSTLIST} | grep -q ${INSTNAME}
  ((RC=$?))
  set_tot_rc
done
#Allow script to only be run by Instance running script

  if [[ ${CURINST} != ${INSTNAME} ]] ; then
    notify ERROR "ERROR: $SCRIPTNAME must be run by $INSTNAME not $CURINST"
    continue
  elif (( ${RC} != 0 )) ; then
    notify ERROR "ERROR: Instance name ${INSTNAME} not found in db2ilist"
 continue
  else
    log "############################################################"
    log "Processing instance list entry ${INSTNAME}."
  fi

  set_db2profile $INSTNAME

  # Checks if script already running on instance
  chk_script_run
  ((RC=$?))
  set_tot_rc
  if (( ${RC} != 0 )); then
    notify WARNING "WARNING: ${SCRIPTNAME} process already running against UDB instance ${INSTNAME}"
  fi

  # set the environment for the instance
  set_inst_param ${INSTNAME}


  # set the list of databases to process
  set_db_list
  ((RC=$?))
  set_tot_rc

  get_inst_level

    if (( $LEVEL < 91 )) ; then
      log "Script does not support lower than 9.1"
      ((RC=111))
      set_tot_rc
      continue

    fi

  if (( ${RC} == 0 )) ; then
    for DBNAME in ${DBLIST}; do
     log "======================================================"
     HADR_DB_ROLE=$(db2 get db cfg for $DBNAME |grep "HADR database role" | awk '{print $NF}'|sed 's/^[= \t]*//')

     if [ ! -z "$HADR_DB_ROLE" ] && [ STANDBY = $HADR_DB_ROLE ] ;then
      log "cannot process Database $DBNAME in HADR STANDBY mode"
     elif [ $OUTDIRMARKED -eq 0 ] ; then
         log "cannot process the tool as the out path is not mentioned "
        script_help
     else
        if [ $OUTDIRPRE -eq 1 ] ; then

               HAS_PROC=`db2 connect to $DBNAME > /dev/null;db2 -x " select distinct  PROCSCHEMA from syscat.PROCEDURES where PROCSCHEMA not like 'SYS%' and PROCSCHEMA!='SQLJ'"|wc -l`
                if [ $HAS_PROC -ge 1 ] && [ $SCHEMAMARKED -eq 1 ] ; then
                        log "Executing for $DBNAME"
                        mainfunction $DBNAME $SCHEMA
                elif [ $HAS_PROC -ge 1 ] && [ $SCHEMAMARKED -eq 0 ] ; then
                        db2 connect to $DBNAME > /dev/null;db2 -x " select distinct  PROCSCHEMA from syscat.PROCEDURES where PROCSCHEMA not like 'SYS%' and PROCSCHEMA!='SQLJ'" | while read SCHEMA ;
                        do
                                 log "Executing for $DBNAME"
                                 mainfunction $DBNAME $SCHEMA
                        done
               elif  [ $HAS_PROC -eq 0 ] ; then
                        log " DB $DBNAME has no procs to execute "
                        continue
                 fi
        else
                echo " Output directory  $OUTDIR is not found . Please create the same our use correct path "
                log " Output directory  $OUTDIR is not found . Please create the same our use correct path "
        fi

           fi
        done
fi

exit ${TOTRC}

