Hi ,
I am running this script ( pasting only error code ) to generate some ddl definition for tables . But what I want is to break out of the db2look part when the base_table is not like DIM_$TN or FACT_$TN . After this it should come back to while loop to read the next TN . I read the other threads too but still not clear .

db2 -x "select tabname from syscat.tables where tabschema='${source_schema}' and type='A'" > table_alias.lst
cat table_alias.lst | \
while read TN
do

typeset -l TN


base_table=`db2 -x "select base_tabname from syscat.tables where tabschema='${source_schema}' and tabname=upper('$TN') " `
echo ${base_table}
if [ ${base_table} != "DIM_$TN" || ${base_table} != "FACT_$TN" ] ; then
break
else
continue
fi
db2look -d ${dbname} -z ${source_schema} -t ${base_table} -e -x > VIEW.${source_schema}.$TN.${ecmdate}.sql
tr 'a-z' 'A-Z' < VIEW.${source_schema}.$TN.${ecmdate}.sql > $tmpfile_cln
cat $tmpfile_cln > VIEW.${source_schema}.$TN.${ecmdate}.sql
cat VIEW.${source_schema}.$TN.${ecmdate}.sql

Here is the sh -x part of it .

#sh -x alias_creation.shl uhcdev01 opsdm001 opsdm002
+ [[ -n 1 ]]
+ return
+ USAGE=USAGE: alias_creation.shl [Source_DBNAME-required] [Source_Schema] [Target_Schema]
+ [[ 3 -lt 3 ]]
+ dbname=uhcdev01
+ source_schema=opsdm001
+ target_schema=opsdm002
+ typeset -u dbname
+ tmpfile_tab=/tmp/tmpfile_tab
+ tmpfile_dep=/tmp/tmpfile_tab_dep
+ tmpfile_cln=/tmp/tmpfile_cln
+ tmpfile_cln1=/tmp/tmpfile_cln1
+ + date +%Y%m%d
ecmdate=20080612
+ db2 connect to UHCDEV01

Database Connection Information

Database server = DB2/AIX64 9.1.2
SQL authorization ID = DRAM
Local database alias = UHCDEV01

+ [ opsdm001 ]
+ source_schema=opsdm001
+ [ opsdm002 ]
+ target_schema=opsdm002
+ typeset -u source_schema
+ typeset -u target_schema
+ cat table_alias.lst
+ read TN
+ typeset -l TN
+ + db2 -x select base_tabname from syscat.tables where tabschema='OPSDM001' and tabname=upper('unique_provider_targ')
base_table=
+ echo

+ [ != DIM_unique_provider_targ
alias_creation.shl[65]: test: ] missing
+ != FACT_unique_provider_targ ]
alias_creation.shl[65]: !=: not found
+ continue
+ read TN
+ typeset -l TN
+ + db2 -x select base_tabname from syscat.tables where tabschema='OPSDM001' and tabname=upper('unique_provider_uhg_resp')
base_table=
+ echo

+ [ != DIM_unique_provider_uhg_resp
alias_creation.shl[65]: test: ] missing
+ != FACT_unique_provider_uhg_resp ]
alias_creation.shl[65]: !=: not found
+ continue
+ read TN
+ typeset -l TN
+ + db2 -x select base_tabname from syscat.tables where tabschema='OPSDM001' and tabname=upper('zip')
base_table=VW_ZIP
+ echo VW_ZIP
VW_ZIP
+ [ VW_ZIP != DIM_zip
alias_creation.shl[65]: test: ] missing
+ VW_ZIP != FACT_zip ]
alias_creation.shl[65]: VW_ZIP: not found
+ continue
+ read TN
+ typeset -l TN
+ + db2 -x select base_tabname from syscat.tables where tabschema='OPSDM001' and tabname=upper('zip_customer_segment')
base_table=VW_ZIP_CUSTOMER_SEGMENT
+ echo VW_ZIP_CUSTOMER_SEGMENT
VW_ZIP_CUSTOMER_SEGMENT
+ [ VW_ZIP_CUSTOMER_SEGMENT != DIM_zip_customer_segment
alias_creation.shl[65]: test: ] missing
+ VW_ZIP_CUSTOMER_SEGMENT != FACT_zip_customer_segment ]
alias_creation.shl[65]: VW_ZIP_CUSTOMER_SEGMENT: not found
+ continue
+ read TN
+ typeset -l TN
+ + db2 -x select base_tabname from syscat.tables where tabschema='OPSDM001' and tabname=upper('zip_provider_srvc')
base_table=VW_ZIP_PROVIDER_SRVC
+ echo VW_ZIP_PROVIDER_SRVC
VW_ZIP_PROVIDER_SRVC
+ [ VW_ZIP_PROVIDER_SRVC != DIM_zip_provider_srvc
alias_creation.shl[65]: test: ] missing
+ VW_ZIP_PROVIDER_SRVC != FACT_zip_provider_srvc ]
alias_creation.shl[65]: VW_ZIP_PROVIDER_SRVC: not found
+ continue
+ read TN
+ db2 terminate

