05-09-2008
I am pasting the code that I am using .
I am trying to get ddl based on table names passed in table.lst file .
I am doing 2 things here.
a) Changing tablespace name to _S02 . ( Thanks for the help on that )
b) Adding VW as prefix to all the view names .
I have written error part in the section where I get the error .
I get error on adding VW section.
It works fine for tablespace name add . But doesn't change the View name.
i.e. view name = DAYA.DIM_DATE
DESIRED OUTPUT I WANT IS LIKE THIS
DAYA.VW_DIM_DATE
#!/bin/ksh
USAGE='USAGE: miglook.shl [Source_DBNAME-required] [Source_Schema] [Target_Schema]'
if [[ $# -lt 3 ]]; then
clear
echo "\nIncorrect usage!!\n"
echo "Script was invoked last time as: miglook.shl $*"
echo "\nThe correct usage is:\n"
echo "$USAGE"
echo "\n************************************************************************************************* ********************************************"
echo "Arguments:"
echo "Source_DBNAME i.e. Name of database from which to extract DDL"
echo "Source_Schema i.e. Name of schema from which objects DDL need to be extracted"
echo "Target_Schema i.e. Name of schema for which DDL need to be created\n"
echo "*************************************************************************************************** ******************************************\n"
exit
fi
dbname=$1
source_schema=$2
target_schema=$3
typeset -u dbname
tmpfile_tab=/tmp/tmpfile_tab
tmpfile_dep=/tmp/tmpfile_tab_dep
tmpfile_cln=/tmp/tmpfile_cln
ecmdate=`date +%Y%m%d`
db2 connect to $dbname
if [ $2 ]
then
source_schema=$2
else
source_schema='UHCDM001'
fi
if [ $3 ]
then
target_schema=$3
else
target_schema=${source_schema}
fi
typeset -u source_schema
typeset -u target_schema
cat table.lst | \
while read TN
do
#################################################################################################### #########################################################
# convert the table name read to lowercase to statdardise for tkt name.
#################################################################################################### #########################################################
typeset -l TN
#################################################################################################### #########################################################
# Create list of dependent objects for base table
#################################################################################################### #########################################################
echo $TN > ${tmpfile_tab}_$TN
db2 -x "select tabname from syscat.tabdep where bschema=upper('${source_schema}') and bname=upper('$TN') and btype in ('T','V','S') " > ${tmpfile_dep}_$TN
cat ${tmpfile_dep}_$TN | while read line
do
db2 -x "select tabname from syscat.tabdep where bschema=upper('${source_schema}') and bname=upper('$line') and btype in ('T','V','S') " >> ${tmpfile_dep}_$TN
done
cat ${tmpfile_dep}_$TN >> ${tmpfile_tab}_$TN
#################################################################################################### #########################################################
# Extracting DDL for base table and its dependent dependent objects along with their grants.
#################################################################################################### #########################################################
cat ${tmpfile_tab}_$TN | while read tname
do
db2look -d ${dbname} -z ${source_schema} -t ${tname} -e -x >> $TN.${ecmdate}.sql
cat $TN.${ecmdate}.sql | tr a-z A-Z > $TN.${ecmdate}.sql
done
#################################################################################################### ########################################################
# Cleaning the table
#################################################################################################### #########################################################
cat $TN.${ecmdate}.sql |egrep -v "^--|^$|CONNECT|COMMIT|TERMINATE" > $tmpfile_cln
cat $tmpfile_cln |egrep -v "SET CURRENT" > $TN.${ecmdate}.sql
echo "CONNECT TO $dbname ;" > $tmpfile_cln
echo "SET SESSION_USER $target_schema ;" >> $tmpfile_cln
echo "SET CURRENT SCHEMA $target_schema ; " >> $tmpfile_cln
cat $TN.${ecmdate}.sql >> $tmpfile_cln
echo "COMMIT WORK ; " >> $tmpfile_cln
echo "CONNECT RESET ; " >> $tmpfile_cln
echo "TERMINATE ; " >> $tmpfile_cln
#################################################################################################### #########################################################
# If moving same set of tables from one schema to other schema in same datebase then rename schema correctly
#################################################################################################### #########################################################
if [[ ${source_schema} != ${target_schema} ]]; then
cat $tmpfile_cln | sed -e 's/'${source_schema}'/'${target_schema}'/g' > $TN.${ecmdate}.sql
typeset -l source_schema
typeset -l target_schema
# cat $TN.${ecmdate}.sql1 > $TN.${ecmdate}.sql
cat $TN.${ecmdate}.sql | sed -e 's/'${source_schema}'/'${target_schema}'/g' > $tmpfile_cln
cat $TN.${ecmdate}.sql > $tmpfile_cln
sed -e 's/\(TEST[^"]*\)/\1_S02/g' $tmpfile_cln > $TN.${ecmdate}.sql
##############################################################################
#checks if the file has view in it ADD VW_ TO VIEW NAME (ERROR PART )
#############################################################################
grep -iq 'view' $TN.${ecmdate}.sql
if [ $? -eq 0 ]
then
VW_VAR=` cat $TN.${ecmdate}.sql | grep -i view | awk '{print $3}' | cut -d '.' -f2 `
echo " VW_$VW_VAR "
sed -e 's/'${VW_VAR}'/VW_'${VW_VAR}'/g' $TN.${ecmdate}.sql > $tmpfile_cln
else
cat $TN.${ecmdate}.sql > $tmpfile_cln
fi
$tmpfile_cln > $TN.${ecmdate}.sql
################################################################################################
#cat $TN.${ecmdate}.sql
typeset -u source_schema
typeset -u target_schema
fi
#################################################################################################### ########################################
# Cleaning up old files
#################################################################################################### #########################################################
rm ${tmpfile_tab}_$TN
rm ${tmpfile_dep}_$TN
rm $tmpfile_cln
done
db2 terminate
any help is appreciated .
Thanks a lot in advance !
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
hello,
I am trying to parse an error returned by a command inside the if statement but it is just displays the full error instead and then stops.
if ; then
echo "no such package"
else
echo "similar version found will use pkgrm"
fi
the above code just displays
please let me know... (2 Replies)
Discussion started by: rakeshou
2 Replies
2. Shell Programming and Scripting
:b:hi,
I have a script as given below:
pr_det="1"
if
then
awk ' BEGIN {printf("%23s","session")}' >> report.txt
awk ' BEGIN {printf "\n"} ' >> report.txt
else
awk ' BEGIN {printf("%55s","file_dsc")} ' >> report.txt
awk ' BEGIN {printf("%101s","no_recs")} '... (1 Reply)
Discussion started by: jisha
1 Replies
3. Shell Programming and Scripting
year=`date '+%Y'`
month=`date '+%m'`
day=`date '+%d'`
day=`expr $day - 1`
case $month in
1 | 3 | 5 | 7 | 8 | 10 | 12);;
if($day =7 ); then
$day=6
fi
4 | 6 | 9 | 11);;
if ; then
$day=31
fi
2);;
if ; then
if ; then (2 Replies)
Discussion started by: dannyd_y
2 Replies
4. Shell Programming and Scripting
HI i am getting error while executing the given statement
for filename in `cat a/file.lst`
do
if then
echo "Exit Code Description :File $filename - is missing in Input Directory" >a.log
exit
else
count1=`awk 'END {print NR}' $filename`
echo "$count1">>a.log
count2=`awk 'END {print... (4 Replies)
Discussion started by: ravi214u
4 Replies
5. Shell Programming and Scripting
hi,
When i try to run the code below, i get the following error
"ksh: syntax error: `(' unexpected"
i am not able to figure it out. Can anyone help me?
Code: (2 Replies)
Discussion started by: ragavhere
2 Replies
6. UNIX for Dummies Questions & Answers
Hi,
This is my script to catch any oracle errors.
In this, the $sqlerr returns
ORA-01017: invalid username/password; logon denied
when i specify wrong username/password
the if condition is failing. how can i resolve the issue.
the if statement gives error
sqloutput=`sqlplus -s -L... (1 Reply)
Discussion started by: Swapna173
1 Replies
7. Shell Programming and Scripting
I am working on script for stale nfs.
the file consists of
cat data01stale.log
- - - - /abcd/backup
- - - - /abcd/data
Script (16 Replies)
Discussion started by: nareshkumar522
16 Replies
8. Shell Programming and Scripting
Hi
Can you please tell me what is wrong with this line:
if && ]; then
basically i want to check if x = 12 and F (Filename) end with 'g'. But it is throwing syntax error. (7 Replies)
Discussion started by: rtagarra
7 Replies
9. Shell Programming and Scripting
Can anybody tell the correct way to use the following awk pattern check within an if statement?
When I run this command outside of the if statement
cat /tmp/test2.out | awk -v T=$TIME -v G=$GROUP -v C=$CDATE '$0 ~ T && $0 ~ G && $0 ~ C' | grep -i "Starting the group"
I get the following... (0 Replies)
Discussion started by: kieranfoley
0 Replies
10. UNIX for Beginners Questions & Answers
hello all
im new to unix and when i use below script i get an error :
#! /bin/bash
Echo -e "enter the name of the file : \c"
read file_name
if
then
echo "$file_name found"
else
echo "$file_name not found"
fi
running the script i get below error :
$ ./hello (26 Replies)
Discussion started by: Ibrahims1
26 Replies