Shell Script xmpDB2Util – Frame for DB2 utilities which connect to database

Shell Script xmpDB2Util – Frame for DB2 utilities which connect to database

This KSH shell script executes DB2 SQL statements and commands. The script and starts an DB2 enviroment connects itself to the database specified as argument. 

#!/bin/ksh
################################################################################
# xmpDB2Util - Frame for DB2 utilities which connect to database
# ------------------------------------------------------------------------------
# Parameter: DML          	- Dateiname der LOAD DML
#            TDB         	- Target Database
#            LOG                - (opt) Log File Name
# Calls ...: xmpSetFunktion.sh	- Funktionsbibliothek
#            xmpSetInstance.sh  - Setzen der xmp DB2 Umgebung
#            db2                - IBM DB2 Command Line Interface
#            xmpLog.pl          - Logging Funktion
# Comment  : Alle, für DB2 Utilities, die DB Connection benötigen (Export, Load
#            etc), erforderlichen Schritte durchführen.
#            - Umgebung aufbauen per xmpSetInstanz.sh
#            - Connect (implizit, ohne Angabe von User und Psw)
#            - Ausführung Utility
#            - Protokollierung auf xmpdba.log
#            - Connect abbauen
# ------------------------------------------------------------------------------
# Autor      GR /03.01.01       - erste Version V1.1
# 27.04.01/GR - V1.2.2: Aufruf von xmpLog.pl war nicht korrekt parameterisiert,
#               Ausgabe der DML im Utility Log und der xmpdba.log Datei,
#               Prüfung auf DB2/SQL Fehler im Utility Log,
#               Formatierung von Fehler- und Warnungen überarbeitet.
# 02.05.01/GR - Korrektur bei echo von "select * ..."
# 08.06.01/GR - V1.2.3: Changed comments and message after applying SetInstanz.sh
# 27.07.01/GR - V1.2.4: Check, wether log file accessible
# 05.11.01/GR - V1.2.5: Write start/stop timestamp into log
# 29.01.02/GR - V1.3: Neew option, TEMPFILES support for DB2 LOAD Utility
# 08.05.02/GR - V1.4: Adapted to Solaris 2.8 (chmod 755 for temporary path),
#                     now considering error message SQL10018N
# 21.08.02/GR - V1.5: Print SQL Messages in case of errors
# 14.05.03/GR - V1.6: Now considering error message SQL3304N
# 04.08.03/GR - V1.7: Now considering error message SQL0911N w/o "error" keyword
# 03.03.04/GR - V1.8: DB2 w/o autocommit, w/o stop at execution error
# 08.03.04/GR - V1.9: Adapt to AIX usage, changed xmpSetInstanz.sh to xmpSetInstance.sh
# 16.05.07/GR - V1.9.1: SQL0091N is not an error message
# 18.05.07/GR - V2.0: Amendment when instance already allocated
################################################################################

#-------------------------------------------------------------------------------
# Init
#-------------------------------------------------------------------------------
  PROGM=`basename $0`
  VER="2.0"

#-------------------------------------------------------------------------------
# Plattform-spezifisch
#-------------------------------------------------------------------------------
  case `uname` in
    AIX)
      StringEQ="="
      ;;
    SunOS)
      StringEQ="=="
      ;;
   *)
    echo "$PROG: Unknown plaform, is not AIX or SunOS."
    exit 8
    ;;
  esac

#-------------------------------------------------------------------------------
# Pfad der DBA's Administrationsfunktion, -steuerdaten festlegen
#-------------------------------------------------------------------------------
# der Defaultwert ist :
  xmpDBADIR="/opt/xmpdba"
  export xmpDBADIR
  xmpDbaDir="$xmpDBADIR"

  if [ ! -f "$xmpDbaDir/bin/xmpFunktion.sh" ] ; then
     Msg="Der Defaultwert des DBA-Pfad stimmt nicht mehr. Abend! "
     echo $PROGM $Msg
     return 128
  fi

#-------------------------------------------------------------------------------
# Funktionen aktivieren
#-------------------------------------------------------------------------------
  . ${xmpDbaDir}/bin/xmpFunktion.sh
  if [ $? -ne 0 ] ; then
     Msg="Aktivieren der Funktionen $Funktion ist fehlgeschlagen. Abend!"
     echo $PROGM $Msg
     exit 128
  fi

