REORG() {
# Procedure to REORG a given schema/table combination, with logic to select the most appropriate clustering index.
# Caller establishes the database connection through a call to CONNECT().
# 
# Arguments $1 schema name - will fold to upper case; this argument must be supplied but may specify
#                            the default schema $DBSCHEMA (exported from $INSTHOME/sqllib/userprofile);
#           $2 table name  - will fold to upper case;
#           $3 ksh debug switch (+x or -x).
# e.g. REORG $DBSCHEMA EMPLOYEE +x. 
# Edit LOGDIR and WORKDIR as required.                                 
# Alex Levy, 19.11.2007 http://www.sustainablesoftware.net

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

 # Construct a REORG TABLE statement, using a ranking system to select the 'best' clustering index. 
 # Select clustering, MDC dimensional, date/time based, duplicate, unique and primary indexes in that order of preference.
 # Allow for unindexed tables; and tables with multiple indices of the same rank.
 # Further allow for the restriction on read access for range-partitioned tables (test on syscat.datapartitions.seqno > 0).
 # Also construct a REORG INDEXES statement, if the table is indexed.
 db2 -x "with temp(tabschema,tabname,index_to_use,rank) as			\
        (select i.tabschema, i.tabname,						\
                rtrim(rtrim(i.indschema)||'.'||i.indname) index_to_use,		\
	        case								\
                   when i.indextype = 'CLUS' then 1				\
                   when i.indextype = 'DIM'  then 2				\
                   when i.indextype = 'BLOK' then 3				\
                   when (u.colname in ('EFF_TS','EXP_TS')			\
		    or u.colname like '%DATE')					\
   	           and  u.colseq = 1	     then 4				\
                   when  i.uniquerule = 'D'  then 5				\
                   when  i.uniquerule = 'U'  then 6				\
                   when  i.uniquerule = 'P'  then 7 				\
                end as rank							\
         from   syscat.indexes     i inner join 				\
                syscat.indexcoluse u 						\
                on i.indschema  = u.indschema					\
	        and i.indname   = u.indname					\
         where  i.tabschema = '${SCHEMA}' and i.tabname = '${TABLE}'		\
		UNION ALL							\
         values('${SCHEMA}','${TABLE}','',99)					\
        ),									\
      temp2 (tabschema,tabname,index_to_use) as					\
        (select tabschema,tabname,min(index_to_use)				\
         from   temp 								\
         where rank = (select min(rank) from temp)				\
         group by tabname,tabschema						\
        ),									\
      temp3 (tabschema,tabname,seqno) as					\
        (select   tabschema,tabname,max(seqno)					\
         from     syscat.datapartitions						\
         where    tabschema = '${SCHEMA}' and tabname = '${TABLE}'		\
         group by tabschema,tabname						\
        )									\
 select 'reorg table ',char(rtrim(temp2.tabschema)||'.'||temp2.tabname,80),	\
        case 									\
            when temp2.index_to_use is null or temp2.index_to_use = '' then ' '	\
            else char('index '||temp2.index_to_use,80) 				\
        end,									\
        case temp3.seqno							\
             when 0  then ' allow read access;'					\
             when 99 then ' allow read access;'					\
             else ';' 								\
        end									\
        from temp2 inner join temp3						\
          on temp2.tabschema = temp3.tabschema					\
         and temp2.tabname = temp3.tabname					\
             UNION 								\
 select ' ',' ',								\
        case									\
             when temp2.index_to_use is null or temp2.index_to_use = '' 	\
             then 'echo there are no indexes for this table'			\
             else 'reorg indexes all for table '||char(rtrim(temp2.tabschema)||'.'||temp2.tabname,80)	\
        end,									\
        case 									\
             when temp2.index_to_use is null or temp2.index_to_use = '' 	\
                  then ';'							\
             when temp3.seqno = 0 						\
                  then ' allow read access;'					\
             else ';'								\
        end									\
        from temp2 inner join temp3						\
          on temp2.tabschema = temp3.tabschema					\
         and temp2.tabname = temp3.tabname" > $WORKDIR/reorg.${TABLE}.${LOGDATE}.sql
	
 typeset -i RC=$?
 if [[ $RC -gt 0 ]]
 then
    MESSAGE "REORG() :Bad return code generating SQL for the REORG of table $TARGET. Exiting ..." PLAIN E $DEBUG 
    return $RC
 fi

 RUN_DB2FILE $WORKDIR/reorg.${TABLE}.${LOGDATE}.sql -txf 0 $LOGDIR/reorg.${TABLE}.${LOGDATE}.report nocompress $DEBUG

 RC=$?
 if [[ $RC -gt 0 ]]
 then
    MESSAGE "REORG() :Bad return code $RC trying to REORG table $TARGET. Exiting ..." PLAIN E $DEBUG 
 fi
 return $RC
}
