#!/bin/ksh
# set tabstop=4 to display correctly in vi
###
#  Name:		StopDB2.ksh 
#  Synopsis:	Emergency or out of hours DB2 instance and database DB2_SHUTDOWN for non-DBA staff.
#				The intention here is to provide a more robust and comprehensive 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 cannot stop until all databases under it are deactivated. 
#				2. For SQL Replication environments, ALWAYS stop DB2 at the apply_side server BEFORE you stop DB2 at the capture-side server.
#
#  Usage:		Run through root only directly at the database server. Pick one of:
#  				a) deactivate just one named database, leaving the DB2 instance up. 
#  					<path>/StopDB2.ksh <instance> <dbname> | tee <path>/StopDB2.ksh.<instance>.log ; rc =$?; echo $rc
#  	            b) deactivate all databases and shutdown the instance:
#  	  				<path>/StopDB2.ksh <instance> ALL | tee <path>/StopDB2.ksh.<instance>.log ; rc =$?; echo $rc 
#  	  			c) deactivate all databases and shutdown all instances at the server:
#  	  				<path>/StopDB2.ksh ALL ALL | tee <path>/StopDB2.ksh.ALL.log ; rc =$?; echo $rc 
#
#  Returns:		0  on success
#				>0 on error	; call a DBA and advise the server, the return code and location of tee'd logfile. Without these, you're on your own.
#
#				#!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
#				#
#				#   Most Important: A DB2 instance may support multiple databases.
#				#   --------------  Stopping the instance requires ALL databases are first deactivated.
#				#                   Please check you really want to do this first!
#				#					
#				#!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
#
# Maintenance:	1. This may not be industrial strength, if applications exhibit persistent
#				connect behaviour. That will require additional code to revoke CONNECT,
#				QUIESCE_CONNECT and possibly other elevated authority.
#				2. The $install_path variable will need amendment in line with any version upgrade
#
#  Author: Alex Levy t/a www.sustainablesoftware.net
#  Last update: April 2013
#
###

