Hi ,
I am getting an error when I run the script for checking word "view" in a file . I am using if statement. like this

if [ cat $TN.${ecmdate}.sql1 | grep -i view ]
then
VW_VAR=` cat $TN.${ecmdate}.sql1 | grep -i view | awk '{print $3}' | cut -d '.' -f2 `
echo " VW_$VW_VAR "
sed -e 's/'${VW_VAR}'/VW_'${VW_VAR}'/g' $TN.${ecmdate}.sql1 > $TN.${ecmdate}.sql
else
cat $TN.${ecmdate}.sql1 > $TN.${ecmdate}.sql
fi

But I am getting error in the end like this

migration_orig.shl[106]: test: ] missing
grep: can't open ]

the script name is migration_orig.shl .

Any input is appreciated .

It looks like you're using /bin/sh

try:

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 !

I modified the add VW_ section. When I run it as sh -x scriptname <dbname> <source_schema> <target schema>
the output on screen is exactly what I want but the files generated is not the one. I am missing the output of files somewhere .

##############################################################################
#checks if the file has view in it
#############################################################################
cat $TN.${ecmdate}.sql | grep -i 'view'
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
cat $tmpfile_cln
# cat $tmpfile_cln > $TN.${ecmdate}.sql
else
cat $TN.${ecmdate}.sql > $tmpfile_cln
fi

################################################################################################

help please !

It would really help to see what the output is supposed to be, and what you are currently getting.

As an aside, you have multiple occurrences of an anti-pattern known as Useless Use of Cat, and also Useless Use of Grep and Useless Use of Test $?.

cat file | grep anything can always be rephrased as grep anything file as long as a single file is involved.

grep anything | awk '{ print $something }' can basically always be rephrased as awk '/anything/ { print $something }' (use the awk tolower() function on the search string to ignore case).

grep something; if [ $? -eq 0 ] ... can fruitfully be simplified to simply if grep something ...

This is not to pick on you (maybe pick a little bit on jim who ought to know better :-) -- rather an attempt at helping you make your scripts more idiomatic and readable.

if ( grep view dim_date.20080512.sql )
then
VW_VAR=` awk '/VIEW/ {print $3}' dim_date.20080512.sql | cut -d '.' -f2 `
echo " VW_${VW_VAR} "
sed -e 's/'${VW_VAR}'/VW_'${VW_VAR}'/g' dim_date.20080512.sql
cat $tmpfile_cln
else
cat dim_date.20080512.sql
fi

I am not getting the value for VW_VAR .

Please help here ......

You are grepping for lowercase view but in the awk script, you act on uppercase VIEW.

Anyway, solving the original problem is probably a more pressing issue at the moment; maybe then you can address stylistic issues in the scripts, and revert to a working version if a change turns out to break stuff. Sorry if I managed to lead you astray. (Having said that, the parentheses around the grep are redundant ...)

An example of the sh -x output as compared to what happens in reality would still be a good help.