#!/usr/bin/qsh
#
# Licensed Materials - Property of IBM
# (C) Copyright IBM Corp. 2004, 2010.  ALL RIGHTS RESERVED 
# 5724-I63, 5724-H88, 5655-N02, 5733-W70
# US Government Users Restricted Rights - Use, duplication, or disclosure
# restricted by GSA ADP Schedule Contract with IBM Corp.
#
#   CEI/ws/code/cei.configdb/src/dbconfig/sqltemplates/db2iseries/cr_event_db2iseries, WAS.cei.configdb, WASX.CEI
#   @Version 1.2 6/15/06
#   
#---------------------------------------------------------------------------
# Description:
# This script is intended to be called from the application or from the
# cr_event_db2 script to create the Event Infrastructure DB2 database 
# on iSeries
# Usage: cr_event_db2iseries dbUser dbPassword
# This script must be run locally on the target system if createDB=false
#
# To manually rerun scripts if createDB=true was initially passed 
# to the configEventServiceDB2iSeriesDB command copy the scripts 
# to the local machine and set "createDB=false" below. Sign on the local system
# and invoke as: cr_event_db2iseries dbUser dbPassword .
#
# If createDB=true and the toolbox jdbc driver is selected then a WRKRDBDIRE
# relational database entry must exist on both the local and remote machine 
# that matches the dbHostName. 
#
#---------------------------------------------------------------------------

#-------------------
#Function routines
#-------------------
lower()
{ 
    echo $1 | tr "[:upper:]" "[:lower:]" 
}

upper()
{ 
    echo $1 | tr "[:lower:]" "[:upper:]" 
}

getRDBname()
# Input: hostname
# Returns: Either RDB alias name (if exists) or Relational database name.
# Search RDB directory entries with hostname as input and return corresponding 
# RDB alias name (if exists) or Relational database name. The RDB alias or 
# Relational database name are passed to the "-r" parameter in the db2 -tvf command.
{

	hostname="$1"
	
	# Order of precendence for returned rdbname name matching hostname is - 
	# 1) rmtLocFound (Exact match of entered and remote location hostname)
	# 2) hostnamePartFound (Hostname entered is subset of remote location hostname)
	# 3) hostnameShortFound (Hostname entered is longer than remote location hostname)
	# 4) rdbNameFound (Hostname entered matches relational database name) 

	# upper case hostname
	hostnameUpper=$(upper $hostname)

	# select rdbname for exact hostname match
	i=0;
	db2 -t "SELECT DBXRDBN FROM qsys.qadbxrdbd WHERE UPPER(DBTXRLC) = '$hostnameUpper' ;" 2>&1  \
	| while read line
		do

		i=`expr $i + 1`
		if [ "$i" -eq "4" -a  -n "$line" ]; 
		then
			# 4th line contains query result
			echo $line
			return
		fi
	done	


	# Select rdbname where hostname is subset of remote location in rdb record.
	# User entered hostname only and the remote location is a fully qualified 
	# domain name. 
	i=0;
	db2 -t "SELECT DBXRDBN FROM qsys.qadbxrdbd WHERE UPPER(DBTXRLC) LIKE '$hostnameUpper.%' ;" 2>&1  \
	| while read line
		do

		i=`expr $i + 1`
		if [ "$i" -eq "4" -a  -n "$line" ]; 
		then
			# 4th line contains query result
			echo $line
			return
		fi
	done	


	#Check if IP address used as hostname. Returns 1st digit of ipaddr if found.
	ipaddr=$(echo $hostname | sed -n -e 's?^\([0-9]*\)\.\([0-9]*\)\.\([0-9]*\)\.\([0-9]*\).*?\1?p')

	if [ -z "$ipaddr" ]
	then
		# returns hostname portion of fully qualified domain name.
		hostnameShort=$(echo $hostname | sed -n -e 's?^\([A-Za-z0-9#$@_]*\)\.\([A-Za-z0-9#$@_]\).*?\1?p')
	fi

	# select rdbname where hostname is longer than remote location in rdb record
	if [ ! -z "$hostnameShort" ]
	then

		hostnameUpper=$(upper $hostnameShort)
		i=0;
		db2 -t "SELECT DBXRDBN FROM qsys.qadbxrdbd WHERE UPPER(DBTXRLC) = '$hostnameUpper' ;" 2>&1  \
		| while read line
			do

			i=`expr $i + 1`
			if [ "$i" -eq "4" -a  -n "$line" ]; 
			then
				# 4th line contains query result
			 	echo $line
			    return
			fi
		done
	fi


	# select rdbname where hostname = rdbname
	i=0;
	db2 -t "SELECT DBXRDBN FROM qsys.qadbxrdbd WHERE UPPER(DBXRDBN) = '$hostnameUpper' ;" 2>&1  \
	| while read line
		do

		i=`expr $i + 1`
		if [ "$i" -eq "4" -a  -n "$line" ]; 
		then
			# 4th line contains query result
			echo $line
			return
		fi
	done

	#Return hostnameif not found
	echo $hostname
	
} #end function

#---------------------------------------------------------------------------
# Main start
#---------------------------------------------------------------------------

