SETIDENTITY() {
# Procedure to set the next value for an identity column after a load.
# 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 is $DBSCHEMA (set in $INSTHOME/sqllib/userprofile)
#           $2 table name  - ditto 
#           $3 ksh debug switch (+x or -x)
# e.g. SETIDENTITY $DBSCHEMA EMPLOYEE +x. 
# Edit the WORKDIR temporary work directory as required.
# Alex Levy, 27.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 SETIDENTITY $*" PLAIN I $DEBUG 
 else
    MESSAGE "SETIDENTITY() Bad Call! \n Usage SETIDENTITY \$1 schema name \$2 table name \$3 ksh debug switch (+x or -x)\n" PLAIN E +x
    return 1 
 fi    

 WORKDIR=$INSTHOME/tmp  # Edit this
 DIR_CHK $WORKDIR $DEBUG  
 typeset -i RC=$? ; [[ $RC -gt 0 ]] && return $RC 
 LOGDATE=$(date +"%Y%m%d:%H:%M:%S")
 DRIVER=$WORKDIR/SETIDENTITY.$TARGET.$LOGDATE
 
 db2 -x "select colname from syscat.columns where tabschema = '${SCHEMA}' and tabname = '${TABLE}' and identity = 'Y' for fetch only" > $DRIVER
 RC=$?
 # allow for no identity columns (RC=1)
 if [[ $RC -gt 1 ]]
 then
    MESSAGE "SETIDENTITY() :Bad return code $RC selecting identity column list for $TARGET. Exiting ..." PLAIN E $DEBUG 
    return $RC
 fi
 
 if [[ -s $DRIVER ]]
 then
    while read COLUMN
    do
      maxvalue=$(db2 -x "select coalesce(max($COLUMN),0) +1 from $TARGET ")
      RC=$?
      if [[ $RC -gt 0 ]]
      then
         MESSAGE "SETIDENTITY() :Bad return code $RC selecting next value from $TARGET column $COLUMN. Exiting ..." PLAIN E $DEBUG 
         return $RC
      fi
      db2 -v "alter table ${TARGET} alter column ${COLUMN} set generated always restart with $maxvalue" 
      RC=$?
      if [[ $RC -gt 0 ]]
      then
         MESSAGE "SETIDENTITY() :Bad return code $RC altering identity column $COLUMN, table $TARGET with restart value ${maxvalue}. Exiting ..." PLAIN E $DEBUG
         return $RC
      fi
    done < $DRIVER
    return 0
else
    MESSAGE "SETIDENTITY() :No identity columns for table $TARGET." PLAIN I DEBUG
    return 0
fi
}
