SETINTEGRITY() {
# Procedure to SET INTEGRITY for all tables in a check pending status in a given schema or schema list.
# Includes error checking which will iteratively retry set integrity up to 10 times whilst child tables 
# are still in check pending.
# 
# Arguments $1 schema - usually just one schema, but the argument can be a comma-separated list string;
#                       the SQL command wraps this string in single quotes so the argument needs to include 
#                       internal quotes if it contains multiple schemata; for example to set integrity for 
#                       schema1, schema2 and schema3 the argument becomes:  schema1','schema2','schema3    
#                     - the default schema name is $DBSCHEMA (exported by $INSTHOME/sqllib/userprofile)
#           $2 ksh debug switch (+x or -x)
# e.g. SETINTEGRITY $DBSCHEMA +x. 
# Edit the LOGDIR and WORKDIR variables as required.
# Alex Levy, 15.11.2007 http://www.sustainablesoftware.net

 if [[ $# -eq 2 ]]
 then
    DEBUG=$2 ; set $DEBUG
    typeset -u SCHEMA=$1
    MESSAGE "Call is SETINTEGRITY $*" PLAIN I $DEBUG 
 else
    MESSAGE "SETINTEGRITY() Bad Call! \n Usage SETINTEGRITY \$1 schema name \$2 ksh debug switch (+x or -x)\n" PLAIN E +x
    return 1
 fi    
 
 WORKDIR=$INSTHOME/tmp  # edit this ...
 LOGDIR=$INSTHOME/log   # ... and this.
 DIR_CHK $WORKDIR $DEBUG  
 DIR_CHK $LOGDIR $DEBUG  
 typeset -i TRIES=0 NR_TABLES_IN_CHECK=0 RC=0 
 LOGDATE=$(date +"%Y%m%d:%H:%M:%S")

 COUNT_TABLES
 if [[ $NR_TABLES_IN_CHECK -gt 0 ]]
 then PRIME_TABLE_STATUS
 # allow for maximum return code 2 from set integrity
 RUN_DB2FILE $WORKDIR/setintegrity_cascade.${LOGDATE}.sql -txf 2 $LOGDIR/setintegrity_cascade.${LOGDATE}.report nocompress $DEBUG
 fi
 while [[ $TRIES -le 10 && $NR_TABLES_IN_CHECK -gt 0 ]]
 do
       GENERATE_SQL
       RUN_SQL
       COUNT_TABLES
       let TRIES=TRIES+1      
 done
}

COUNT_TABLES() {
 # Get a count of the number of tables in check pending for the schema(s) provided.
 NR_TABLES_IN_CHECK=$(db2 -x "select count(*) from syscat.tables where tabschema in ('${SCHEMA}') and status = 'C'")
 RC=$? 
 if [[ $RC -gt 0 ]]
 then
    MESSAGE "\n SETINTEGRITY() COUNT_TABLES SQL Error. \n" PLAIN E $DEBUG
    return $RC
 fi
} 

PRIME_TABLE_STATUS() {

# Cascade all child tables into a check pending state if their parents have set integrity pending.
# This means a subsequent SET INTEGRITY .. IMMEDIATE CHECKED should then work for all tables (children and parents) in check pending.
# This will return a SQL0347W warning, which can safely be ignored.
# Why not limit to tables in check pending status? Because dependent tables in a 'Normal' status will themselves be placed in check pending.
# The SET INTEGRITY statement needs to work on a complete set of tables in check pending and their recursive dependencies,within the same statement.
# Otherwise you get errors like: 
# SQL3608N  Cannot check a dependent table "RCSOT03.COL_SLA" using the SET INTEGRITY statement while the parent table or underlying table 
# "RCSOT03.PROC_SCHED_SCD" is in the Set Integrity Pending state or if it will be put into the Set Integrity Pending state 
# by the SET INTEGRITY statement.  
# Yuk. 

db2 -x  "with TABLIST(tabschema,fullname,seqno) as						\
             (select tabschema,									\
                     rtrim(tabschema)||'.'||tabname, 						\
                     rownumber() over (partition by tabschema)					\
              from   syscat.tables								\
              where  tabschema in ('${SCHEMA}')  						\
              and    STATUS = 'C'),               						\
             SERIAL_LIST(s_tabschema,s_fullname,s_seqno) as					\
             (select tabschema,									\
                     long_varchar(rtrim(fullname)),						\
                     seqno 									\
              from   tablist 									\
              where  seqno = 1									\
                     UNION ALL									\
              select tabschema,									\
                     long_varchar(rtrim(s_fullname||','||fullname)), 				\
                     seqno									\
              from   tablist t, serial_list s							\
              where s.s_tabschema = t.tabschema 						\
              and   s.s_seqno +1 = t.seqno )					       		\
        select rtrim('set integrity for '|| s_fullname||' OFF CASCADE IMMEDIATE TO ALL TABLES;')\
        from   ( select s_fullname, 								\
                        s_seqno,								\
                        rownumber() over (partition by s_tabschema order by s_seqno desc) as rownum 	\
                 from   serial_list) as mandatory_but_unreferenced_correlation_token		\
                 where  rownum = 1" | egrep -v "SQL0347W|infinite" > $WORKDIR/setintegrity_cascade.${LOGDATE}.sql
 RC=$? 
 if [[ $RC -gt 2 ]]
 then
    MESSAGE "\n SETINTEGRITY() PRIME_TABLE_STATUS SQL Error. \n" PLAIN E $DEBUG
    return $RC
 fi
}

GENERATE_SQL() {

#Build a set integrity statement for all tables in check pending, for all schemata in string '$SCHEMA'.
#The recursion serialises the table names from multiple columns into a comma-separated list. Neat, huh?
#A smart move would be to enhance this to present the table list in parent-child order.
#This will still return a SQL0347W warning, which can safely be ignored.

db2 -x "with TABLIST(tabschema,fullname,seqno) as						\
             (select tabschema,									\
                     rtrim(tabschema)||'.'||tabname, 						\
                     rownumber() over (partition by tabschema)					\
              from   syscat.tables								\
              where  tabschema in ('${SCHEMA}')  						\
              and    STATUS = 'C'),               						\
             SERIAL_LIST(s_tabschema,s_fullname,s_seqno) as					\
             (select tabschema,									\
                     long_varchar(rtrim(fullname)),						\
                     seqno 									\
              from   tablist 									\
              where  seqno = 1									\
                     UNION ALL									\
              select tabschema,									\
                     long_varchar(rtrim(s_fullname||','||fullname)), 				\
                     seqno									\
              from   tablist t, serial_list s							\
              where s.s_tabschema = t.tabschema 						\
              and   s.s_seqno +1 = t.seqno )							\
        select rtrim('set integrity for '|| s_fullname || ' immediate checked;')		\
        from   ( select s_fullname, 								\
                        s_seqno,								\
                        rownumber() over (partition by s_tabschema order by s_seqno desc) as rownum 	\
                 from   serial_list) as mandatory_but_unreferenced_correlation_token		\
                 where  rownum = 1" | egrep -v "SQL0347W|infinite" > $WORKDIR/setintegrity.${LOGDATE}.sql
 RC=$? 
 if [[ $RC -gt 2 ]]
 then
    MESSAGE "\n SETINTEGRITY() GENERATE_SQL SQL Error. \n" PLAIN E $DEBUG
    return $RC
 fi
}
   
RUN_SQL () {
# allow for maximum return code 2 from set integrity
RUN_DB2FILE $WORKDIR/setintegrity.${LOGDATE}.sql -txf 2 $LOGDIR/setintegrity.${LOGDATE}.report nocompress $DEBUG
}