set -u		# assert all variables are defined 
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
job=$(basename $0 .ksh)
typeset db2_cmd_path=
typeset -i count=0 capture_count=0 apply_count=0
typeset -i rc=0 rc1=0
typeset -i num_sessions=99 loops=0
typeset me=$(whoami)
typeset cmd="print SQL replication does not run on this server."
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_SHUTDOWN {
# Assert the instance has not already 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 the list db directory command 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 4
fi

# Do not attempt to stop the instance if it's already stopped!
count=$(${db2_cmd_path}/db2gcf -s -i $instance | grep -c Available )
(( count == 0 )) && print ${BOLD} "$0 : Instance $instance is not available, so cannot be stopped." ${PLAIN}
(( count == 0 )) && exit 5
count=0

# 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 6
fi

# Stop SQL Replication first, but only if it's running! Also capture the command to restart it for the StartDB2.ksh script.
# There may of course be no replication.
# ps -eaf | grep asn | grep -v grep on a capture server produces output like: 
# db2inst1  1891  1889  0 Aug06 ?        00:04:04 asncap capture_server=SOURCE capture_schema=ASN capture_path=/database/replication/capture_files startmode=WARMNS 
# whilst ps -eaf | grep asn | grep -v grep on an apply produces output like:
# db2inst2 29051  1  2 Aug06 ?   01:23:09 /db2home/db2inst2/sqllib/bin/asnapply apply_qual=AQ00 control_server=TARGET apply_path=/database/replication pwdfile=asnpwd.aut

count=$(ps -eaf | grep asn | grep -v grep | wc -l)
if  (( count == 1 ))
then
	capture_count=$(ps -eaf | grep asn | grep capture_server | grep -v grep | wc -l)
	apply_count=$(ps -eaf | grep asn | grep control_server | grep -v grep | wc -l)
    if	  (( capture_count == 1 )) 
  	then  startcmd=$(ps -eaf | grep asn | grep -v grep | awk '{print "echo", $8, $9, $10, $11, $12,"| at now"}') 
		  print ${startcmd} > ~${instance}/.start_capture.cmd
		  stopcmd=$(ps -eaf | grep asn | grep -v grep | awk '{print "asnccmd", $9, $10, "stop"}' | head -1)
		  print ${stopcmd} > ~${instance}/.stop_capture.cmd
		  chmod 445 ~${instance}/.start_capture.cmd ~${instance}/.stop_capture.cmd
    fi 
    if    (( apply_count == 1 )) 
	then  startcmd=$( ps -eaf | grep asn | grep -v grep | awk '{print "echo ",$8, $9, $10, $11, $12, " | at now"}')
	 	  print ${startcmd} > ~${instance}/.start_apply.cmd
		  chmod 445 ~${instance}/.start_apply.cmd
          stopcmd=$(ps -eaf | grep asn | grep -v grep | awk '{print "asnacmd", $9, $10, "stop"}' | head -1)
		  print ${stopcmd} > ~${instance}/.stop_apply.cmd
		  chmod 445 ~${instance}/.start_apply.cmd ~${instance}/.stop_apply.cmd
    fi 
else
	stopcmd="print instance $instance is not currently using SQL replication."
fi

su - $instance -c "${stopcmd}"
rc=$?
if   (( rc > 0 ))
then print ${BOLD} "SQL Replication stop error. Please advise DBA." ${PLAIN}
     exit 10
fi

# Generate a database list for this instance, mapping aliases to the underlying database names and testing whether the database is local or remote;
# - commands like list active databases only display the db name, never the alias; utilities like db2pd do not accept alias names;
# - here, 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}.dbnames       # sequence dbname
db2 list db directory | egrep "Database alias" | awk -F"= " '{print NR,\$2}' > /tmp/\${job}.dbaliases     # sequence alias
db2 list db directory | egrep "entry type"     | awk -F"= " '{print NR,\$2}' > /tmp/\${job}.dbtypes       # sequence Indirect/Remote
join -o 1.1 1.2 2.2  /tmp/\${job}.dbnames /tmp/\${job}.dbtypes > /tmp/\${job}.dblist1                     # dbname   Indirect/Remote
join -o 1.1 1.2 2.2  /tmp/\${job}.dbaliases  /tmp/\${job}.dbtypes > /tmp/\${job}.dblist2                  # alias    Indirect/Remote
join -o 1.2 2.2 1.3  /tmp/\${job}.dblist1 /tmp/\${job}.dblist2 > /tmp/\${job}.dblist3                     # alias dbname Indirect/Remote
grep Indirect /tmp/\${job}.dblist3 | sort -u > /tmp/\${job}.dblist.sort
if   [[ -s /tmp/\${job}.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 11
fi

# Extract just the base database name(s) in scope
touch /tmp/${job}.dblist.final
while read name alias type
do
    if  [[ $dbname == "ALL" ]]
    then
        if   [[ $name == $alias ]]
        then echo $name >> /tmp/${job}.dblist.final
		fi
	else
        if   [[ $dbname == $name ]]
		then echo $dbname > /tmp/${job}.dblist.final
             break
        fi
    fi
done < /tmp/${job}.dblist.sort

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

# Force connections loop:
while (( num_sessions > 0 ))
do  
	if   [[ $dbname == "ALL" ]]
    then su - $instance -c "db2 -v force applications all"   
    else
		 > /tmp/connlist.$$
		 chmod 777 /tmp/connlist.$$
		 su - $instance -c "db2 list applications for $dbname global" | tail -n +5 | head -n -1 | awk '{print "force application(",$3,");"}' > /tmp/connlist.$$
		 if   [[ -s /tmp/connlist.$$ ]]
		 then su - $instance -c "db2 -txf /tmp/connlist.$$"
		 fi 
    fi
    sleep 5
    (( loops += 1 ))
    if  (( loops > 100 ))
    then
        # I'm tired of waiting
        print ${BOLD} "$0 :Applications cannot be forced - users are still connected. Exiting ..." ${PLAIN}
        exit 13
    fi
	if   [[ $dbname == "ALL" ]]
	then num_sessions=$(su - $instance -c "db2 list applications global" |  tail -n +5 | head -n -1 | wc -l)
	else num_sessions=$(su - $instance -c "db2 list applications for $dbname global" |  tail -n +5 | head -n -1 | wc -l)
    fi
done

# deactivate loop
for db in $(cat /tmp/${job}.dblist.final)
do
    su - $instance -c "db2 -v deactivate database $db"
	rc1=$?	
    (( rc1 > 2 )) && print "Failed to deactivate database ${db} " 
	(( rc1 <= 2 )) && rc1=0 # allow for database already deactivated, rc 2.
	(( rc = $? + rc1 ))     # cumulative return code
done

if  (( rc == 0 ))
then
	rm -f /tmp/${job}.*
else
    print ${BOLD} "$0 : Databases are still active, therefore the instance cannot be stopped." ${PLAIN}
    exit 14
fi 

# Stop the instance at each partition:
if  [[ $dbname == "ALL" ]]
then
	for part in $( cat ~${instance}/sqllib/db2nodes.cfg | awk '{print $1}' )
	do
    	${db2_cmd_path}/db2gcf -d -p $part -i $instance
    	(( rc = $? + rc ))
	done
	# tidy up any stray processes - probably not as effective as ipcrm:
	# note ${db2_cmd_path}/db2gcf -k -i $instance will not work if the instance is already stopped.
	su - $instance -c "ipclean"
	su - db2das1 -c "db2admin stop" > /dev/null
fi

(( rc == 0 )) && print "$0 : Program completes without any detected errors."
(( rc == 0 )) || print ${BOLD} "$0 : Program did not run successfully." ${PLAIN}
print "Output from ${db2_cmd_path}/db2gcf -s -i $instance :"
${db2_cmd_path}/db2gcf -s -i $instance
(( rc == 0 )) || exit 15
} # end DB2_SHUTDOWN

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

# Comment out DAS processing - it will now be redundant at most sites:
### Stop the DAS if and only if an instance stop was requested;
### Do not capture errors; future-proof for removal of DAS in future releases.
###if   [[ $dbname == "ALL" && $(id db2das1) ]]
###then
###	 if   [[ -f ~db2das1/das/bin/db2admin ]]
###	 then su - db2das1 -c "db2admin stop" > /dev/null 2>&1
###	 fi
###fi

exit $rc
