05-12-2008
Thanks again ERA !
As you mentioned I changed grep to upper case and it works.
Now , I am posting the script with sh -x .
#sh -x migration_orig.shl tst103 daya opsdm002
+ [[ -n 1 ]]
+ return
+ USAGE=USAGE: miglook.shl [Source_DBNAME-required] [Source_Schema] [Target_Schema]
+ [[ 3 -lt 3 ]]
+ dbname=tst103
+ source_schema=daya
+ target_schema=opsdm002
+ typeset -u dbname
+ tmpfile_tab=/tmp/tmpfile_tab
+ tmpfile_dep=/tmp/tmpfile_tab_dep
+ tmpfile_cln=/tmp/tmpfile_cln
+ + date +%Y%m%d
ecmdate=20080512
+ db2 connect to TST103
Database Connection Information
Database server = DB2/AIX64 9.1.2
SQL authorization ID = DRAM
Local database alias = TST103
+ [ daya ]
+ source_schema=daya
+ [ opsdm002 ]
+ target_schema=opsdm002
+ typeset -u source_schema
+ typeset -u target_schema
+ cat table.lst
+ read TN
+ typeset -l TN
+ echo dim_date
+ 1> /tmp/tmpfile_tab_dim_date
+ db2 -x select tabname from syscat.tabdep where bschema=upper('DAYA') and bname=upper('dim_date') and btype in ('T','V','S')
+ 1> /tmp/tmpfile_tab_dep_dim_date
+ cat /tmp/tmpfile_tab_dep_dim_date
+ read line
+ db2 -x select tabname from syscat.tabdep where bschema=upper('DAYA') and bname=upper('DIM_DATE_VIEW') and btype in ('T','V','S')
+ 1>> /tmp/tmpfile_tab_dep_dim_date
+ read line
+ cat /tmp/tmpfile_tab_dep_dim_date
+ 1>> /tmp/tmpfile_tab_dim_date
+ cat /tmp/tmpfile_tab_dim_date
+ read tname
+ db2look -d TST103 -z DAYA -t dim_date -e -x
+ 1>> dim_date.20080512.sql
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DRAM
-- Specified SCHEMA is: DAYA
-- The db2look utility will consider only the specified tables
-- Creating DDL for table(s)
-- Schema name is ignored for the Federated Section
+ cat dim_date.20080512.sql
+ tr a-z A-Z
+ 1> dim_date.20080512.sql
+ read tname
+ db2look -d TST103 -z DAYA -t DIM_DATE_VIEW -e -x
+ 1>> dim_date.20080512.sql
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DRAM
-- Specified SCHEMA is: DAYA
-- The db2look utility will consider only the specified tables
-- Creating DDL for table(s)
-- Schema name is ignored for the Federated Section
+ cat dim_date.20080512.sql
+ tr a-z A-Z
+ 1> dim_date.20080512.sql
+ read tname
+ cat dim_date.20080512.sql
+ egrep -v ^--|^$|CONNECT|COMMIT|TERMINATE
+ 1> /tmp/tmpfile_cln
+ cat /tmp/tmpfile_cln
+ egrep -v SET CURRENT
+ 1> dim_date.20080512.sql
+ echo CONNECT TO TST103 ;
+ 1> /tmp/tmpfile_cln
+ echo SET SESSION_USER OPSDM002 ;
+ 1>> /tmp/tmpfile_cln
+ echo SET CURRENT SCHEMA OPSDM002 ;
+ 1>> /tmp/tmpfile_cln
+ cat dim_date.20080512.sql
+ 1>> /tmp/tmpfile_cln
+ echo COMMIT WORK ;
+ 1>> /tmp/tmpfile_cln
+ echo CONNECT RESET ;
+ 1>> /tmp/tmpfile_cln
+ echo TERMINATE ;
+ 1>> /tmp/tmpfile_cln
+ [[ DAYA != OPSDM002 ]]
+ cat /tmp/tmpfile_cln
+ sed -e s/DAYA/OPSDM002/g
+ 1> dim_date.20080512.sql
+ typeset -l source_schema
+ typeset -l target_schema
+ cat dim_date.20080512.sql
+ sed -e s/daya/opsdm002/g
+ 1> /tmp/tmpfile_cln
+ cat dim_date.20080512.sql
+ 1> /tmp/tmpfile_cln
+ sed -e s/\(TEST[^"]*\)/\1_S02/g /tmp/tmpfile_cln
+ 1> dim_date.20080512.sql
+ grep VIEW dim_date.20080512.sql
CREATE VIEW OPSDM002.DIM_DATE_VIEW AS SELECT DT_SYS_ID , FULL_DT FROM OPSDM002.DIM_DATE;
+ + awk /VIEW/ {print $3} dim_date.20080512.sql
+ cut -d . -f2
VW_VAR=DIM_DATE_VIEW
+ echo VW_DIM_DATE_VIEW
VW_DIM_DATE_VIEW
+ sed -e s/DIM_DATE_VIEW/VW_DIM_DATE_VIEW/g dim_date.20080512.sql
+ 1> /tmp/tmpfile_cln
+ cat /tmp/tmpfile_cln
CONNECT TO TST103 ;
SET SESSION_USER OPSDM002 ;
SET CURRENT SCHEMA OPSDM002 ;
CREATE TABLE "OPSDM002 "."DIM_DATE" (
"DT_SYS_ID" SMALLINT NOT NULL ,
"FULL_DT" DATE NOT NULL ,
"DAY_ABBR_CD" CHAR(3) NOT NULL ,
"MONTH_NM" CHAR(9) NOT NULL ,
"DAY_WK" SMALLINT NOT NULL ,
"WEEK_DAY_NM" CHAR(9) NOT NULL ,
"MONTH_NBR" SMALLINT NOT NULL ,
"LST_DAY_MO_IND" CHAR(1) NOT NULL ,
"DAY_NBR" SMALLINT NOT NULL ,
"MONTH_ABBR_CD" CHAR(3) NOT NULL ,
"QUARTER_NM" CHAR(6) NOT NULL ,
"QUARTER_NBR" SMALLINT NOT NULL ,
"YEAR_NBR" SMALLINT NOT NULL ,
"SAME_WEEK_DAY_YR_AGO_DT" DATE NOT NULL ,
"SEQ_NBR" SMALLINT NOT NULL ,
"WEEK_BEGN_DT" DATE NOT NULL ,
"WEEK_DAY_IND" CHAR(1) NOT NULL ,
"WEEK_NBR_YR" SMALLINT NOT NULL ,
"YEAR_MO" CHAR(6) NOT NULL ,
"LOAD_DT" DATE NOT NULL WITH DEFAULT CURRENT DATE ,
"UPDT_DT" DATE NOT NULL WITH DEFAULT CURRENT DATE )
COMPRESS YES
DISTRIBUTE BY HASH("DT_SYS_ID")
IN "TESTTBLSP_S02" ;
CREATE VIEW OPSDM002.VW_DIM_DATE_VIEW AS SELECT DT_SYS_ID , FULL_DT FROM OPSDM002.DIM_DATE;
GRANT ALTER ON TABLE "OPSDM002 "."DIM_DATE" TO USER "DRAM " ;
GRANT DELETE ON TABLE "OPSDM002 "."DIM_DATE" TO USER "DRAM " ;
GRANT INDEX ON TABLE "OPSDM002 "."DIM_DATE" TO USER "DRAM " ;
GRANT INSERT ON TABLE "OPSDM002 "."DIM_DATE" TO USER "DRAM " ;
GRANT SELECT ON TABLE "OPSDM002 "."DIM_DATE" TO USER "DRAM " ;
GRANT UPDATE ON TABLE "OPSDM002 "."DIM_DATE" TO USER "DRAM " ;
GRANT REFERENCES ON TABLE "OPSDM002 "."DIM_DATE" TO USER "DRAM " ;
COMMIT WORK ;
CONNECT RESET ;
TERMINATE ;
+ typeset -u source_schema
+ typeset -u target_schema
+ rm /tmp/tmpfile_tab_dim_date
+ rm /tmp/tmpfile_tab_dep_dim_date
+ rm /tmp/tmpfile_cln
+ read TN
+ typeset -l TN
+ echo dim_company
+ 1> /tmp/tmpfile_tab_dim_company
+ db2 -x select tabname from syscat.tabdep where bschema=upper('DAYA') and bname=upper('dim_company') and btype in ('T','V','S')
+ 1> /tmp/tmpfile_tab_dep_dim_company
+ cat /tmp/tmpfile_tab_dep_dim_company
+ read line
+ cat /tmp/tmpfile_tab_dep_dim_company
+ 1>> /tmp/tmpfile_tab_dim_company
+ cat /tmp/tmpfile_tab_dim_company
+ read tname
+ db2look -d TST103 -z DAYA -t dim_company -e -x
+ 1>> dim_company.20080512.sql
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DRAM
-- Specified SCHEMA is: DAYA
-- The db2look utility will consider only the specified tables
-- Creating DDL for table(s)
-- Schema name is ignored for the Federated Section
+ cat dim_company.20080512.sql
+ tr a-z A-Z
+ 1> dim_company.20080512.sql
+ read tname
+ cat dim_company.20080512.sql
+ egrep -v ^--|^$|CONNECT|COMMIT|TERMINATE
+ 1> /tmp/tmpfile_cln
+ cat /tmp/tmpfile_cln
+ egrep -v SET CURRENT
+ 1> dim_company.20080512.sql
+ echo CONNECT TO TST103 ;
+ 1> /tmp/tmpfile_cln
+ echo SET SESSION_USER OPSDM002 ;
+ 1>> /tmp/tmpfile_cln
+ echo SET CURRENT SCHEMA OPSDM002 ;
+ 1>> /tmp/tmpfile_cln
+ cat dim_company.20080512.sql
+ 1>> /tmp/tmpfile_cln
+ echo COMMIT WORK ;
+ 1>> /tmp/tmpfile_cln
+ echo CONNECT RESET ;
+ 1>> /tmp/tmpfile_cln
+ echo TERMINATE ;
+ 1>> /tmp/tmpfile_cln
+ [[ DAYA != OPSDM002 ]]
+ cat /tmp/tmpfile_cln
+ sed -e s/DAYA/OPSDM002/g
+ 1> dim_company.20080512.sql
+ typeset -l source_schema
+ typeset -l target_schema
+ cat dim_company.20080512.sql
+ sed -e s/daya/opsdm002/g
+ 1> /tmp/tmpfile_cln
+ cat dim_company.20080512.sql
+ 1> /tmp/tmpfile_cln
+ sed -e s/\(TEST[^"]*\)/\1_S02/g /tmp/tmpfile_cln
+ 1> dim_company.20080512.sql
+ grep VIEW dim_company.20080512.sql
+ cat dim_company.20080512.sql
+ 1> /tmp/tmpfile_cln
+ typeset -u source_schema
+ typeset -u target_schema
+ rm /tmp/tmpfile_tab_dim_company
+ rm /tmp/tmpfile_tab_dep_dim_company
+ rm /tmp/tmpfile_cln
+ read TN
+ db2 terminate
DB20000I The TERMINATE command completed successfully.
apsp8642:/dmartdev/dram/work #
If you notice , the cat file shows the VW_DIM_DATE_VIEW in the output with sh -x but the generated files are not the same . I have been trying but I am unable to get the desired output.
The output file has the following line
CREATE VIEW OPSDM002.DIM_DATE_VIEW AS SELECT DT_SYS_ID , FULL_DT FROM OPSDM002.DIM_DATE;
While I want the output like this
CREATE VIEW OPSDM002.VW_DIM_DATE_VIEW AS SELECT DT_SYS_ID , FULL_DT FROM OPSDM002.DIM_DATE;
Thanks,
Daya
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