Shell script to rebuild an entire database - part 2.
This script should be put in the same path as the script provided in: "Shell script to rebuild an entire database - part 1. "
Click here to get the file
Size 3.6 kB - File type text/plain
File contents
#!/bin/ksh
#
# ---------------------------------------------------------------------
# This script use the rebuild.conf file to get the following parameters:
# - database name;
# - page size used to create the database;
#
# - Modifications:
# - Danilo C. Pereira - creation.
#
# ---------------------------------------------------------------------
. $HOME/.profile
### --- Variables
# Path to create the new database.
tDATAPATH=$1
# Database name.
tDBNAME=$(cat rebuild.conf| grep -i DB | cut -f2 -d":")
# Used to create the database setting a defaul value for bufferpools.
tPAGESIZE=$(cat rebuild.conf| grep -i PAGESIZE | cut -f2 -d":")
# Used to check if any row was rejected in the loads.
tLoadCheck=
# Used to check the number of args.
tNUMARGS=$#
# Used to check how many tables needs to run the set integrity command.
tNumtable=0
# Checking the the number of arguments.
if [[ $tNUMARGS -ne 1 ]]; then
echo "Usage: ./rebuld_db_onTarget.ksh <path to create the new database>"
echo "example: ./rebuld_db_onTarget.ksh /db2/db1"
exit -2
fi
# Creating the database:
db2 "create database $tDBNAME ON $tDATAPATH USING CODESET UTF-8 TERRITORY BR COLLATE USING UCA500R1_LEN_S1 PAGESIZE $tPAGESIZE"
# Running the db2look:
db2 -tvf newDb_db2look.sql | tee newDb_db2look.out
# Connecting to the database:
db2 connect to $tDBNAME
# Running the alter table statement to increase the size of the var fildes.
db2 -tvf newDb_tChartoIncrease.sql | tee newDb_tChartoIncrease.out
# Running the loads:
db2 -tvf newDb_load.sql | tee newDb_load.out
db2 -tvf newDb_loadWithIdentity.sql | tee newDb_loadWithIdentity.out
# creating the set integrity commands.
db2 -x "select 'set integrity for ',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,40),'immediate checked;' from syscat.tables where status = 'C' order by tabschema, tabname" >> tSetIntegrity.sql
tNumtable=$(cat tSetIntegrity.sql | wc -l)
cat tSetIntegrity.sql | tr -s " " | cut -f4 -d" " | tr '.' ' ' >> tSetInt1.out
awk ' $5=numtable { if ( NR < $5 ) { print $1 ".\"" $2 "\"," } if (NR == $5) { print $1 ".\"" $2 "\"" } } ' numtable=$tNumtable tSetInt1.out | tr '\n' ' ' >> tSetInt2.out
awk ' { print "set integrity for", $0 ,"immediate checked;" } ' tSetInt2.out > $tDBNAME.setIntegrity.sql
db2 -tvf $tDBNAME.setIntegrity.sql | tee $tDBNAME.setIntegrity.out
db2 terminate
# Checking if any rows have been rejected during the load.
tLoadCheck=$(cat newDb_load*.out | grep -i rejected | tr -s " " | cut -f6 -d" " | grep -v 0 | wc -l)
if [[ $tLoadCheck -ne 0 ]]; then
echo -e "\nThere are rows rejected. \nNumber of failed loads: $tLoadCheck"
fi
# Checking if the load has failed.
tloadCheck=$(cat newDb_load*.out | grep -i SQL | grep -v SQL3107W)
if [[ -n $tloadCheck ]]; then
echo -e "\nThe load has failed with the following error(s):"
echo -e "\n$tloadCheck"
fi
# Checking if the alter statement to increase varchar/char has failed.
tNUMALTER=$(cat newDb_tChartoIncrease.out | grep -i alter | wc -l)
tNUMSQL=$(cat newDb_tChartoIncrease.out | grep -i "The SQL command completed successfully." | wc -l)
if [[ $tNUMALTER != $tNUMSQL ]] then;
echo -e "\nPlease check the newDb_tChartoIncrease.out file. There alter statement failed."
fi
# Checking if the db2look sql has failed.
tERRORDB2LOOK=$(cat newDb_db2look.out | cut -f1 -d" " | sort -u | grep -i DB2)
if [[ -n $(echo $tERRORDB2LOOK | grep -v DB20000I) ]]; then
tERRORDB2LOOK=$(echo $tERRORDB2LOOK | tr " " "\n" | grep -v DB20000I)
echo -e "\nThe following errors were found in the db2look sql: $tERRORDB2LOOK"
fi
# Deleting the temporary files.
rm -f tSetIntegrity.sql
rm -f tSetInt1.out
rm -f tSetInt2.out
exit 0