#-------------------------------------------------------------------------------
# Info Block ausgeben
#-------------------------------------------------------------------------------
  InfoMsg "$0 - V$VER"
  InfoMsg "======================================================="
  InfoMsg "Current User ................: "`id`
  InfoMsg "Current Date/Time ...........: "`date +%Y-%m-%d-%H.%M.%S`
  InfoMsg "System ......................: "`uname -n`
  InfoMsg "DML (export/import/load o.a.): $1"
  InfoMsg "Target Database .............: $2"
  InfoMsg "Execution Mode ..............: NO AUTOCOMMIT and"
  InfoMsg "   (constant, not modifyable)  NO STOP ON ERROR"

#-------------------------------------------------------------------------------
# Argumente prüfen
#-------------------------------------------------------------------------------
  if [ $# -lt 1 or $1 = ? ] ; then
	InfoMsg "Syntax für $PROGM dmlfile tdb [logfile] [TEMPFILES]"
	InfoMsg "... where dmlfile   = DB2 DML File"
	InfoMsg "          tdb       = Target Database"
	InfoMsg "          logifle   = Log File (optional)"
	InfoMsg "          TEMPFILES = keyword, parallel LOAD Support"
	ErrorMsg "Abend mit RC=4"
 	exit 4
  fi

  if [ ! "$1" ]; then
	WarningMsg "$PROGM: Keine DB2 DML File angegeben!"
	ErrorMsg "Abend mit RC=16"
 	exit 16
  fi 

  if [ ! "$2" ]; then
	WarningMsg "$PROGM: Keine Target Database angegeben."
	ErrorMsg "Abend mit RC=16"
 	exit 16
  fi 

  dml=$1
  db=$2
  log=$3
  tf=""

#-------------------------------------------------------------------------------
# Checks on arguments
#-------------------------------------------------------------------------------
  if [ ! -r $dml ] ; then
	WarningMsg "$PROGM: DML File $dml nicht verarbeitbar!"
	ErrorMsg "Abend mit RC=16"
	exit 16
  fi

#-------------------------------------------------------------------------------
# Keyword gefunden: Parallel LOAD Support über TEMPFILES
#-------------------------------------------------------------------------------
  if [ "$3" $StringEQ "TEMPFILES" ]; then
	log=""
	tf=$3
  fi 

  if [ "$4" $StringEQ "TEMPFILES" ]; then
	tf=$4
  fi 

#-------------------------------------------------------------------------------
# Dateiname der DML File bearbeiten
#-------------------------------------------------------------------------------
  dmlpath=`dirname $dml`
  if [ $dmlpath $StringEQ "." ]; then
	dmlpath=$PWD
  fi
  dmlfnam=`basename $dml`
  dmlfile="$dmlpath/$dmlfnam"

#-------------------------------------------------------------------------------
# DB2 Log-Datei Pfad setzen und falls vorhanden, löschen
#-------------------------------------------------------------------------------
  if [ ! "$log" ]; then
	logpath=`dirname $dml`
	if [ $logpath $StringEQ "." ]; then
		logpath=$PWD
	fi
	logfnam=`basename $dml | 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 "$PROGM: $logfile nicht lösch-/wiederverwendbar."
	ErrorMsg "Abend mit RC=128"
        exit 128
  fi

  echo "">$logfile
  rc=$?
  if [ $rc -ne 0 ] ; then
	WarningMsg "$PROGM: $logfile nicht beschreibbar (Autorisierung)."
	ErrorMsg "Abend mit RC=128"
        exit 128
  fi

#-------------------------------------------------------------------------------
# If TEMPFILES specified, extend LOAD control card, if necessary
#-------------------------------------------------------------------------------
  if [ "$tf" $StringEQ "TEMPFILES" ]; then
	#---------------------------------------------------------------------------
	# Create temporary path using command name and process id
	#---------------------------------------------------------------------------
	tfnam="/tmp/$PROGM.$$"
	#echo ">>>$tfnam"
	mkdir $tfnam
	if [ ! -w $tfnam ] ; then
		WarningMsg "$PROGM: TEMPFILE Path $tfnam nicht nutzbar."
		ErrorMsg "Abend mit RC=128"
	        exit 128
	fi
	#-----------------------------------------------------------------------
	# Grant access to group and others				V 1.4
	#-----------------------------------------------------------------------
	chmod 775 $tfnam

	#-----------------------------------------------------------------------
	# Insert TEMPFILES option into DB2 LOAD Statements only:
	# change some words from mixed case to upper case, quit if insertion
        # already exists. If not, insert new/temporary string. That's it!
	# (Warning: Cannot replace $tfnam variable due to special chars (eg./)!)
	#-----------------------------------------------------------------------
	sed 	-e 's/[Ll][Oo][Aa][Dd]/LOAD/g' \
		-e 's/ [Ii][Nn][Tt][Oo] / INTO /g' \
		-e 's/ [Ii][Nn][Ss][Ee][Rr][Tt] / INSERT /g' \
		-e 's/ [Rr][Ee][Pp][Ll][Aa][Cc][Ee] / REPLACE /g' \
		-e 's/ [Rr][Ee][Ss][Tt][Aa][Rr][Tt] / RESTART /g' \
		-e 's/ [Tt][Ee][Rr][Mm][Ii][Nn][Aa][Tt][Ee] / TERMINATE /g' \
	  	-e '/LOAD/,/INTO/{/TEMPFILES/q
s/REPLACE INTO/TEMPFILES PATH tfnam &/g
s/INSERT INTO/TEMPFILES PATH tfnam &/g
s/RESTART INTO/TEMPFILES PATH tfnam &/g
s/TERMINATE INTO/TEMPFILES PATH tfnam &/g
}' \
		-e "s/tfnam/\/tmp\/$PROGM\.$$/g" \
		$dml > $tfnam/$dmlfnam

	#-----------------------------------------------------------------------
	# DML input has been modified now
	#-----------------------------------------------------------------------
	dml=$tfnam/$dmlfnam
  fi

#-------------------------------------------------------------------------------
# DB2 Umgebung setzen
#-------------------------------------------------------------------------------
  save_db=$db						# restore later	V1.9
  if [ "$lP_BANN" == "xmpSetInstance.sh" ]; then
    INSTHOME=`db2usrinf -d $DB2INSTANCE`
	InfoMsg "DB2 Umgebung existiert ......: $DB2INSTANCE at $INSTHOME"
  else
	if [ -x "$xmpDbaDir/bin/xmpFunktion.sh" ]; then
		. ${xmpDbaDir}/bin/xmpSetInstance.sh $db >> /dev/null
		rc=$?
		if [ $rc -ne 0 ] ; then
			WarningMsg "$PROGM: DB2 Umgebung kann nicht per "
			WarningMsg "$xmpDbaDir/bin/xmpFunktion.sh gesetzt werden,1 RC=$rc."
			ErrorMsg "Abend mit RC=128"
			exit 128
		fi
	else
		WarningMsg "$PROGM: DB2 Umgebung kann nicht per "
		WarningMsg "$xmpDbaDir/bin/xmpFunktion.sh gesetzt werden, RC=$rc."
		ErrorMsg "Abend mit RC=128"
		exit 128
	fi
	InfoMsg "DB2 Instanz/Umgebung ........: $DB2INSTANCE at $INSTHOME"
  fi  

  db=$save_db						# restore previous V1.9 

#-------------------------------------------------------------------------------
# DB2 Command Interface Options setzen
#-------------------------------------------------------------------------------
  #db2 "UPDATE COMMAND OPTIONS USING S ON Z ON $logfile V OFF">>/dev/null
  #rc=$?
  #if [ $rc -ne 0 ] ; then
  #	WarningMsg "$PROGM: DB2 Command Options nicht setzbar, RC=$rc."
  #	ErrorMsg "Abend mit RC=128"
  #	exit 128
  #fi

#-------------------------------------------------------------------------------
# DB2 Connect zur Target Database
#-------------------------------------------------------------------------------
  db2 "connect to $db">>/dev/null
  rc=$?
  if [ $rc -ne 0 ] ; then
	WarningMsg "$PROGM: DB2 Connect zu $db endet mit RC=$rc."
	db2 "connect to $db"					# V1.5
	ErrorMsg "Abend mit RC=128"
        exit 128
  fi

#-------------------------------------------------------------------------------
# DB2 Steuerkarten ausgeben
#-------------------------------------------------------------------------------
  #InfoMsg "Inhalt DML File zur Protokollierung ..."
  #dmldump=`cat $dml`
  #echo "File $dmlfile contains ..." | tee -a $logfile
  #echo "$dmldump" | tee -a $logfile

#-------------------------------------------------------------------------------
# DB2 Utility starten: -tvf terminator/verbose on/file input
#                      +s do not stop execution on error
#                      +c autocommit off
#-------------------------------------------------------------------------------
  echo "--------------------------------------------"  | tee -a $logfile
  echo "Processing starts at "`date +%Y-%m-%d-%H.%M.%S`" ..."  | tee -a $logfile
  echo "--------------------------------------------"  | tee -a $logfile
  #db2 -tf $dml | tee -a $logfile
  db2 +s +c -z$logfile -tvf $dml
  rc=$?
  echo "------------------------------------------"  | tee -a $logfile
  echo "Processing ends at "`date +%Y-%m-%d-%H.%M.%S`" ..."  | tee -a $logfile
  echo "------------------------------------------"  | tee -a $logfile

#-------------------------------------------------------------------------------
# DB2 Disconnect von Target Database
#-------------------------------------------------------------------------------
  db2 "connect reset">>/dev/null
  rcd=$?
  if [ $rcd -ne 0 ] ; then
	WarningMsg "$PROGM: DB2 Disconnect von $db endet mit RC=$rcd."
	WarningMsg "Verarbeitung wird fortgesetzt!"
  fi

#-------------------------------------------------------------------------------
# DB2 Command Interface Options weitesgehend zurücksetzen
#-------------------------------------------------------------------------------
#db2 "UPDATE COMMAND OPTIONS USING V ON Z OFF">>/dev/null

#-------------------------------------------------------------------------------
# Erfolg des DB2 Utilities prüfen, DB2/SQL Fehler in Logfile suchen
#-------------------------------------------------------------------------------
  if [ $rc -ne 0 ] ; then
        ${xmpDbaDir}/bin/xmpLog.pl -a $db -s E -t "xmpDB2Util.sh mit $dmlfile fehlerhaft."
	#cat $logfile 							# V 1.5
	WarningMsg "$PROGM: DB2 Utility endet mit RC=$rc."
	ErrorMsg "Abend mit RC=$rc"
        exit $rc
  fi

  #errcnt=`cat $logfile | grep -i error | grep 'SQL....N' | wc -l`		#V1.9.1
  errcnt=`cat $logfile | egrep -i error | egrep -v 'SQL0091N' | egrep 'SQL....N' | wc -l`
  if [ $errcnt -ne 0 ] ; then
        ${xmpDbaDir}/bin/xmpLog.pl -a $db -s E -t "xmpDB2Util.sh mit $dmlfile endet mit $errcnt Fehlern."
	cat $logfile | egrep '(SQL....C|SQL....N|SQL....W|SQL.....N)'	# V 1.6
	WarningMsg "$PROGM: DB2 Utility endet mit $errcnt Fehlern."
	ErrorMsg "Abend mit RC=64"
        exit 64
  fi

# errcnt=`cat $logfile | egrep '(SQL....C|SQL10018N|SQL3304N)' | wc -l`	# V 1.6
  errcnt=`cat $logfile | egrep '(SQL....C|SQL10018N|SQL3304N|SQL0911N)' | wc -l`	# V 1.7
  if [ $errcnt -ne 0 ] ; then
        ${xmpDbaDir}/bin/xmpLog.pl -a $db -s E -t "xmpDB2Util.sh mit $dmlfile endet mit $errcnt Fehlern."
#	cat $logfile | egrep '(SQL....C|SQL10018N|SQL3304N)'		# V 1.6
	cat $logfile | egrep '(SQL....C|SQL10018N|SQL3304N|SQL0911N)'	# V 1.7
	WarningMsg "$PROGM: DB2 Utility endet mit $errcnt Fehlern."
	ErrorMsg "Abend mit RC=64"
        exit 64
  fi
#-------------------------------------------------------------------------------
# Remove temporary path used for TEMPFILES option (do not check success of rmdir)
#-------------------------------------------------------------------------------
  if [ "$tf" $StringEQ "TEMPFILES" ]; then
	rm -rf $tfnam
  fi

#-------------------------------------------------------------------------------
# Erfolg im xmpdba.log protokollieren
#-------------------------------------------------------------------------------
  ${xmpDbaDir}/bin/xmpLog.pl -a $db -s I -t "xmpDB2Util.sh mit $dmlfile erfolgreich."

#-------------------------------------------------------------------------------
# *** ENDE ***
#-------------------------------------------------------------------------------
  InfoMsg "$PROGM finished. (RC=0)"

exit 0
/ code for Linux/Unix/Windows

Related Posts

weiterlesen
17. Fachtagung „Datenbanksysteme für Business, Technologie und Web“ (BTW) März 2017
SQL playing with user id’s
Neuer DB2 Newsletter 2015/01
weiterlesen
ERRORCODE=-4220 – db2.jcc.charsetDecoderEncoder=3
IBM announced DB2® 10.5 for Linux™ , UNIX™ , and Windows™
weiterlesen
Das Ende von DB2 9.7 naht – dieses Jahr Upgrade auf 10.5 oder 11.1
weiterlesen
IBM DB2 Workshop für SAP Kunden
weiterlesen
IBM DB2 12 for z/OS Technical Overview – new draft Redbook

Comments

Comments are closed.