#!/bin/ksh
###
# Discover index types (type-1 type-2) in use on a DB2 UDB database and convert all type-1s to type-2.
# Type-2 indexes are required for certain online operations including REORG and load; 
# for multidimensional clustering; and improved concurrency.

# The INSPECT command is the only means to determine the index type at V8
# - this information is not held in the system catalog.
# The result is the file named type1_tables.list, located in the DIAGPATH.
# Required authorisation: at least one of SYSADM,SYSMAINT,SYSCTRL or DBADM.
#
# Parameters: $1 instance name, $2 database name.
#
# Alex Levy, Sustainable Software Ltd.
# Grateful thanks to Philippe Zappa of IBM for the sed! 
# April 2006, updated November 2006.
###


if [[ $# -eq 2 ]]
then 
   typeset -l instance=$1
   dbalias=$2
   . /home/${instance}/sqllib/db2profile
else
   print "Usage! I said: type1_index.ksh instance database_alias"
   exit 1
fi
  
# INSPECT will not overwrite output from previous runs; it logs to the diagpath.
DIAGPATH=$(db2 get dbm cfg | grep DIAGPATH | awk '{print $7}')
cd $DIAGPATH
rm -f ${DIAGPATH}/db2inspect.out 2> /dev/null
# inspect command requires a db connection.
db2 "connect to ${dbalias}" > /dev/null
db2 "inspect check database for error state all results keep db2inspect.out"
# Format results.
db2inspf ${DIAGPATH}/db2inspect.out db2inspf.out

database=$1
rm -f ${DIAGPATH}/type1_tables.list

# Extract table and tablespace object IDs, where index type is 1.
# First remove the empty spaces located at the begining of each line of the formatted report, 
# otherwise the grep -p will return the whole report line.

sed "s/     //g" ${DIAGPATH}/db2inspf.out | grep -p "type is 1" | grep "Object:" > type1_tables.tmp

while read line
do
    table_id=`print "$line" | awk  '{print $5}'`
    tablespace_id=`print "$line" | awk  '{print $7}'`
    db2 -x "select rtrim(tabschema)||'.'||rtrim(tabname) from syscat.tables \
            where tableid = ${table_id}                                     \
            and tbspaceid = ${tablespace_id}                                \
            for fetch only with ur" >> type1_tables.list
done < type1_tables.tmp

# Now run the index conversion on all partitions - this is extremely quick.
# You may wish to change the access clause to WRITE or NO access.
if [[ -s type1_tables.list ]]
then
   typeset -i partitions=$(wc -l /home/${instance}/sqllib/db2nodes.cfg | awk '{print $1}' )
   if [[ $partitions -eq 1 ]]
   then partitionstring=""
   else partitionstring="on all dbpartitionnums"
   fi
   for table in `cat type1_tables.list`
   do
       db2 -v "reorg indexes for table ${table} allow read access convert ${partitionstring} "
       db2 -v commit
   done
fi 
rm -f type1_tables.tmp db2inspect.out
db2 "terminate" > /dev/null
cd -
