File contents

#!/bin/ksh
#
#---------------------------------------------------------------------------------------------
# Create the a zip file with db2look output, export output to rebuild a database.
# Create a file called: <db name>.rebuild.ksh with the necessary commands to rebuild
# in the target server.
#
# Modification:
# Danilo Caetano Pereira:  Creation - June, 01 - 2009
#
#---------------------------------------------------------------------------------------------
. $HOME/sqllib/db2profile

##### --- Variables

# db name
tDBNAME=$1

# number os args.
tNUMARGS=$#

# the output path.
outPath=/db2/BD02/migração/$tDBNAME"_rebuild"

# current path.
tCurrentPath=$(pwd)

# User to handle errors.
tERROR=0

# Function to handle errors.
getError() {
	echo -e "\n$tERROR"
	exit -2
}

if [[ $tNUMARGS -ne 1 ]]; then
        echo "Usage: ./rebuilddb.ksh <parameter 1>"
        echo "<parameter 1> - <db name>"
        exit -2
fi

touch $tCurrentPath/$tDBNAME.rebuild.log

echo -e "\nConnecting to the database.."
db2 connect to $tDBNAME >> $tCurrentPath/$tDBNAME.rebuild.log

if [[ $? -ne 0 ]]; then
	tERROR="Database is not connectable, Please check!"
	getError
fi

# Check if the output path exist, if not it's created.
if [[ ! -d $outPath ]]; then
        mkdir $outPath
        mkdir $outPath/lobs
        chmod 740 $outPath
fi

##### --- Running the utilities to create the db2look, export and load commands:

# running the db2look
echo -e "\nRunning the db2look..\n"
##db2look -d $tDBNAME -a -e -m -l -x -f -xs -o $outPath/$tDBNAME.db2look.sql
db2look -d $tDBNAME -a -e -l -x -f -xs -o $outPath/newDb_db2look.sql

# Creating the export command.
db2 -x "select 'export to ' || rtrim(tabschema)||'.'||rtrim(tabname) ||  '.ixf of ixf lobs to lobs MODIFIED BY lobsinfile MESSAGES ' || rtrim(tabname) || '.msg select * from ' || rtrim(tabschema)||'.'||rtrim(tabname) ||';'from syscat.tables where type = 'T' and tabschema not like 'SYS%' order by tabschema, tabname" >> $outPath/newDb_export.sql

# creating the load commands.
db2 -x "select 'load from ' || rtrim(a.tabschema)||'.'||rtrim(a.tabname) || '.ixf of ixf lobs from lobs SAVECOUNT 5000 MESSAGES l' || rtrim(a.tabname) || '.msg insert into ' || rtrim(a.tabschema)||'.'||rtrim(a.tabname) ||';'from syscat.tables a where  not exists (select * from syscat.columns b where b.generated='A' and a.tabname=b.tabname and  a.tabschema=b.tabschema)  and a.tabschema not like 'SYS%' and a.type='T'" >> $outPath/newDb_load.sql

# creating the load commands for tables with identity as 
db2 -x "select 'load from ' || rtrim(a.tabschema)||'.'||rtrim(a.tabname) || '.ixf of ixf lobs from lobs modified by identityoverride SAVECOUNT 5000 MESSAGES l' || rtrim(a.tabname) || '.msg insert into ' || rtrim(a.tabschema)||'.'||rtrim(a.tabname) ||';'from syscat.tables a where  exists (select * from syscat.columns b where b.generated='A' and a.tabname=b.tabname and  a.tabschema=b.tabschema)  and a.tabschema not like 'SYS%' and a.type='T'" >> $outPath/newDb_loadWithIdentity.sql

##### --- Search for all tables of char and varchar types to increase them.
# Create the alter table command increasing 30% the varchar and char filed's length.

db2 -x "select 'alter table ' || rtrim(tabschema)||'.'||rtrim(tabname) || ' alter column ' ||rtrim(colname) ||' set data type ' || rtrim(typename) || '(' || trim(both '0' from char(int(length * 1.3))) || ');' from syscat.columns where typename like '%CHAR' and tabname exists (select tabname from syscat.tables where type='T' and tabschema not like 'SYS%' and tabschema not like 'DB2INST%' )" | tr -s " " >> $outPath/newDb_tChartoIncrease.sql


