#!/bin/ksh
# set tabstop=4 to display correctly in vi
###
# Script:       StartDB2.ksh
# Synopsis:     Emergency or out of hours DB2 instance and database startup for non-DBA staff.
#				The intention here is to provide a more robust and comprehensive startup script than those that come with most vendor packages, IBM included. 
#
# 				Things you must understand first:
#				=================================
#				1. A host server can support 0-many DB2 instances.
#				   A DB2 instance is a database manager.
#				   Each DB2 instance can support 0-many databases.
#				   The DB2 instance must start before any databases under it can activate. 
#				2. For SQL Replication, ALWAYS start DB2 at the source capture server BEFORE you start DB2 at the target apply server.  
#
# Usage:        Run through root only directly at the database server. Pick one of:
#               a) to activate a single named database:
#               	<path>/StartDB2.ksh <instance> <dbname> | tee /tmp/StartDB2.<instance>.log ; rc=$?; echo $rc
#               b) to start a named DB2 instance and activate all databases under the instance:
#               	<path>/StartDB2.ksh <instance> ALL | tee /tmp/StartDB2.<instance>.log ; rc=$?; echo $rc 
#               c) to start all DB2 instances and activate all databases at the same server:
#               	<path>/StartDB2.ksh ALL ALL | tee /tmp/StartDB2.ALL.log ; rc=$?; echo $rc   
#
#  Returns:     0  on success
#               >0 on error : advise DBA of server name, return code and name and location of the tee'd logfile. Otherwise you're on your own.
#
# Alex Levy, t/a www.sustainablesoftware.net, April 2013.
###

set -u      # assert all variables are defined
typeset job=$(basename $0 .ksh)
typeset BOLD=$(tput smso)
typeset PLAIN=$(tput rmso)

