#!/bin/ksh
# Synopsis:		Compare the DDL structures of 2 databases.
# 				Initial version covers schemata, tables and views, indexes, constraints, routines and triggers; it is easily extensible to other artefacts
# 				and the attributes for comparison are also easily modified according to local site needs.
#
# Arguments: 	$1 database alias on local server: this is where work tables are built.
#			 	$2 second database alias for comparison: can be remote, e.g.
#			 	Compare_Databases.ksh localdb1 localdb2 or
#			 	Compare_Databases.ksh localdb1 "remotedb user xxx using zzz"
#
# Returns:	 	0:  	no differences found
#			 	1:  	differences found
#			 	2-16: 	SQL return code - refer to $LOG for stdout from DB2.
#			 	99  :   ksh or call error
#
# Prereqs:	 	FUNCTION library installed at $FPATH, downloadable from http://www.sustainablesoftware.net.downloads.htm
#
# Custom build: 1. Simply add or replace elements in the 'associative' arrays for {objects, attributes and predicates}.
#				Objects whose attributes are sourced from multiple catalog tables do not fit into this scheme -
#				use the code for constrraints as a template.
#				2. The code below uses USERPACE1 because it is ubiquitous. Modify the tablespace name to any 4K (or larger pagesize) single partition tablespace at the catalog partition.
#
# Future     :  1. Remove select of colnames from syscat.indexes, once the column is no longer populated (post V10.1).
# Maintenance   Will instead need to join to syscat.indexcoluse for a serialised list of index column names, which is a bit tedious. 
#				2. All work tables are catalogued, because declared and created temporary tables do not support LOAD or IMPORT at V9.7 - this may change in higher releases.
#				3. This is not realised as a stored procedure as connections are required to two separate databases; however it could be if the two databases were federated.
#
# Alex Levy t/a www.sustainablesoftware.net, 30.04.2013 

set -u
script=$(basename $0 .ksh)
typeset LOG=/tmp/${script}/LOG
typeset -l current_function
typeset -i RC=0
typeset -i count1=0 count2=0 num_differences=0
typeset BOLD=$(tput smso)
typeset PLAIN=$(tput rmso)

set -A objects schemata tables columns indexes routines triggers # constraints are dealt with outside the array structure
typeset -i schemata=0 tables=1 columns=2 indexes=3 routines=4 triggers=5
set -A attributes	" current server as dbname, schemaname, ownertype, definertype, remarks" \
					" current server as dbname, tabschema, tabname, type, remarks" \
					" current server as dbname, tabschema, tabname, colno, colname, typename, length, scale, default, nulls, rowchangetimestamp, generated, remarks" \
					" current server as dbname, indschema, indname, tabschema, tabname, colnames, uniquerule, colcount, indextype, pctfree, minpctused, reverse_scans" \
					" current server as dbname, routineschema, routinename, routinetype, origin, functiontype, parm_count, language, fenced, federated, valid, coalesce(length(text),0) as length" \
					" current server as dbname, trigschema, trigname, tabschema, tabname, valid, trigtime, trigevent, granularity"
set -A predicates	" where   schemaname <> current schema " \
					" where   type in ('A','N','S','T','V') and tabschema <> current schema " \
					" where   tabschema <> current schema "  \
					" where   indschema <> current schema "  \
					" where   routineschema <> current schema " \
					" where   trigschema <> current schema " \

	
