#!/bin/ksh
# 

# Description 	Many Data Warehouses declare referential constraints for query optimization but do not enforce them due to overhead. 
#				These are sometimes called 'informational constraints' and application logic is responsible for ensuring consistency.  
#				This utility will generate and run a complete set of SQL statements to check for referential constraint violations 
#				in application tables in a given schema. The system will only do this automatically if RI constraints are enforced. 
#							
# Returns		: 0 (success)
#				 >0 (failure, including detected integrity violations)		
#
# Example usage: INTEGRITY_CHECK.ksh dbname applicationschema_to_be_checked e.g.
#				 INTEGRITY_CHECK.ksh sample myschema
# Maintenance	: 1. The logic below assumes the primary key is a single column. Quite often in modern data warehouses,  
#				  that will be an identity column.
#				  Extra code could be added to cater for composite primary keys, at the expense of some complexity.
#			 	: 2. The script could easily extend to a quoted schema list, e.g. "'schema1','schema2',...'schema99'".
#				  In that case, simply change ... = '${SCHEMA}' to ... IN '${SCHEMALIST}'
#
# Author		Alex Levy t/a Sustainable Software Ltd. http://www.sustainablesoftware.net
#				June 2010, copyleft conditions apply.
#
###

typeset -u DB SCHEMA
typeset -i FINAL_RC=0 INVALID_OBJECT_COUNT
WORKFILE=/tmp/RI_CHECK.$$ 
BOLD=$(tput smso) ; PLAIN=$(tput rmso)

DB2_RC_CHECK () {
RC=$1 ; MAXRC=$2; MSG=$3
if   [[ $RC -gt $MAXRC ]]
then print ${BOLD} "$0 : DB2 Error, message is $MSG .Check stdout, stderr and $WORKFILE. Must exit" 
     print Please remember to delete ${WORKFILE} when done. ${PLAIN}
     exit $RC
fi
}

if [[ $# -eq 2 ]]
then typeset -u DB=$1 SCHEMA=$2 
else
	print ${BOLD} "Usage! I said: $0 dbname application_schema e.g. $0 sample myschema" ${PLAIN} && exit 1
fi

db2 connect to $DB 		
RC=$? ; DB2_RC_CHECK $RC 0 "Connection error"

#	Uncomment if you have a V9.7 client and server
#db2 -v "call sysproc.admin_revalidate_db_objects(NULL,'$SCHEMA1',NULL)"
#RC=$? ; DB2_RC_CHECK $RC 0 "Call error to SYSPROC.admin_revalidate_db_objects."

typeset -i INVALID_OBJECT_COUNT=$(db2 -x "select count(*) from syscat.invalidobjects where objectschema = '$SCHEMA" )
RC=$? ; DB2_RC_CHECK $RC 1 "Query error against SYSCAT.INVALIDOBJECTS."
if [[ $INVALID_OBJECT_COUNT -gt 0 ]]
then print ${BOLD} "$0: Please check syscat.invalidobjects for $SCHEMA. Exiting without any further checks ..." ${PLAIN}
	 exit 2
fi

#		Generate SQL integrity checks for all child tables in the presentation schema.
#		SYSCAT.REFERENCES contains the dependency from child to parent. 
#		SYSCAT.KEYCOLUSE contains one row per foreign key column and one row per primary key column for each ref constraint.
#		We query the KEYCOLUSE view twice because there is no guarantee or restriction that the child and parent column names 
#       will always match in name, though best practice is that they normally do.

db2 -x "	select	'select distinct child.', childcol, 							\
			' from ', rtrim(childschema||'.'||childtable), ' child',				\
			' EXCEPT',																\
            ' select distinct parent.', parentcol,		 							\
			' from ', rtrim(parentschema||'.'||parenttable),  ' parent'				\
			from (																	\
					select 	sub30(r.tabschema) as childschema,						\
	   						sub30(r.tabname)   as childtable,						\
							sub30(r.reftabschema) as parentschema,					\
							sub30(r.reftabname) as parenttable,						\
							sub30(k1.colname)	as childcol,						\
							sub30(k2.colname)	as parentcol						\
					from 	syscat.references r										\
        						inner join											\
							syscat.keycoluse k1										\
								on  r.constname = k1.constname						\
								and r.tabschema = k1.tabschema						\
								and r.tabname = k1.tabname							\
								and r.colcount = k1.colseq							\
								inner join											\
							syscat.keycoluse k2										\
								on  r.refkeyname = k2.constname						\
								and r.reftabschema = k2.tabschema					\
								and r.reftabname = k2.tabname						\
								and r.colcount = k2.colseq							\
					where	r.tabschema = '${SCHEMA}'								\
					and		r.colcount	= 1											\
			) temp "	> $WORKFILE
RC=$? ; DB2_RC_CHECK $RC 0 "Select error on system catalog."

#	Stop at the first SQL error, but report all RI violations: a violation will give DB2 return code 0 whilst a clean bill of health will give rc=1
#	This forces us to process a SQL statement at a time instead of running the entire file.
while read STMT
do
	db2 -v $STMT
	RC=$?
	case $RC in
		 0) print ${BOLD} "$0: Referential integrity violation detected for statement: $STMT" ${PLAIN}
			FINAL_RC=2
			;;
		 1) :	# no operation
			;;
		 *) DB2_RC_CHECK $RC 1 "Query error statement $STMT"
			;;
	esac
done < $WORKFILE

db2 -v terminate 
if [[ $FINAL_RC -eq 0 ]]
then print "$0 ends successfully - no RI violations detected."
	 rm -f $WORKFILE
else print "$0 ends with warnings - RI violations were detected. Please see stdout"
fi

rm -f $WORKFILE
exit $FINAL_RC
# end.
