#!/bin/ksh
################################################################################
# xmpDB2ClearTable.sh - Delete content of a table without logging
# ------------------------------------------------------------------------------
# Parameter: Database- Target Database
#            Table     - Creator.Table
#     Logfile    - Log File Name (opt.)
#
# Calls ...: db2- IBM DB2 UDB >= V6.1
#            xmpSetInstanz.sh
#            whence db2 - Check existence of DB2
#
# Comments : This utility loads an empty file into a db2 table using the DB2
#            LOAD utility with option NONRECOVERABLE. This avoids logging of
#            a mass delete operation.
# ------------------------------------------------------------------------------
# Autor      G.Ruban /29.04.02  - first version V1.1
################################################################################

#-------------------------------------------------------------------------------
# Init
#-------------------------------------------------------------------------------
  PROG=`basename $0`
  VER="1.1"

#-------------------------------------------------------------------------------
# Path to DBA's function library 
#-------------------------------------------------------------------------------
# der Defaultwert ist :
  xmpDBADIR="/opt/xmpdba"
  export xmpDBADIR
  xmpDbaDir="$xmpDBADIR"

  if [ ! -f "$xmpDbaDir/bin/xmpFunktion.sh" ] ; then
     Msg="Cannot find xmpFunktion.sh, expected in variable xmpDBADIR - abend! "
     echo $PROG $Msg
     return 128
  fi
 
#-------------------------------------------------------------------------------
# Activate functions
#-------------------------------------------------------------------------------
   . ${xmpDbaDir}/bin/xmpFunktion.sh
  if [ $? -ne 0 ] ; then
     Msg="Cannot activate xmpFunktion.sh - abend!"
     echo $PROG $Msg
     exit 128
  fi

#-------------------------------------------------------------------------------
# Set path to perl if not alread set
#-------------------------------------------------------------------------------
  if [ ! "$PERL5LIB" ]; then      
     PERL5LIB=":/usr/local/bin:$xmpDBADIR/bin"
     export PERL5LIB
  else
     if [ -z `echo $PERL5LIB | grep "[.]*$xmpDBADIR/bin[.]*"` ]; then
        PERL5LIB="$PERL5LIB:/usr/local/bin:$xmpDBADIR/bin"
        export PERL5LIB
     fi   
  fi