Thanks in advance

Break is used to break out of a loop, while continue is used to skip over a
step if it matches (fails to match) a condition.

Change this:
to this:
That should cause the script to skip to the next iteration of the while loop if
either of the first conditions is true, else execute the else part of the if -
else - fi test.

thanks a lot . This is working fine now

My bad . i was in bit haste to reply earlier . I had to test it in multiple scripts .

The newer script is giving weird result .

The comparison part is failing and db2look is skipping. After the comparison , the script should go for db2look but it is continuing to next iteration . Here is sh -x part when I ran the script .

#sh -x alias_creation.shl uhcetl01 opsdm001 opsdm002
+ [[ -n 1 ]]
+ return
+ USAGE=USAGE: alias_creation.shl [Source_DBNAME-required] [Source_Schema] [Target_Schema]
+ [[ 3 -lt 3 ]]
+ dbname=uhcetl01
+ source_schema=opsdm001
+ target_schema=opsdm002
+ typeset -u dbname
+ tmpfile_tab=/tmp/tmpfile_tab
+ tmpfile_dep=/tmp/tmpfile_tab_dep
+ tmpfile_cln=/tmp/tmpfile_cln
+ tmpfile_cln1=/tmp/tmpfile_cln1
+ + date +%Y%m%d
ecmdate=20080619
+ db2 connect to UHCETL01

Database Connection Information

Database server = DB2/AIX64 9.1.2
SQL authorization ID = DRAM
Local database alias = UHCETL01

+ [ opsdm001 ]
+ source_schema=opsdm001
+ [ opsdm002 ]
+ target_schema=opsdm002
+ typeset -u source_schema
+ typeset -u target_schema
+ db2 -x select tabname from syscat.tables where tabschema='OPSDM001' and type='A'
+ 1> table_alias.lst
+ cat table_alias.lst
+ read TN
+ typeset -u TN
+ + db2 -x select base_tabname from syscat.tables where tabschema='OPSDM001' and tabname=upper('CLNOPS_CUSTOMER_SEGMENT')
base_table=DIM_CLNOPS_CUSTOMER_SEGMENT
+ echo DIM_CLNOPS_CUSTOMER_SEGMENT
DIM_CLNOPS_CUSTOMER_SEGMENT
+ [[ DIM_CLNOPS_CUSTOMER_SEGMENT != DIM_CLNOPS_CUSTOMER_SEGMENT ]]
+ echo No match found
No match found
+ echo ###################################
###################################
+ continue
+ read TN
+ typeset -u TN
+ + db2 -x select base_tabname from syscat.tables where tabschema='OPSDM001' and tabname=upper('CUSTOMER_SEGMENT')
base_table=DIM_CUSTOMER_SEGMENT
+ echo DIM_CUSTOMER_SEGMENT
DIM_CUSTOMER_SEGMENT
+ [[ DIM_CUSTOMER_SEGMENT != DIM_CUSTOMER_SEGMENT ]]
+ echo No match found
No match found
+ echo ###################################
###################################
+ continue
+ read TN
+ typeset -u TN
+ + db2 -x select base_tabname from syscat.tables where tabschema='OPSDM001' and tabname=upper('DISCHARGE_STATUS_CODE')
base_table=DIM_DISCHARGE_STATUS_CODE
+ echo DIM_DISCHARGE_STATUS_CODE
DIM_DISCHARGE_STATUS_CODE
+ [[ DIM_DISCHARGE_STATUS_CODE != DIM_DISCHARGE_STATUS_CODE ]]
+ echo No match found
No match found
+ echo ###################################
###################################
+ continue
+ read TN
+ typeset -u TN
+ + db2 -x select base_tabname from syscat.tables where tabschema='OPSDM001' and tabname=upper('FEE_SCHEDULE_CODE')
base_table=DIM_FEE_SCHEDULE_CODE
+ echo DIM_FEE_SCHEDULE_CODE
DIM_FEE_SCHEDULE_CODE
+ [[ DIM_FEE_SCHEDULE_CODE != DIM_FEE_SCHEDULE_CODE ]]
+ echo No match found
No match found
+ echo ###################################
###################################
+ continue
+ read TN
+ typeset -u TN
+ + db2 -x select base_tabname from syscat.tables where tabschema='OPSDM001' and tabname=upper('GENERIC_THERAPEUTIC_CLASS')
base_table=DIM_GENERIC_THERAPEUTIC_CLASS
+ echo DIM_GENERIC_THERAPEUTIC_CLASS
DIM_GENERIC_THERAPEUTIC_CLASS
+ [[ DIM_GENERIC_THERAPEUTIC_CLASS != DIM_GENERIC_THERAPEUTIC_CLASS ]]
+ echo No match found
No match found
+ echo ###################################
###################################
+ continue
+ read TN
+ db2 terminate
DB20000I The TERMINATE command completed successfully.

While my script code is as below


