#!/usr/bin/ksh
#-----------------------------------------------------------------------------------------
# Synopsis: Compare 2 schemata in the same database. The output lists:
#	    the number of tables in each schema
#	    tables in schema A not in schema B
#	    tables in schema B not in schema A
#	    tables in either schema with a different row count (based on RUNSTATS)
#           column definition differences, by table and schema
#           differences in primary, unique, foreign and check constraints 
#           indexes, views, aliases and nicknames in one schema but not the other
#           rioutines(functions, methods, procedures) and triggers in one schema but not the other.
#           As at V9.5, DB2 does not pre-pack this functionality - though it does include administrative procedures
#           to drop or copy schemata. 
#
# 	    CUSTOMISATION FOR YOUR SITE 
# 	    --------------------------
#           - Review the attributes used to compare each DB2 object type and select those that matter to you. 
#             For example, do you care for the purposes of comparison whether the owner is different? 
#             If views or triggers have the same name in different schemata, do you want to compare the definition text? etc.
# 	    - You can of course extend the comparisons to other database objects such as MQTs, sequences, distinct types etc.
# 	    - Modify the substr calls() if you use excessively long object names; and consider the virtues of brevity.
#           - This may not work properly if either schema has (inadvertently or otherwise) mixed or lower case object names,
#             introduced e.g. through an ODBC client.
#           - Finally, modify the CONSTRAINTS() function if future releases of DB2 increase the name length limit for constraints from 18. 
#
# Arguments: $1 dbalias
#            $2 schema #1
#            $3 schema #2
#
# Pre-reqs:  minimum CONNECT,IMPLICIT_SCHEMA and CREATIN/DROPIN authorities on the implicit schema;
#            write access to /tmp
#
# Assumptions: runs with autocommit on by default.
#
# Based on an original prototype by Nick Smith of IBM - a top guy.
# Alex Levy t/a http://www.sustainablesoftware.net, June 2009
#-----------------------------------------------------------------------------------------

# global variables
BOLD=$(tput smso) ; PLAIN=$(tput rmso)
typeset -u DBNAME SCHEMA_1 SCHEMA_2

DB2_RC_CHECK  () { 
typeset -i RETCODE=$1 MAXRC=$2  
LOCATION=$3   
MSG=$4 
if [[ $RETCODE -gt $MAXRC ]] 
then  
   print ${BOLD} $0 DB2/SQL error return code $RETCODE at program location $LOCATION. 
   if [[ -n $MSG ]] 
   then print $MSG   
   fi 
   print Exiting ... ${PLAIN} 
   db2 terminate 
   exit $RETCODE 
fi 
} 