#-------------------------------------------------------------------------------
# Check arguments
#-------------------------------------------------------------------------------
  if [ $# -lt 2 $or $1 = ? or -z $2 ] ; then
InfoMsg "Requested syntax:"
InfoMsg "$0 database table [logfile]"
InfoMsg "... where database = name of local or remote database"
InfoMsg "          table    = table name, which includes creator"
InfoMsg "          logfile  = log file name (optional)"
InfoMsg "Abends with RC=4"
 exit 4
  fi

#-------------------------------------------------------------------------------
# Information when calling script
#-------------------------------------------------------------------------------
  InfoMsg $PROG " - V$VER"
  InfoMsg "======================================================="
  InfoMsg "Current User ................: "`id`
  InfoMsg "Current Date/Time ...........: "`date +%Y-%m-%d-%H.%M.%S`
  InfoMsg "System ......................: "`uname -n`
  InfoMsg "Target Database .............: $1"
  InfoMsg "Target Table to clear........: $2"

  db=$1
  tb=$2
  log=$3 

#-------------------------------------------------------------------------------
# Check path to DB2 binaries
#-------------------------------------------------------------------------------
  work=`whence db2`
  if [ $? -ne 0 ] ; then
#-----------------------------------------------------------------------
# Set DB2 environment
#-----------------------------------------------------------------------
if [ -x "$xmpDbaDir/bin/xmpFunktion.sh" ]; then
. ${xmpDbaDir}/bin/xmpSetInstanz.sh $db >> /dev/null
rc=$?
if [ $rc -ne 0 ] ; then
WarningMsg "$PROG: Cannt set DB2 environment using"
WarningMsg "$xmpDbaDir/bin/xmpFunktion.sh, RC=$rc."
ErrorMsg "Abends with RC=128"
exit 128
fi
else
WarningMsg "$PROG: Cannt set DB2 environment using"
WarningMsg "$xmpDbaDir/bin/xmpFunktion.sh, RC=$rc."
ErrorMsg "Abends with RC=128"
exit 128
fi
InfoMsg "DB2 instance/path ...........: $DB2INSTANCE at $INSTHOME"
  fi

#-------------------------------------------------------------------------------
# Set perl bin path
#-------------------------------------------------------------------------------
  perlpath=`whence perl`
  if [ $? -ne 0 ] ; then
     if [ -x "/usr/local/bin/perl" ]; then
PATH="$PATH:/usr/local/bin"
     export PATH
     else
        if [ -x "/usr/bin/perl" ]; then
   PATH="$PATH:/usr/bin"
        export PATH
        else
           WarningMsg "$0: Perl Binary not found, possibly cannot execute Perl."
        fi
     fi
  else
     perlpath=`dirname $perlpath`
     PATH="$PATH:$perlpath"
     export PATH
  fi

#-------------------------------------------------------------------------------
# Create/Set log file name
#-------------------------------------------------------------------------------
  if [ ! "$log" ]; then
#logpath=`dirname $0`
logpath="$xmpDBADIR/log"
if [ $logpath == "." ]; then
logpath=$PWD
fi
logfnam=`basename $0 | awk -F. '{print $1}'`
logfile="$logpath/$logfnam.LOG"
  else
        logfile=$log
  fi

  InfoMsg "Utility Log File Name .......: $logfile"

  rm -f $logfile
  rc=$?
  if [ $rc -gt 2 ] ; then
WarningMsg "$PROG: $logfile not deletable/reusable."
ErrorMsg "Abends with RC=128"
        exit 128
  fi

  echo "">$logfile
  rc=$?
  if [ $rc -ne 0 ] ; then
WarningMsg "$PROG: Cannot write to $logfile (authorization)."
ErrorMsg "Abends with RC=128"
        exit 128
  fi

#-------------------------------------------------------------------------------
# Set DB2 command interface options
#-------------------------------------------------------------------------------
  db2 "UPDATE COMMAND OPTIONS USING S ON Z ON $logfile V OFF">>/dev/null
  rc=$?
  if [ $rc -ne 0 ] ; then
WarningMsg "$PROG: Cannot set DB2 command options, RC=$rc."
ErrorMsg "Abends with RC=128"
        exit 128
  fi

#-------------------------------------------------------------------------------
# DB2 Connect to Target Database
#-------------------------------------------------------------------------------
  db2 "connect to $db">>/dev/null
  rc=$?
  if [ $rc -ne 0 ] ; then
WarningMsg "$PROG: DB2 Connect to $db ends with RC=$rc."
ErrorMsg "Abends with RC=128"
        exit 128
  fi

#-------------------------------------------------------------------------------
# Create temporary DB2 NULL.DEL file
#-------------------------------------------------------------------------------
  echo "" >/tmp/NULL.DEL

#-------------------------------------------------------------------------------
# Start DB2 LOAD utility
#-------------------------------------------------------------------------------
  echo "--------------------------------------------"  | tee -a $logfile
  echo "Processing starts at "`date +%Y-%m-%d-%H.%M.%S`" ..."  | tee -a $logfile
  echo "--------------------------------------------"  | tee -a $logfile
  db2 "load from /tmp/NULL.DEL of del replace into $tb nonrecoverable" | tee -a $logfile
  rc=$?
  echo "------------------------------------------"  | tee -a $logfile
  echo "Processing ends at "`date +%Y-%m-%d-%H.%M.%S`" ..."  | tee -a $logfile
  echo "------------------------------------------"  | tee -a $logfile

#-------------------------------------------------------------------------------
# Disconnect from DB2 Target Database
#-------------------------------------------------------------------------------
  db2 "connect reset">>/dev/null
  rcd=$?
  if [ $rcd -ne 0 ] ; then
WarningMsg "$PROG: DB2 Disconnect from $db ends with RC=$rcd."
WarningMsg "Processing continues!"
  fi

#-------------------------------------------------------------------------------
# Reset DB2 command interface options
#-------------------------------------------------------------------------------
db2 "UPDATE COMMAND OPTIONS USING V ON Z OFF">>/dev/null

#-------------------------------------------------------------------------------
# Check success of DB2 Utility
#-------------------------------------------------------------------------------
  if [ $rc -ne 0 ] ; then
        ${xmpDbaDir}/bin/xmpLog.pl -a $db -s E -t "xmpDB2ClearTable.sh for $tb with errors."
WarningMsg "$PROG: DB2 Utility ends with RC=$rc."
ErrorMsg "Abends with RC=128"
        exit 128
  fi

  errcnt=`cat $logfile | grep -i error | grep 'SQL....N' | wc -l`
  if [ $errcnt -ne 0 ] ; then
        ${xmpDbaDir}/bin/xmpLog.pl -a $db -s E -t "xmpDB2ClearTable.sh for $tb ends with $errcnt errors."
WarningMsg "$PROG: DB2 Utility ends with $errcnt errors."
ErrorMsg "Abends with RC=64"
        exit 64
  fi

  errcnt=`cat $logfile | egrep '(SQL....C|SQL3304N|SQL3038N)' | wc -l`
  if [ $errcnt -ne 0 ] ; then
        ${xmpDbaDir}/bin/xmpLog.pl -a $db -s E -t "xmpDB2ClearTable.sh für $tb ends with $errcnt errors."
WarningMsg "$PROG: DB2 Utility ends with $errcnt errors."
ErrorMsg "Abends with RC=64"
        exit 64
  fi

#-------------------------------------------------------------------------------
# Remove temporary file
#-------------------------------------------------------------------------------
  rm -rf /tmp/NULL.DEL

#-------------------------------------------------------------------------------
# Make an entry in DBA log file
#-------------------------------------------------------------------------------
  ${xmpDbaDir}/bin/xmpLog.pl -a $db -s I -t "xmpDB2ClearTable.sh for $tb successful."

#-------------------------------------------------------------------------------
# *** END ***
#-------------------------------------------------------------------------------
  InfoMsg "$PROG finished. (RC=0)"
 
exit 0