LOAD() {
# Function to run an unlogged DB2 load utility in a choice of modes (REPLACE, INSERT or TERMINATE), using comma delimited input files.
# Includes error checking which will issue a load terminate by recursive call. 
#
# Supply a value for $MSGPATH
#
# Arguments $1 load file path and name 
#           $2 lob file path and name 
#           $3 fully qualified tablename 
#           $4 mode 
#           $5 ksh debug switch (+x or -x),
#           $6 return code from failed load, reserved for recursive call
# e.g. LOAD $HOME/data.del $HOME/lobs/lob1.001 myschema.mytable replace -x. 
#
# The lobfile can be any zero byte, readable file for tables without LOB data.
# Assumes the caller has established a current database connection in the same shell.
# Note that argument checking is minimal, as I'm coding all the calls!
# Handy tip: REPLACE with a zero length file is the same as a 'truncate'.
# Alex Levy, 14.11.2007 http://www.sustainablesoftware.net

 MSGPATH=${HOME}/msg		# <== edit this
 
 if [[ $# -eq 5 || $# -eq 6 ]]
 then
    DEBUG=$5 ; set $DEBUG
    DIR_CHK `dirname $1` $DEBUG
    DIR_CHK `dirname $2` $DEBUG
    EXISTENCE_TEST $1 $DEBUG
    EXISTENCE_TEST $2 $DEBUG 
    typeset -u FULLTABLENAME=$3 MODE=$4
    SCHEMA=${FULLTABLENAME%%.*}
    TABLE=${FULLTABLENAME##*\.}
    case $MODE in
         REPLACE|INSERT|TERMINATE) : 
             ;;
         *) MESSAGE "LOAD() $4 is an invalid mode." PLAIN E $DEBUG
            return 4 
             ;;
    esac
    DIR_CHK $MSGPATH $DEBUG
    typeset -i LOADRC=${6:-0} # Assign the value of $6 to $LOADRC if it's defined (by a previous load failure); otherwise zero.
 else
    MESSAGE "LOAD() Bad Call! \n Usage LOAD $1 load file path and name $2 lob file path and name $3 qualified tablename $4 mode $5 ksh debug switch (+x or -x)\n where mode is one of |REPLACE|INSERT|TERMINATE|" PLAIN E +x
            return 4 
 fi    
 
 typeset -i IDENT_TABLE=$(db2 -x "select count(*) from syscat.columns where tabschema = '$SCHEMA' and tabname = '$TABLE' and IDENTITY = 'Y' for fetch only")
 db2 "commit" > /dev/null 
 if [[ $IDENT_TABLE -gt 0 ]]
 then OPTIONS=" lobsinfile identityoverride"
 else OPTIONS=" lobsinfile"
 fi

 db2 -v "load from $1 of del  \
         lobs from $2         \
         modified by $OPTIONS \
         messages $MSGPATH/${TABLE}.${LOGDATE}.${MODE}.msg \
         ${MODE} into ${FULLTABLENAME} nonrecoverable without prompting lock with force" 
 RC=$?
 LOADRC=$(BIGGER $LOADRC $RC $DEBUG)
 if [[ $LOADRC -gt 0 ]]
 then 
      if [[ $MODE = "TERMINATE" ]]
      then 
         db2 -v rollback
         db2 -v list utilities show detail 
         return $LOADRC
      fi
      MESSAGE "$0 :Load failure, please review $MSGPATH/${TABLE}.${LOGDATE}.TERMINATE.msg" PLAIN E $DEBUG
      # call yourself in TERMINATE mode! 
      LOAD $1 $2 $3 TERMINATE $5 $LOADRC
 else
      # release any locks etc.
      db2 -v commit
      return $LOADRC
 fi
}