INITIAL () {
if [[ $# = 3 ]] 
then
   DBNAME=$1
   SCHEMA_1=$2
   SCHEMA_2=$3
else
   print ${BOLD} "Usage! I said $0 is a script for comparing 2 schemata in a given database."
   print "Call it with three parameters: dbname, schema1 and schema2 \n (e.g. $0 sample tweedledee tweedledum)" ${PLAIN}
   exit 1
fi

db2 connect to $DBNAME
RC=$? ; DB2_RC_CHECK $RC 0 2 "Unable to connect to database $DBNAME. Check your spelling and the accompanying error message."

typeset -i SCHEMA_COUNT=$(db2 -x "select count(*) from syscat.schemata where schemaname in ('$SCHEMA_1','$SCHEMA_2')")
RC=$? ; DB2_RC_CHECK $RC 0 3 "One or both of schemata $SCHEMA_1 and $SCHEMA_2 does not exist on database $DBNAME. Try again."

print "Performing a reorgchk update statistics on $SCHEMA_1"
db2 "reorgchk update statistics on schema $SCHEMA_1" > /dev/null
RC=$? ; DB2_RC_CHECK $RC 0 4 "Bad return code from the reorgchk for $SCHEMA_1. Attempt this command manually to find out why."

print "performing a reorgchk update statistics on $SCHEMA_2"
db2 "reorgchk update statistics on schema $SCHEMA_2" > /dev/null
RC=$? ; DB2_RC_CHECK $RC 0 5 "Bad return code from the reorgchk for $SCHEMA_2. Attempt this command manually to find out why."
}

TABLES () {

# create a cast function for the maximum display length for syscat.tables objects in either schema:
typeset -i S1_MAXTABLENGTH=$(db2 -x "select max(length(tabname)) from syscat.tables where tabschema = '$SCHEMA_1' and type in ('A','N','T','V')")
RC=$? ; DB2_RC_CHECK $RC 0 5_1 
typeset -i S2_MAXTABLENGTH=$(db2 -x "select max(length(tabname)) from syscat.tables where tabschema = '$SCHEMA_2' and type in ('A','N','T','V')")
RC=$? ; DB2_RC_CHECK $RC 0 5_2 
if [[ S1_MAXTABLENGTH -ge  S2_MAXTABLENGTH ]]
then typeset -i MAXTABLENGTH=$S1_MAXTABLENGTH
else typeset -i MAXTABLENGTH=$S2_MAXTABLENGTH
fi

db2 set current schema current user
RC=$? ; DB2_RC_CHECK $RC 0 5_3 
db2 "drop function TAB_DISPLAY" > /dev/null
RC=$? ; DB2_RC_CHECK $RC 4 5_3_1 
db2 "create function TAB_DISPLAY( tabname varchar(128))	\
     returns char($MAXTABLENGTH)			\
specific tab_display language sql			\
deterministic no external action			\
contains sql						\
return substr(tabname,1,$MAXTABLENGTH)		        "
RC=$? ; DB2_RC_CHECK $RC 0 5_4 

db2 "comment on function TAB_DISPLAY is 'Returns the leading characters of the tablename argument, truncated to the length of the longest tablename in either schema $SCHEMA_1 or $SCHEMA_2.'" 
RC=$? ; DB2_RC_CHECK $RC 4 5_5   # Allow rc=4 for string expansion beyond 254 characters (SQLSTATE=54002).

db2 commit
RC=$? ; DB2_RC_CHECK $RC 0 5_8 

print -- "-----------------------------------------------------"
print    " Basic table comparisons for $SCHEMA_1 and $SCHEMA_2 "
print -- "-----------------------------------------------------"
print "\n"

typeset -i SCHEMA_1_TABLE_COUNT SCHEMA_2_TABLE_COUNT
#print -- "---------------------------------------------------------------------"
#print    " Checking that $SCHEMA_1 and $SCHEMA_2 have the same number of tables"
#print -- "---------------------------------------------------------------------"
#print    "                                                                     "
SCHEMA_1_TABLE_COUNT=$(db2 -x "select count(*) from syscat.tables where tabschema = '$SCHEMA_1' and type = 'T'") 
RC=$? ; DB2_RC_CHECK $RC 0 6 
SCHEMA_2_TABLE_COUNT=$(db2 -x "select count(*) from syscat.tables where tabschema = '$SCHEMA_2' and type = 'T'") 
RC=$? ; DB2_RC_CHECK $RC 0 7 
if [[ $SCHEMA_1_TABLE_COUNT -eq $SCHEMA_2_TABLE_COUNT ]]
then
    print "$SCHEMA_1 and $SCHEMA_2 both have $SCHEMA_1_TABLE_COUNT tables. \n"
else
    print "$SCHEMA_1  has $SCHEMA_1_TABLE_COUNT tables and $SCHEMA_2 has $SCHEMA_2_TABLE_COUNT tables.\n"
fi

print "The following tables,aliases,nicknames and views appear in $SCHEMA_1 but not in $SCHEMA_2 :\n"
db2 -x "select tab_display(tabname),              \
               case type when 'T' then 'Table'    \
                         when 'A' then 'Alias'    \
                         when 'N' then 'Nickname' \
                         when 'V' then 'View'     \
                    end                           \
        from   syscat.tables                      \
        where  tabschema = '$SCHEMA_1'            \
        and    type in ('A','N','T','V')          \
               EXCEPT                             \
        select tab_display(tabname),              \
               case type when 'T' then 'Table'    \
                         when 'A' then 'Alias'    \
                         when 'N' then 'Nickname' \
                         when 'V' then 'View'     \
                    end                           \
        from   syscat.tables                      \
        where  tabschema = '$SCHEMA_2'            \
        and    type in ('A','N','T','V')          "
RC=$? ; DB2_RC_CHECK $RC 1 8 

print "The following tables,aliases,nicknames and views appear in $SCHEMA_2 but not in $SCHEMA_1 :\n"
db2 -x "select tab_display(tabname),              \
               case type when 'T' then 'Table'    \
                         when 'A' then 'Alias'    \
                         when 'N' then 'Nickname' \
                         when 'V' then 'View'     \
                    end                           \
        from   syscat.tables                      \
        where  tabschema = '$SCHEMA_2'            \
        and    type in ('A','N','T','V')          \
               EXCEPT                             \
        select tab_display(tabname),              \
               case type when 'T' then 'Table'    \
                         when 'A' then 'Alias'    \
                         when 'N' then 'Nickname' \
                         when 'V' then 'View'     \
                    end                           \
        from   syscat.tables                      \
        where  tabschema = '$SCHEMA_1'            \
        and    type in ('A','N','T','V')          "
RC=$? ; DB2_RC_CHECK $RC 1 9 
db2 commit > /dev/null #release share locks

#-------------------------------------------------
# Compare row counts for each matching table name.
#-------------------------------------------------

if [[ $SCHEMA_1_TABLE_COUNT -ne $SCHEMA_2_TABLE_COUNT ]]
then
   print "The schemata contain different tables, so we only compare row counts in common tables."
fi
db2 "select tab_display(s1.tabname) as table, s1.card as $SCHEMA_1, s2.card as $SCHEMA_2 \
     from   (select tabname, card		\
             from   syscat.tables		\
             where  tabschema = '$SCHEMA_1'	\
             and type = 'T' ) s1		\
                 INNER JOIN			\
            (select tabname, card		\
             from   syscat.tables		\
             where  tabschema = '$SCHEMA_2'	\
             and type = 'T' ) s2		\
                 on s1.tabname = s2.tabname	\
     where  s1.card <> s2.card                  \
     order by 1 for fetch only                  " > /tmp/comprow.$$
RC=$? ; DB2_RC_CHECK $RC 1 10 "Look in /tmp/comprow.$$"
db2 commit > /dev/null #release share locks

if [[ $RC -eq 1 ]] 
then  
   print -- "-------------------------------------------------------------" 
   print    " Identical row counts for $SCHEMA_1 and $SCHEMA_2 .          " 
   print -- "-------------------------------------------------------------" 
   print "\n" 
else 
   print -- "-------------------------------------------------------------" 
   print    " The following tables have a different number of rows.       " 
   print -- "-------------------------------------------------------------" 
   print "\n" 
   cat /tmp/comprow.$$ 
   rm /tmp/comprow.$$ 
fi 
}

COLUMNS () {

print -- "--------------------------------------------------------------"
print    "Find all the tables and columns which differ between schemata."
print    "ADDED columns are present in $SCHEMA_2 but not in $SCHEMA_1   "
print    "DELETED columns are present in $SCHEMA_1 but not in $SCHEMA_2 "
print -- "--------------------------------------------------------------\n"

db2 "select tab_display(tabname) as tabname, 	\
            substr(colname,1,30) as colname, 	\
            substr(typename,1,12) as typename, 	\
            length, 				\
            generated, 				\
            nulls, 				\
            'ADDED' as label  			\
    from    syscat.columns 			\
    where   tabschema = '$SCHEMA_2' 		\
            EXCEPT 				\
    select  tab_display(tabname) as tabname, 	\
            substr(colname,1,30) as colname, 	\
            substr(typename,1,12) as typename, 	\
            length, 				\
            generated, 				\
            nulls, 				\
            'ADDED' as label 			\
    from    syscat.columns 			\
    where   tabschema = '$SCHEMA_1' 		\
            UNION 				\
    select  tab_display(tabname) as tabname, 	\
            substr(colname,1,30) as colname, 	\
            substr(typename,1,12) as typename, 	\
            length, 				\
            generated, 				\
            nulls, 				\
            'DELETED' as label  		\
    from    syscat.columns 			\
    where   tabschema = '$SCHEMA_1' 		\
            EXCEPT 				\
    select  tab_display(tabname) as tabname, 	\
            substr(colname,1,30) as colname, 	\
            substr(typename,1,12) as typename, 	\
            length, 				\
            generated, 				\
            nulls, 				\
            'DELETED' as label 			\
    from    syscat.columns 			\
    where   tabschema = '$SCHEMA_2' 		\
    order by tabname, colname, typename,label   \
    for fetch only                              " > /tmp/compcol.$$

RC=$? ; DB2_RC_CHECK $RC 1 11 "Look in /tmp/compcol.$$"
db2 commit > /dev/null #release share locks

if [[ $RC -eq 1 ]] 
then  
   print    " $SCHEMA_1 and $SCHEMA_2 have identical column definitions. " 
   print "\n" 
else 
   print    " The following tables have different column definitions.     " 
   print "\n" 
   cat /tmp/compcol.$$ 
   rm /tmp/compcol.$$ 
fi 
}

CONSTRAINTS () {

print -- "-----------------------------------------------------------------------------"
print    "Find all varying primary, unique and foreign constraints:"
print    "ADDED constraints are present in $SCHEMA_2 but not in $SCHEMA_1 "
print    "DELETED constraints are in $SCHEMA_1 but not in $SCHEMA_2 "
print -- "-----------------------------------------------------------------------------\n"

db2 "select substr(k.constname,1,18) as constname,	\
            tab_display(k.tabname)   as tabname,   	\
            substr(k.colname,1,30)   as colname,   	\
            t.type,				   	\
            t.enforced, 			   	\
            'ADDED'                as label	   	\
     from   syscat.keycoluse k				\
                  inner join				\
            syscat.tabconst  t				\
                  on  k.constname = t.constname		\
                  and k.tabschema = t.tabschema  	\
     where  k.tabschema = '$SCHEMA_2' 			\
            EXCEPT 					\
     select substr(k.constname,1,18) as constname, 	\
            tab_display(k.tabname)   as tabname, 	\
            substr(k.colname,1,30)   as colname, 	\
            t.type,					\
            t.enforced, 				\
            'ADDED'                as label 		\
     from   syscat.keycoluse k				\
                  inner join				\
            syscat.tabconst  t				\
                  on  k.constname = t.constname		\
                  and k.tabschema = t.tabschema  	\
     where  k.tabschema = '$SCHEMA_1' 			\
            UNION 					\
     select substr(k.constname,1,18) as constname,	\
            tab_display(k.tabname)   as tabname,	\
            substr(k.colname, 1,30)  as colname, 	\
            t.type,					\
            t.enforced, 				\
            'DELETED'              as label 		\
     from   syscat.keycoluse k				\
                  inner join				\
            syscat.tabconst  t				\
                  on  k.constname = t.constname		\
                  and k.tabschema = t.tabschema  	\
     where  k.tabschema = '$SCHEMA_1' 			\
            EXCEPT 					\
     select substr(k.constname,1,18) as constname, 	\
            tab_display(k.tabname)   as tabname, 	\
            substr(k.colname,1,30)   as colname, 	\
            t.type,					\
            t.enforced, 				\
            'DELETED'              as label  		\
     from   syscat.keycoluse k				\
                  inner join				\
            syscat.tabconst  t				\
                  on  k.constname = t.constname		\
                  and k.tabschema = t.tabschema  	\
     where  k.tabschema = '$SCHEMA_2' 			\
     order by constname,tabname,label  			\
     for fetch only                                     " > /tmp/compconst.$$

RC=$? ; DB2_RC_CHECK $RC 1 12 "Look in  /tmp/compconst.$$"
db2 commit > /dev/null #release locks

if [[ $RC -eq 1 ]] 
then  
   print    " $SCHEMA_1 and $SCHEMA_2 have identical constraints (primary, unique and foreign). " 
   print "\n" 
else 
   print    " The following constraints vary by schema: " 
   print "\n" 
   cat /tmp/compconst.$$ 
   rm /tmp/compconst.$$ 
fi 

# note: check and functional constraints are not recorded in syscat.tabconst
print -- "----------------------------------------------------------------------"
print    "Find all varying check and functional constraints:"
print    "ADDED constraints are present in $SCHEMA_2 but not in $SCHEMA_1 "
print    "DELETED constraints are in $SCHEMA_1 but not in $SCHEMA_2 "
print -- "----------------------------------------------------------------------\n"


db2 "select substr(constname,1,18) as constname,	\
            tab_display(tabname)   as tabname,   	\
            type,				   	\
            enforced, 			   		\
            enablequeryopt,		   		\
            'ADDED'                as label	   	\
     from   syscat.tabconst   				\
     where  tabschema = '$SCHEMA_2' 			\
     and    type in ('I','K')				\
            EXCEPT 					\
     select substr(constname,1,18) as constname, 	\
            tab_display(tabname)   as tabname, 		\
            type,					\
            enforced,	 				\
            enablequeryopt,		   		\
            'ADDED'                as label 		\
     from   syscat.tabconst  				\
     where  tabschema = '$SCHEMA_1' 			\
     and    type in ('I','K')				\
            UNION 					\
     select substr(constname,1,18) as constname,	\
            tab_display(tabname)   as tabname,		\
            type,					\
            enforced, 					\
            enablequeryopt,		   		\
            'DELETED'              as label 		\
     from   syscat.tabconst   				\
     where  tabschema = '$SCHEMA_1' 			\
     and    type in ('I','K')				\
            EXCEPT 					\
     select substr(constname,1,18) as constname, 	\
            tab_display(tabname)   as tabname, 		\
            type,					\
            enforced, 					\
            enablequeryopt,		   		\
            'DELETED'              as label  		\
     from   syscat.tabconst   				\
     where  tabschema = '$SCHEMA_2' 			\
     and    type in ('I','K')				\
     order by constname,tabname,label for fetch only    " > /tmp/compconst2.$$

RC=$? ; DB2_RC_CHECK $RC 1 13 "Look in  /tmp/compconst2.$$"
db2 commit > /dev/null #release locks

if [[ $RC -eq 1 ]] 
then  
   print    " $SCHEMA_1 and $SCHEMA_2 have identical constraints (primary, unique and foreign). " 
   print "\n" 
else 
   print    " The following constraints vary by schema: " 
   print "\n" 
   cat /tmp/compconst2.$$ 
   rm /tmp/compconst2.$$ 
fi 

}

INDICES () {
print -- "------------------------------------------------------------------"
print    "Find all indices and index keys which vary between schemata.      "
print    "ADDED indices are present in $SCHEMA_2 but not in $SCHEMA_1       "
print    "DELETED indices are in $SCHEMA_1 but not in $SCHEMA_2             "
print -- "------------------------------------------------------------------\n"

db2 "select substr(i.indname,1,30)   as indname,	\
            tab_display(i.tabname)   as tabname, 	\
            substr(ic.colname,1,30)  as colname,	\
            i.uniquerule		       ,	\
 	    i.colcount			       ,	\
            i.indextype                        ,	\
            i.pctfree			       ,	\
            i.minpctused		       ,	\
	    i.reverse_scans		       ,	\
            'ADDED'                  as label	   	\
     from   syscat.indexes i				\
                   inner join				\
            syscat.indexcoluse ic			\
                   on  i.indschema = ic.indschema	\
                   and i.indname = ic.indname		\
     where  i.indschema = '$SCHEMA_2'			\
            EXCEPT					\
     select substr(i.indname,1,30)   as indname,	\
            tab_display(i.tabname)   as tabname, 	\
            substr(ic.colname,1,30)  as colname,	\
            i.uniquerule		       ,	\
 	    i.colcount			       ,	\
            i.indextype                        ,	\
            i.pctfree			       ,	\
            i.minpctused		       ,	\
	    i.reverse_scans		       ,	\
            'ADDED'                  as label	   	\
     from   syscat.indexes i				\
                   inner join				\
            syscat.indexcoluse ic			\
                   on  i.indschema = ic.indschema	\
                   and i.indname = ic.indname		\
     where  i.indschema = '$SCHEMA_1'			\
            UNION 					\
     select substr(i.indname,1,30)   as indname,	\
            tab_display(i.tabname)   as tabname, 	\
            substr(ic.colname,1,30)  as colname,	\
            i.uniquerule		       ,	\
 	    i.colcount			       ,	\
            i.indextype                        ,	\
            i.pctfree			       ,	\
            i.minpctused		       ,	\
	    i.reverse_scans		       ,	\
            'DELETED'                  as label	   	\
     from   syscat.indexes i				\
                   inner join				\
            syscat.indexcoluse ic			\
                   on  i.indschema = ic.indschema	\
                   and i.indname = ic.indname		\
     where  i.indschema = '$SCHEMA_1'			\
            EXCEPT					\
     select substr(i.indname,1,30)   as indname,	\
            tab_display(i.tabname)   as tabname, 	\
            substr(ic.colname,1,30)  as colname,	\
            i.uniquerule		       ,	\
 	    i.colcount			       ,	\
            i.indextype                        ,	\
            i.pctfree			       ,	\
            i.minpctused		       ,	\
	    i.reverse_scans		       ,	\
            'DELETED'                  as label	   	\
     from   syscat.indexes i				\
                   inner join				\
            syscat.indexcoluse ic			\
                   on  i.indschema = ic.indschema	\
                   and i.indname = ic.indname		\
     where  i.indschema = '$SCHEMA_2'			\
     order by indname,tabname,colname,label		\
     for fetch only					" > /tmp/compind.$$

RC=$? ; DB2_RC_CHECK $RC 1 14 "Look in  /tmp/compind.$$"
db2 commit > /dev/null #release locks

if [[ $RC -eq 1 ]] 
then  
   print    " $SCHEMA_1 and $SCHEMA_2 have identical indexes. " 
   print "\n" 
else 
   print    " The following indexes vary by schema: " 
   print "\n" 
   cat /tmp/compind.$$ 
   rm /tmp/compind.$$ 
fi 
}

ROUTINES () {

routcount=$(db2 -x "select count(*) from syscat.routines where routineschema in ('$SCHEMA_1','$SCHEMA_2')" )
RC=$? ; DB2_RC_CHECK $RC 1 15 
if [[ $routcount -eq 0 ]]
then
   print "\n There are no routines (functions, methods and procedures) in either $SCHEMA_1 or $SCHEMA_2 \n"
   return
fi

db2 "select substr(routinename,1,30) as routine,\
            case routinetype			\
                 when 'F' then 'Function'  	\
                 when 'M' then 'Method'    	\
                 when 'P' then 'Procedure' 	\
                 end,				\
            origin, functiontype, parm_count, language, fenced, federated, valid \
      from  syscat.routines                  	\
      where routineschema = '$SCHEMA_1'         \
               EXCEPT                           \
     select substr(routinename,1,30) as routine,\
            case routinetype			\
                 when 'F' then 'Function'  	\
                 when 'M' then 'Method'    	\
                 when 'P' then 'Procedure' 	\
                 end,				\
            origin, functiontype, parm_count, language, fenced, federated, valid \
      from  syscat.routines                  	\
      where routineschema = '$SCHEMA_2'         \
      for fetch only 				" > /tmp/comprout1.$$

RC1=$? ; DB2_RC_CHECK $RC1 1 16 
if [[ $RC1 -eq 0 ]] 
then  
   print "The following routines (functions, methods and procedures) appear in $SCHEMA_1 but not in $SCHEMA_2 :\n"
   cat /tmp/comprout1.$$
fi
rm  /tmp/comprout1.$$

db2 "select substr(routinename,1,30) as routine,\
            case routinetype			\
                 when 'F' then 'Function'  	\
                 when 'M' then 'Method'    	\
                 when 'P' then 'Procedure' 	\
                 end,				\
            origin, functiontype, parm_count, language, fenced, federated, valid \
      from  syscat.routines                  	\
      where routineschema = '$SCHEMA_2'         \
               EXCEPT                           \
     select substr(routinename,1,30) as routine,\
            case routinetype			\
                 when 'F' then 'Function'  	\
                 when 'M' then 'Method'    	\
                 when 'P' then 'Procedure' 	\
                 end,				\
            origin, functiontype, parm_count, language, fenced, federated, valid \
      from  syscat.routines                  	\
      where routineschema = '$SCHEMA_1'         \
      for fetch only 				" > /tmp/comprout2.$$

RC2=$? ; DB2_RC_CHECK $RC2 1 17 
db2 commit > /dev/null # release share locks
if [[ $RC2 -eq 0 ]] 
then  
   print "The following routines (functions, methods and procedures) appear in $SCHEMA_2 but not in $SCHEMA_1 :\n"
   cat /tmp/comprout2.$$
fi
rm  /tmp/comprout2.$$

if [[ $RC1 -eq 1 && $RC2 -eq 1 ]]
then
   print " $SCHEMA_1 and $SCHEMA_2 appear to have identical routines (functions, methods and procedures)." 
fi
}

TRIGGERS () {

trigcount=$(db2 -x "select count(*) from syscat.triggers where trigschema in ('$SCHEMA_1','$SCHEMA_2')" )
RC=$? ; DB2_RC_CHECK $RC 1 18 
if [[ $trigcount -eq 0 ]]
then
   print "\n There are no triggers in either $SCHEMA_1 or $SCHEMA_2 \n"
   return
fi

db2 "select substr(trigname,1,30) as trigger,	\
            tab_display(tabname)  as tabname,	\
            valid,				\
            case trigtime   			\
                 when 'A' then 'After'  	\
                 when 'B' then 'Before'    	\
                 when 'I' then 'Instead' 	\
                 end,				\
            case trigevent  			\
                 when 'D' then 'Delete' 	\
                 when 'I' then 'Insert'    	\
                 when 'U' then 'Update' 	\
                 end,				\
            case granularity  			\
                 when 'R' then 'Row'    	\
                 when 'S' then 'Statement' 	\
                 end 				\
      from  syscat.triggers                  	\
      where trigschema = '$SCHEMA_1'            \
               EXCEPT                           \
     select substr(trigname,1,30) as trigger,	\
            tab_display(tabname)  as tabname,	\
            valid,				\
            case trigtime   			\
                 when 'A' then 'After'  	\
                 when 'B' then 'Before'    	\
                 when 'I' then 'Instead' 	\
                 end,				\
            case trigevent  			\
                 when 'D' then 'Delete' 	\
                 when 'I' then 'Insert'    	\
                 when 'U' then 'Update' 	\
                 end,				\
            case granularity  			\
                 when 'R' then 'Row'    	\
                 when 'S' then 'Statement' 	\
                 end 				\
      from  syscat.triggers                  	\
      where trigschema = '$SCHEMA_2'            \
      for fetch only 				" > /tmp/comptrig1.$$

RC1=$? ; DB2_RC_CHECK $RC1 1 19 
if [[ $RC1 -eq 0 ]] 
then  
   print "The following routines (functions, methods and procedures) appear in $SCHEMA_1 but not in $SCHEMA_2 :\n"
   cat /tmp/comptrig1.$$
fi
rm  /tmp/comptrig1.$$

db2 "select substr(trigname,1,30) as trigger,	\
            tab_display(tabname)  as tabname,	\
            valid,				\
            case trigtime   			\
                 when 'A' then 'After'  	\
                 when 'B' then 'Before'    	\
                 when 'I' then 'Instead' 	\
                 end,				\
            case trigevent  			\
                 when 'D' then 'Delete' 	\
                 when 'I' then 'Insert'    	\
                 when 'U' then 'Update' 	\
                 end,				\
            case granularity  			\
                 when 'R' then 'Row'    	\
                 when 'S' then 'Statement' 	\
                 end 				\
      from  syscat.triggers                  	\
      where trigschema = '$SCHEMA_2'            \
               EXCEPT                           \
     select substr(trigname,1,30) as trigger,	\
            tab_display(tabname)  as tabname,	\
            valid,				\
            case trigtime   			\
                 when 'A' then 'After'  	\
                 when 'B' then 'Before'    	\
                 when 'I' then 'Instead' 	\
                 end,				\
            case trigevent  			\
                 when 'D' then 'Delete' 	\
                 when 'I' then 'Insert'    	\
                 when 'U' then 'Update' 	\
                 end,				\
            case granularity  			\
                 when 'R' then 'Row'    	\
                 when 'S' then 'Statement' 	\
                 end 				\
      from  syscat.triggers                  	\
      where trigschema = '$SCHEMA_1'            \
      for fetch only 				" > /tmp/comptrig2.$$

RC2=$? ; DB2_RC_CHECK $RC2 1 20
db2 commit > /dev/null # release share locks
if [[ $RC2 -eq 0 ]] 
then  
   print "The following triggers appear in $SCHEMA_2 but not in $SCHEMA_1 :\n"
   cat /tmp/comptrig2.$$
fi
rm  /tmp/comptrig2.$$

if [[ $RC1 -eq 1 && $RC2 -eq 1 ]]
then
   print " $SCHEMA_1 and $SCHEMA_2 appear to have identical triggers." 
fi
}

# main
INITIAL $*
TABLES
COLUMNS
CONSTRAINTS
INDICES 
ROUTINES
TRIGGERS
db2 "connect reset" > /dev/null