#Always use invoked user id and password
if [ $# -ge 2 ]
then
    DB_USERID=$1
    DB_PASSWORD=$2
fi

COLLECTION=##collection#
dbName="*LOCAL"
dbName=##DB_NAME#
dbHOSTNAME="*LOCAL"
dbHOSTNAME=##dbHostName#
rdbHOSTNAME=*LOCAL
createDB=false
createDB=##createDB#

toolboxDriver=false
toolboxDriver=##toolboxJdbcClassPath#
ntvJdbcParms=""
ntvJdbcParms="-r ##DB_NAME#  -u $DB_USERID  -p $DB_PASSWORD "

CURRENT_DIR=`dirname $0`
if [ "$CURRENT_DIR" = "." ] ; then
   CURRENT_DIR=`pwd`
fi

#Upper/lower case vars for db2 command and string compares
createDB=$(lower $createDB)
dbName=$(upper $dbName)
dbHostNameUpper=$(upper $dbHOSTNAME)
DB_USERID=$(upper $DB_USERID)

#Derive db2 command parameters
if [ $toolboxDriver != "false" ]  #toolbox jdbc driver selected
then
    if [ $dbName != "*LOCAL" -a  $dbName != "*SYSBAS" ]
    then
        #Use dbName if given for -r parameter. Must be in WRKRDBDIRE list.
	toolboxJdbcParms="-r $dbName  -u $DB_USERID  -p $DB_PASSWORD "
    elif [ $createDB = "false" -o  $dbHostNameUpper = "LOCALHOST" -o $dbHostNameUpper = "LOOPBACK" -o $dbHostNameUpper = "127.0.0.1" ]
    then
        #Generic hostname provided so use defaults for local or remote targets
        toolboxJdbcParms=""    
    else
        #Useable hostname provided. Must be in WRKRDBDIRE list.
        #Invoke getRDBname() to obtain matching rdbname for hostname
        rdbParm=$(getRDBname $rdbHOSTNAME)
        toolboxJdbcParms="-r $rdbParm  -u $DB_USERID  -p $DB_PASSWORD "
    fi
fi

#Remove password from log
echoString=$(echo "$ntvJdbcParms" | sed -e "s!$DB_PASSWORD!******!g")  #  ##nativeJdbcClassPath# 
echoString=$(echo "$toolboxJdbcParms" | sed -e "s!$DB_PASSWORD!******!g")  #  ##toolboxJdbcClassPath# 


#The return code from the script
rc=0

#Collection creation flag
collection_created=0

echo  "db2 -v -t -f $CURRENT_DIR/cr_collection.db2 $echoString"
db2 -v -t -f $CURRENT_DIR/cr_collection.db2 $ntvJdbcParms  #  ##nativeJdbcClassPath#
db2 -v -t -f $CURRENT_DIR/cr_collection.db2 $toolboxJdbcParms  #  ##toolboxJdbcClassPath#
rc=$?

if [ $rc -eq 0 ] ; then
   collection_created=1
fi

if [ $rc -eq 0 ] ; then
   echo  "db2 -v -t -f $CURRENT_DIR/cr_tbl.db2 $echoString"
   db2 -v -t -f $CURRENT_DIR/cr_tbl.db2 $ntvJdbcParms  #  ##nativeJdbcClassPath#  
   db2 -v -t -f $CURRENT_DIR/cr_tbl.db2 $toolboxJdbcParms  #  ##toolboxJdbcClassPath# 
   rc=$?
fi

if [ $rc -eq 0 ] ; then
   echo  "db2 -v -t -f $CURRENT_DIR/cr_tbl_catalog.db2 $echoString" 
   db2 -v -t -f $CURRENT_DIR/cr_tbl_catalog.db2 $ntvJdbcParms  #  ##nativeJdbcClassPath#
   db2 -v -t -f $CURRENT_DIR/cr_tbl_catalog.db2 $toolboxJdbcParms  #  ##toolboxJdbcClassPath#
   rc=$?
fi

if [ $rc -eq 0 ] ; then
   echo  "db2 -v -t -f $CURRENT_DIR/ins_metadata.db2 $echoString"
   db2 -v -t -f $CURRENT_DIR/ins_metadata.db2 $ntvJdbcParms  #  ##nativeJdbcClassPath# 
   db2 -v -t -f $CURRENT_DIR/ins_metadata.db2 $toolboxJdbcParms  #  ##toolboxJdbcClassPath# 
   rc=$?
   # Suppress warning about FETCH, UPDATE or DELETE; or the result of a
   # query is an empty table
   if [ $rc -eq 2 ] ; then
      rc=0
   fi
fi

if [ $rc -eq 0 ] ; then
   echo  "db2 -v -t -f $CURRENT_DIR/catalogSeed.db2 $echoString"
   db2 -v -t -f $CURRENT_DIR/catalogSeed.db2 $ntvJdbcParms  #  ##nativeJdbcClassPath# 
   db2 -v -t -f $CURRENT_DIR/catalogSeed.db2 $toolboxJdbcParms  #  ##toolboxJdbcClassPath# 
   rc=$?
   # Suppress warning about FETCH, UPDATE or DELETE; or the result of a
   # query is an empty table
   if [ $rc -eq 2 ] ; then
      rc=0
   fi
fi

if [ $rc -eq 0 ] ; then
   # Added call to db2 cmd with -d option.
   echo  "db2 -v -d -f $CURRENT_DIR/cr_stpr.db2 $echoString" 
   db2 -v -d -f $CURRENT_DIR/cr_stpr.db2 $ntvJdbcParms   #  ##nativeJdbcClassPath#    
   db2 -v -d -f $CURRENT_DIR/cr_stpr.db2  $toolboxJdbcParms  #  ##toolboxJdbcClassPath#    
   rc=$?
fi

echo "GRTOBJAUT OBJ($COLLECTION) OBJTYPE(*LIB) USER($DB_USERID) AUT(*ALL) REPLACE(*YES)" 
system -q "GRTOBJAUT OBJ($COLLECTION) OBJTYPE(*LIB) USER($DB_USERID) AUT(*ALL) REPLACE(*YES)"  
system -q "GRTOBJAUT OBJ($COLLECTION/*ALL) OBJTYPE(*ALL) USER($DB_USERID) AUT(*ALL) REPLACE(*YES)"  

exit $rc