(( $# == 2 )) || F_ERROR_MESSAGE "$0 expects 2 arguments but received $#" +x
(( $# == 2 )) || exit 99
typeset -u dbalias1=$1
typeset -u dbalias2=$2

# remove file leftovers from previous run and create a working directory
rm -fr /tmp/${script} > /dev/null
mkdir -m 755 /tmp/${script}

function report_error_in_function {
typeset db=$1
typeset tabname=$2
typeset RC=$3
F_ERROR_MESSAGE "${script}.${current_function}: error on db $db for table $tabname, return code ${RC}." +x
exit $RC
}

function DB2_RC_CHECK { 
typeset -i RETCODE=$1 MAXRC=$2  
LOCATION=$3   
MSG=$4 
if (( RETCODE > MAXRC ))
then  
   F_ERROR_MESSAGE "$MSG return code $RETCODE at program location $LOCATION." +x
   print "Exiting ... "
   db2 -v terminate 
   exit $RETCODE 
fi 
} 

function EXPORT_CATALOG {
(( $# == 1 )) || F_ERROR_MESSAGE "$0 expects 1 argument but received $#" +x
(( $# == 1 )) || exit 1
typeset -l dbname=$1
typeset -u dbalias=$(echo $dbname | awk '{print $1}')	# $dbname without any user ... using ... string
typeset -i RC=0
current_function=$0
db2 -v "connect to $dbname" >> $LOG
RC=$?; (( RC == 0 )) ||  F_ERROR_MESSAGE "$0 connection error to $dbalias" +x 
       (( RC == 0 )) ||  exit $RC

db2 -v "update command options using v on" >> $LOG
for	i in ${objects[*]}
do
	db2 -v "export to /tmp/${script}/${dbalias}.${i}.del of del \
			select 	${attributes[i]}	\
			from 	syscat.${i}			\
			${predicates[i]}    		\
			order by 1,2,3     			\
			for fetch only with ur" >> $LOG
	RC=$?; (( RC <= 2 )) || report_error_in_function $dbalias syscat.${i} $RC
done

db2 -v "export to /tmp/${script}/${dbalias}.constraints.del of del 		\
	select 	current server, k.constname, k.tabschema, k.tabname, 		\
			k.colname, k.colseq, t.type, t.enforced, t.enablequeryopt	\
     from   syscat.keycoluse k											\
                  inner join											\
            syscat.tabconst  t											\
                  on  k.constname = t.constname							\
                  and k.tabschema = t.tabschema 						\
                  and k.tabname = t.tabname								\
	where 	k.tabschema <> current schema								\
			UNION														\
	select 	current server, constname, tabschema, tabname, '0', '0', 	\
			type, enforced, enablequeryopt								\
    from   syscat.tabconst												\
	where 	tabschema <> current schema 								\
	order by constname, tabschema, tabname, colname						\
	for fetch only with ur " >> $LOG
RC=$?; (( RC <= 2 )) || report_error_in_function $dbalias syscat.tabconst $RC

}

function DEFINE_WORK_TABLES {
typeset -i RC=0
current_function=$0
db2 -v "update command options using v on" >> $LOG
db2 -v "connect to $dbalias1" >> $LOG
RC=$?; (( RC == 0 )) ||  F_ERROR_MESSAGE "$0 connection error to $dbalias1" +x 
       (( RC == 0 )) ||  exit $RC
db2 -v "create schema DBCOMPARE" >> $LOG
RC=$? ; DB2_RC_CHECK $RC 0 10 "Unable to create schema DBCOMPARE at db ${dbalias1}."

for	i in ${objects[*]}
do
	db2 -v "create table dbcompare.${i} as (					\
			select 	${attributes[i]}	from syscat.${i}		\
			) definition only in userspace1" >> $LOG
	RC=$?; (( RC == 0 )) || report_error_in_function $dbalias1 dbcompare.${i} $RC
done

db2 -v "create table dbcompare.constraints as (								\
	select 	current server as dbname, k.constname, k.tabschema, k.tabname, 	\
			k.colname, k.colseq, t.type, t.enforced, t.enablequeryopt	\
     from   syscat.keycoluse k											\
                  inner join											\
            syscat.tabconst  t											\
                  on  k.constname = t.constname							\
                  and k.tabschema = t.tabschema 						\
                  and k.tabname = t.tabname								\
	where 	k.tabschema <> current schema								\
			UNION														\
	select 	current server, constname, tabschema, tabname, '0', '0', 	\
			type, enforced, enablequeryopt								\
    from   syscat.tabconst												\
	where 	tabschema <> current schema 								\
	order by constname, tabschema, tabname, colname	)					\
	definition only in userspace1" >> $LOG
RC=$?; (( RC == 0 )) || report_error_in_function $dbalias dbcompare.constraints $RC
}

# Main
print "$script starts at $(date) \nPlease be patient,I've a lot of work to do ..."
F_SETUP_DB2_ENVIRONMENT $HOME $USER $DBALIAS +x > $LOG
for db in $dbalias1 $dbalias2
do
	EXPORT_CATALOG $db
done

DEFINE_WORK_TABLES

current_function=main
for export in ${objects[*]} constraints
do 
	# populate work tables
	cat /tmp/${script}/*.${export}.del >> /tmp/${script}/${export}.del
	db2 -vm "import from  /tmp/${script}/${export}.del of del replace into dbcompare.$export" >> $LOG
	RC=$?; DB2_RC_CHECK $RC 1 11 "Import error for table  dbcompare.${export}."
done

dbalias2=$(echo $dbalias2 | awk '{print $1}')

print -- "------------------------------------------------------------------"
print    "Find all objects which differ between databases.                  "
print    "ADDED rows are present in $dbalias1 but not in ${dbalias2}.       "
print    "DELETED rows are present in $dbalias2 but not in ${dbalias1}.     "
print    "Rows which are both ADDED and DELETED do not match in definition. "
print -- "------------------------------------------------------------------\n"

for object in ${objects[*]} constraints
do 
	print -- "-----------------------------------------------------"
	print    " Basic $object comparisons for $dbalias1 and ${dbalias2}. "
	print -- "-----------------------------------------------------"
	print "\n"

	count1=$(db2 -x "select count(*) from dbcompare.$object where dbname = '$dbalias1'")
	count2=$(db2 -x "select count(*) from dbcompare.$object where dbname = '$dbalias2'")
	print "There are $count1 $object in $dbalias1 and $count2 $object in $dbalias2"

	db2 -x "select 	'ADDED',o.*				\
			from 	dbcompare.${object} o	\
			where 	dbname = '$dbalias1'	\
					EXCEPT					\
			select 	'ADDED',o.*				\
			from 	dbcompare.${object} o	\
			where 	dbname = '$dbalias2'	\
					UNION   				\
			select 	'DELETED',o.*			\
			from 	dbcompare.${object} o	\
			where 	dbname = '$dbalias2' 	\
					EXCEPT					\
			select 	'DELETED',o.*			\
			from 	dbcompare.${object} o	\
			where 	dbname = '$dbalias1'"
	RC=$? ; DB2_RC_CHECK $RC 1 12 "Query error against dbcompare.schemata"
	(( RC == 1 )) && print "Schemata in the 2 databases are identical in number and definitions match for all compared attributes."
	(( RC == 0 )) &&  (( num_differences += 1 ))
done

db2 -v terminate >> $LOG

# test num_differences, drop dbcompare schema if cool
if	 (( num_differences == 0 )) 
then print "\nDatabases $dbalias1 and $dbalias2 appear to have identical structures across all measurements."
	 print "Storage paths, tablespaces, containers, users, groups, roles and grants have not been compared." 
	 print "Now tidying up ..."
	 rm -fr /tmp/${script}
	 db2 -v "call sysproc.admin_cmd('DBCOMPARE',NULL,'ERRORSCHEMA','ERRORTAB')"
	 print "$script : All done."
	 exit 0
else
	 print "\nDifferences detected across $num_differences object types."
	 print "Leaving DBCOMPARE tables and export directory /tmp/${script} in place to aid investigation." 
	 print "Please tidy up afterward." 
	 print "\nPlease note differences in system-generated object names are to be expected." 
	 print "Also expect small differences in the byte-length of routines if they are built from db2look output." 
	 exit 1
fi