# Check argument count:
(( $# == 2 )) || print ${BOLD} "Usage! I said $0 DB2instancename <DB2databasename|ALL>." ${PLAIN}
(( $# == 2 )) || exit 1
typeset -l instance=$1
typeset -u dbname=$2

# remaining variables
typeset db2_cmd_path=
typeset -i count=0
typeset -i rc=0 rc1=0
typeset -i num_sessions=99 loops=0
typeset me=$(whoami)
typeset install_path=/opt/ibm/db2/V9.7/instance

# Check root is running this - the db2gcf command requires either root or DB2 sysadm authority:
if   [[ $me == "root" ]]
then :
else
     print  ${BOLD} "$0 : This program is only designed to run from root. Sorry." ${PLAIN}
     exit 2
fi

# Check install_path - this will change at each version upgrade
if   [[ -d $install_path && -x $install_path/db2ilist ]]
then :
else
     print  ${BOLD} "$0 : DB2 has been upgraded from an earlier version. Please ask DBAs to amend \$install_path variable. Sorry." ${PLAIN}
     exit 3
fi

function DB2_STARTUP {
# Assert the instance has not been failed over; the tell-tale sign is if key directories such as sqllib are still under $INSTHOME.
# Further, test the path to DB2 commands must be executable.
# Execute the db2profile in the current shell (to run DB2 commands later)
db2_cmd_path=~$instance/sqllib/bin
if   [[ -d ${db2_cmd_path} && -x ${db2_cmd_path} && -x ~${instance}/sqllib/db2profile ]]
then . ~${instance}/sqllib/db2profile
else
     print ${BOLD} "$0 : It looks like DB2 instance $instance has failed over. Therefore it cannot be stopped at this server." ${PLAIN}
     # Check whether the instance exists
     count=$(${db2_cmd_path}/db2ilist | grep -c $instance | awk '{print $2}' )
     (( count == 1 )) || print ${BOLD} "$0 : DB2 instance $instance does not exist at this server - please check the spelling." ${PLAIN}
     exit 3
fi

# Place an eyecatcher in the instance's diagnostic log:
if   [[ -f ~${instance}/sqllib/db2dump/db2diag.log ]]
then cat >> ~${instance}/sqllib/db2dump/db2diag.log << END
     "========= $0 runs with arguments $* at $(date) ============="
END
else
     print ${BOLD} "$0 : Logic error \#1 - please tell Alex." ${PLAIN}
     exit 4
fi

# Do not attempt to start the instance if it's already started!
count=$(${db2_cmd_path}/db2gcf -s -i $instance | grep -c Available )
if   (( count == 1 ))
then print ${BOLD} "$0 : Instance $instance has already started. "  ${PLAIN}
else

	count=$(${db2_cmd_path}/db2gcf -s -i $instance | grep -c Operable )
	if   (( count == 1 ))
	then su - $instance -c db2start
     	 rc=$?
     	 if   (( rc > 0 ))
     	 then print ${BOLD} "$0 : Program did not start instance successfully." ${PLAIN}
          	  print "Output from ${db2_cmd_path}/db2gcf -s -i $instance :"
          	  ${db2_cmd_path}/db2gcf -s -i $instance
          	  exit 6
     	 fi
	else
     	 print ${BOLD} "$0 : Instance $instance is not operable, so database(s) cannot be activated. Evidence:" ${PLAIN}
     	 ${db2_cmd_path}/db2gcf -s -i $instance
     	 exit 7
	fi
fi

# Create a list of databases in scope, and validate each.
# We're only interested in local databases (entry type = 'Indirect'), not remotely catalogued databases for other instances.
cat > /tmp/${job}.generate_${instance}_dblist << END
#!/bin/ksh
job=$job
. ~${instance}/sqllib/db2profile
db2 list db directory | egrep "Database name"  | awk -F"= " '{print NR,\$2}' > /tmp/\${job}.${instance}.dbnames       # sequence dbname
db2 list db directory | egrep "Database alias" | awk -F"= " '{print NR,\$2}' > /tmp/\${job}.${instance}.dbaliases     # sequence alias
db2 list db directory | egrep "entry type"     | awk -F"= " '{print NR,\$2}' > /tmp/\${job}.${instance}.dbtypes       # sequence Indirect/Remote
join -o 1.1 1.2 2.2  /tmp/\${job}.${instance}.dbnames /tmp/\${job}.${instance}.dbtypes > /tmp/\${job}.${instance}.dblist1   # dbname Indirect/Remote
join -o 1.1 1.2 2.2  /tmp/\${job}.${instance}.dbaliases  /tmp/\${job}.${instance}.dbtypes > /tmp/\${job}.${instance}.dblist2    # alias Indirect/Remote
join -o 1.2 2.2 1.3  /tmp/\${job}.${instance}.dblist1 /tmp/\${job}.${instance}.dblist2 > /tmp/\${job}.${instance}.dblist3   # alias dbname Indirect/Remote
grep Indirect /tmp/\${job}.${instance}.dblist3 | sort -u > /tmp/\${job}.${instance}.dblist.sort
if   [[ -s /tmp/\${job}.${instance}.dblist.sort ]]
then # there is at least 1 local, catalogued database.
     return 0
else return 1
fi
END

chmod +x /tmp/${job}.generate_${instance}_dblist
su - $instance -c /tmp/${job}.generate_${instance}_dblist
if   (( $? > 0 ))
then print ${BOLD} "$0 :Instance $instance has no catalogued databases - check outputs under /tmp/${job}.\*" ${PLAIN}
     exit 8
fi

# Extract just the base database name(s) in scope
> /tmp/${job}.${instance}.dblist.final

while read name alias type
do
    if  [[ $dbname == "ALL" ]]
    then
        if   [[ $name == $alias ]]
        then echo $name >> /tmp/${job}.${instance}.dblist.final
        fi
    else
        if   [[ $dbname == $name ]]
        then echo $dbname > /tmp/${job}.${instance}.dblist.final
             break
        fi
    fi
done < /tmp/${job}.${instance}.dblist.sort

if   [[ -s /tmp/${job}.${instance}.dblist.final ]]
then :
else print ${BOLD} "$0 : No databases qualify for activation. Check arguments and outputs under /tmp/${job}.\*" ${PLAIN}
     exit 9
fi

# If the server or DB2 crashed, then the databases will need crash recovery.
# That is we should really be running RESTART, not ACTIVATE.
rc=0 ; rc1=0
for db in $(cat /tmp/${job}.${instance}.dblist.final)
do
    su - $instance -c "db2 -v activate database $db"
	rc1=$? 
	if   (( rc1 > 0 )) 
	then  su - $instance -c "db2 -v restart database $db" 
    	  (( rc = $? + rc1 ))
	else  (( rc = rc + rc1 ))
	fi
    wait
done

if    (( rc <= 2 ))
then  rm -f /tmp/{job}.*
	  rc=0	
      print "$job : Databases reactivated for instance $instance."
else
      print ${BOLD} "$job : Database activation was not successful for instance $instance but databases are \*probably\* useable.\nPlease save stdout for DBA attention." ${PLAIN}
fi

# Is this a replication server? If so, run the replication start script created by StopDB2.ksh
# Allow for earlier warning messages, return code 2 (e.g. database already activated SQL1611W).
if   [[ -f ~${instance}/.start_capture.cmd ]]
then chmod 555 ~${instance}/.start_capture.cmd
     print "$job : Starting SQL Replication (CAPTURE)."
     su - $instance -c ~${instance}/.start_capture.cmd
     (( rc = $? + rc ))
     (( rc > 2 )) && print ${BOLD} "$0 : Failed to restart capture-side SQL Replication correctly - seek DBA assistance." ${PLAIN}
fi
if   [[ -f ~${instance}/.start_apply.cmd ]]
then chmod 555 ~${instance}/.start_apply.cmd
print "$job : Starting SQL Replication (APPLY)."
su - $instance -c ~${instance}/.start_apply.cmd
(( rc = $? + rc ))
(( rc > 2 )) && print ${BOLD} "$0 : Failed to restart apply-side SQL Replication correctly - seek DBA assistance." ${PLAIN}
fi
} #end DB2_STARTUP

# Main
if   [[ $instance == "all" ]]
then
     for instance in $($install_path/db2ilist)
     do 
		print "$job : Working on instance ${instance}."
		DB2_STARTUP
     done
else DB2_STARTUP
fi

#Comment out DAS startup - it's no longer needed at most sites.
###Attempt to start the DAS, if there is one; edit the DAS name as required; do not capture errors:
###if   [[ $dbname == "ALL" && $(id dasadm1) ]]
###then
###     if   [[ -f ~dasadm1/das/bin/db2admin ]]
###     then su - dasadm1 -c "db2admin stop" > /dev/null 2>&1
###     fi
###fi

(( rc == 2 )) && rc=0
exit $rc