db2 -x "select tabname from syscat.tables where tabschema='${source_schema}' and type='A'" > table_alias.lst
cat table_alias.lst | \
while read TN
do

typeset -u TN


#################################################################################################### #########################################################
# Extracting DDL for base table and its dependent dependent objects along with their grants.
#################################################################################################### #########################################################

base_table=`db2 -x "select base_tabname from syscat.tables where tabschema='${source_schema}' and tabname=upper('$TN') " `
echo ${base_table}

if [[ "${base_table}" != "DIM_$TN" || "${base_table}" != "FACT_$TN" ]] ; then
echo " No match found "
echo " ################################### "
continue
else
typeset -l TN
echo " hi "
echo ${base_table}
fi

db2look -d ${dbname} -z ${source_schema} -t ${base_table} -e -x > VIEW.${source_schema}.$TN.${ecmdate}.sql
tr 'a-z' 'A-Z' < VIEW.${source_schema}.$TN.${ecmdate}.sql > $tmpfile_cln
cat $tmpfile_cln > VIEW.${source_schema}.$TN.${ecmdate}.sql
cat VIEW.${source_schema}.$TN.${ecmdate}.sql

sed -n '/^CREATE TABLE/,/COMPRESS/p' VIEW.${source_schema}.$TN.${ecmdate}.sql > $tmpfile_cln

cat $tmpfile_cln | grep -v "CREATE" | grep -v "COMPRESS" | awk '{ print $1, "," }' > VIEW.${source_schema}.$TN.${ecmdate}.sql
sed -e '$s/,/)/' VIEW.${source_schema}.$TN.${ecmdate}.sql > $tmpfile_cln
cat $tmpfile_cln > VIEW.${source_schema}.$TN.${ecmdate}.sql

#################################################################################################### ########################################################
# Cleaning the table
#################################################################################################### #########################################################

cat VIEW.${source_schema}.$TN.${ecmdate}.sql |egrep -v "^--|^$|CONNECT|COMMIT|TERMINATE" > $tmpfile_cln
cat $tmpfile_cln |egrep -v "SET CURRENT" > VIEW.${source_schema}.$TN.${ecmdate}.sql
echo "CONNECT TO $dbname ;" > $tmpfile_cln
echo "SET SESSION_USER $source_schema ;" >> $tmpfile_cln
echo "SET CURRENT SCHEMA $source_schema ; " >> $tmpfile_cln
echo " CREATE VIEW $source_schema.vw_$TN ( " >> $tmpfile_cln
cat VIEW.${source_schema}.$TN.${ecmdate}.sql >> $tmpfile_cln
echo " as select " >> $tmpfile_cln
sed -e '$s/)/ /' VIEW.${source_schema}.$TN.${ecmdate}.sql > $tmpfile_cln1
cat $tmpfile_cln1 >> $tmpfile_cln
echo "FROM $source_schema.$base_table " >> $tmpfile_cln
echo " ; " >> $tmpfile_cln
echo " DROP ALIAS $source_schema.$TN ; " >> $tmpfile_cln
echo " CREATE ALIAS $source_schema.$TN for vw_$TN ; " >> $tmpfile_cln

if [ ${source_schema} = 'OPSDM001' ]
then
echo " GRANT SELECT ON TABLE $source_schema.$TN TO group uhcdmtst , group dsdbdev,group selcoe01 ,group closel01 ,group closel02 ,group closel04 ,group closel05 ;" >> $tmpfile_cln
elif [ ${source_schema} = 'CLODM001' ]
then
echo "GRANT SELECT ON TABLE $source_schema.$TN TO group uhcdmtst,group dsdbdev,group selcoe01 ,group closel01 ,group closel02 ,group closel04 ,group closel05 ;" >> $tmpfile_cln
elif [ ${source_schema} = 'UHCDM001' ]
then
echo "GRANT SELECT ON TABLE $source_schema.$TN TO group UHCDMTST,group UHCDMSEL,group UHCDMSE2,group UHCDMSE3,group UHCDMSE4,group UHCDMSE5,group UHCDMSE6,group UHCDMSE7,group UHCDMSE8,group UHCDMSE9,group UHCDMS10,group UHCDMS11,group UHCDMS12,group UHCDMS13,group UHCDMS14 ;" >>$tmpfile_cln
else
" "
fi
echo "COMMIT WORK ; " >> $tmpfile_cln
echo "CONNECT RESET ; " >> $tmpfile_cln
echo "TERMINATE ; " >> $tmpfile_cln
sed -e 's/"/ /g' $tmpfile_cln > VIEW.${source_schema}.$TN.${ecmdate}.sql

#cp VIEW.${source_schema}.$TN.${ecmdate}.sql /dba_dir/uhcetl01/ddl/schema02/view
#################################################################################################### ########################################
# Cleaning up old files
#################################################################################################### #########################################################

rm ${tmpfile_tab}_$TN
rm ${tmpfile_dep}_$TN
rm $tmpfile_cln
# rm VIEW.${source_schema}.$TN.${ecmdate}.sql
done

db2 terminate


Any help is appreciated ,