echo -e "\nRunning the export.."
cd $outPath
db2 -tvf newDb_export.sql | tee newDb_export.out >> $tCurrentPath/$tDBNAME.rebuild.log
cd $tCurrentPath

# Getting the page size used to create the default bufferpool.
tPAGESIZE=$(db2 -x "select pagesize from syscat.bufferpools where BPNAME='IBMDEFAULTBP'")

# Create the config file:
echo -e "DB:$tDBNAME" > $outPath/rebuild.conf
echo -e "PAGESIZE:$tPAGESIZE" >> $outPath/rebuild.conf

cp rebuild_db.ksh $outPath/rebuld_db_onTarget.ksh
chmod 740 $outPath/rebuld_db_onTarget.ksh

echo -e "\nWarning.."
# Count the number of export done.
tExportCheck=$(cat $outPath/newDb_export.out | grep export | wc -l)
echo -e "\n-- The number of export were: $tExportCheck"

# Count the number of SQL3107W messages.
tExportCheck=$(cat $outPath/newDb_export.out | grep SQL3107W | wc -l)
echo -e "-- The number of SQL3107W messages were: $tExportCheck"

# List all SQL messages different of SQL3107W
tExportCheck=$(cat $outPath/newDb_export.out | grep SQL | grep -v SQL3107W)
if [[ -n $tExportCheck ]]; then
    echo "-- The following SQL were found during the export:"
    echo "$tExportCheck" | tr '.' '\n'
fi

# Checking if there is any table with a column set as generated that it's not always/default.
db2 "select rtrim(tabschema), rtrim(tabname), rtrim(identity) from syscat.columns where generated='A'" | grep -i A | tr -s " " >> tINDENTITY.tmp

db2 terminate > /dev/null

while read line
do
    tIDENTITY=$(echo $line | cut -f3 -d" ")
    if [[ $tIDENTITY = "N" ]]; then
	tTabName=$(echo $line | cut -f2 -d" ")
	tSchema=$(echo $line | cut -f1 -d" ")
	echo -e "     * $tSchema.$tTabName" >> tINDENTITYasN.tmp
    fi

done < tINDENTITY.tmp

if [[ -s tINDENTITYasN.tmp ]]; then
   echo -e "-- There is one or more columns created as generated and it's not Identity Always/Default. Please rerun the export for tables:"

   cat tINDENTITYasN.tmp
fi

# Checking for temporary tablespaces to warn about problem with "AUTORESIZE NO" parameter.
tTemporaryTbspace=$(cat $outPath/newDb_db2look.sql | grep -i "AUTORESIZE NO") 

# Check for create bufferpools statement that it's using the "NOT EXTENDED STORAGE" option that will cause the sql fail.
tCreateBuffer=$(cat $outPath/newDb_db2look.sql | grep -i "NOT EXTENDED STORAGE")

if [[ -n $tTemporaryTbspace ]] || [[ -n $tCreateBuffer ]]; then
   echo -e "\n-- Please check the newDb_db2look.sql file."
fi

if [[ -n $tTemporaryTbspace ]]; then
    echo -e "     * The 'create user temporary tablespace' statement has the 'AUTORESIZE NO' option that will cause the sql fail."
fi

if [[ -n $tCreateBuffer ]]; then
    echo -e "     * The following sqls will fail due the 'NOT EXTENDED STORAGE' option:"
    echo -e "       $tCreateBuffer" | tr ";" "\n"
fi

echo -e "\nCreating the tar file.."
tar -cvf $tDBNAME"_rebuild".tar  $outPath > /dev/null
if [[ $? -ne 0 ]]; then
        echo -e "\nThe tar command failed, The $outPath directory will not be deleted."
else
        #gzip $outPath.tar
        rm -rf $outPath
fi

echo -e "\nResult.."
echo -e "\n-- $tDBNAME"_rebuild".tar was created with: db2look, export outputs\n"

# Deleting the temporary file/directory.
rm -f tSetIntegrity.sql
rm -f tINDENTITY.tmp
rm -f tINDENTITYasN.tmp

exit 0