#!/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