#!/bin/ksh  
#
# Scriptname  : $II_SYSTEM/ingres/scripts/db_status.ksh
#
# Description : Lists the status of all databases on a given installation 
#               and checks the availability of the dbms servers.
#
# Parameters  : None.
#
# Author      : M S Ramsay 26 October 1999
#
# History       Date      Name      Reason
#               ----      ----      ------
#               dd/mm/yy  xxx       xxxxxxxxxxxxxxxxxxxxxxxx
#
############################################################################
#
#Parameter validation and set-up.
#

set +A SDS_dbinfo   #Array variable
SDS_scriptname="`basename $0`" 
SDS_runtype=${1:-}

if [[ $SDS_runtype != '' ]] && [[ $SDS_runtype != 'h' ]]
then echo "" 
     echo "ERROR in script $SDS_scriptname - Variable \$1 is invalid.  Should not be $1."
     echo ""
     echo "Try:-"
     echo ""
     echo "h - Help"
     echo ""
     exit
fi

if [[ $SDS_runtype = 'h' ]] 
then echo
     echo "SCRIPT DB_STATUS.KSH."
     echo "====================="
     echo 
     echo
     echo "Description:-"
     echo ""
     echo "This script checks the status of the DBMS servers  registered"
     echo "with the name server.  It checks the number of DBMS processes"
     echo "with the number  of NAME server  registrations and  it checks"
     echo "the availability of each database."
     echo ""
     echo "Parameters:- "
     echo ""
     echo "h - Help"
     echo ""
     exit
fi

clear

#
#Determine the number of DBMS servers running.
#

SDS_servers=$(iinamu <<EOT | sed -e '/SERVICE/d;/Copyright/d' |awk -F\* '{print $2}' |awk '{print $1}'
show 
EOT)

SDS_server_count=0
echo "The following DBMS servers are registered with the NAME server...."
echo

for f in $SDS_servers
do
SDS_server_count=$((SDS_server_count + 1))
echo $f
done
echo

#
#Check the name server entries with the number of DBMS processes.
#

SDS_ii_installation="`ingprenv |grep II_INSTALLATION | awk -F\= '{print $2}'`"
SDS_proc_count="`ps -ef |grep $SDS_ii_installation |grep dbms |wc -l`"

if [[ $SDS_server_count != $SDS_proc_count ]]
	then echo "***********************************************************"
	     echo "***** The number of DBMS processes running does not   *****"
	     echo "***** match the number of entries in the name server. *****"
	     echo "***** This could indicate a serious problem with      *****"
	     echo "***** Ingres.  Please notify DBA immediately.         *****"
	     echo "***********************************************************"
	     echo
	     echo "Continuing with database and DBMS server availability check....."
	     echo
fi

#
# Save current value of II_DBMS_SERVER
#

SDS_old_value=`echo $II_DBMS_SERVER`

#
# Check status of each DBMS Server.
#

for f in $SDS_servers
do
printf "%-30" "Checking status of server $f.........."
export II_DBMS_SERVER=$f
sql iidbdb <<EOT 1>/dev/null           
EOT
        if [[ $? = 0 ]]
        then print "Available"
        else print "Not Responding."
	     echo
	     echo
	     echo
             echo "***********************************************************" 
	     echo "***** It seems that at least one DBMS server is       *****" 
             echo "***** unavailable.  This could cause problems for     *****"
             echo "***** users of this installtion.  Please notify       *****"
             echo "***** DBA immediately.  Once the problem has been     *****"
             echo "***** fixed re-run this script.                       *****"
             echo "***********************************************************" 
	     exit
        fi
done

export II_DBMS_SERVER=$SDS_old_value

echo
echo "Starting Database availability check...."
echo

#
#Get a list of all databases on the current installation.
#

SDS_dbname=$(sql  iidbdb <<EOT | awk -F\| '{ print $2 }' | sed -e '/db_name  /d;/^$/d' 
select name as db_name,own 
from iidatabase
where dbservice !=1
order by db_name ; \g    
EOT)

#Do not remove the order by.  

#
#Get a list of all owners on the current installation.
#

SDS_dbown=$(sql  iidbdb <<EOT | awk -F\| '{ print $3 }' | sed -e '/owner/d;/^$/d' 
select name,own as owner
from iidatabase
where dbservice !=1
order by name ; \g     
EOT)
#Do not remove the order by.  

#
#Place Database owners into SDS_dbinfo array.
#

if [[ $SDS_dbown = '' ]]
	then echo "***********************************************************" 
	     echo "***** It seems that all databases are unavalible.     *****"
             echo "***** This could indicate a serious problem with      *****"
             echo "***** Ingres.  Alternatively, this script may have    *****"
             echo "***** encountered a scenario that it does not         *****"
             echo "***** recognise.  Please contact  DBA immediately.    *****"
             echo "***********************************************************"
	     exit
fi

SDS_dbown_count=0      #Array counter.

for f in $SDS_dbown
do
SDS_dbown_count=$((SDS_dbown_count + 1)) #Increment counter
SDS_dbinfo[${SDS_dbown_count}]=$f        #Set array elements
done

#
#Set-up column headings
#

printf "%-24s" "Database"                                                       
printf "%-24s" "Owner"                                                          
print  "Status"
echo "" 

#
#Display Database, Db Owner and Database avalability status.
#

SDS_dbinfo_count=0      #Array counter.

for i in $SDS_dbname
do
	SDS_dbinfo_count=$((SDS_dbinfo_count + 1)) #Increment counter
	printf "%-24s" ${i}
	printf "%-24s" ${SDS_dbinfo[${SDS_dbinfo_count}]}
	sql -u${SDS_dbinfo[${SDS_dbinfo_count}]} $i <<EOT 1>/dev/null
EOT
	if [[ $? = 0 ]]
	then print "Available"
	else print "Not Responding. Contact DBA."
	fi
done