Linux

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

I don't understand this part just after the sequence which you indicate was problematic in your original script:

It would appear that this is replacing $TN.${ecmdate}.sql with an empty file (and an error message, I would guess) before the new $tmpfile_cln is used for anything. Maybe you intended to "cat $tmpfile_cln > $TN.${ecmdate}.sql"? (But then why not do it the other way around inside the conditional in the first place.)

Thanks a lot ERA !!!!

It is working fine

Hi Era ,

Once again , I am having issue if there are multiple occurances of word view in a file . when I ran the code with sh -x , I get the following messages .


sh -x migration_orig.shl tst103 daya opsdm002
+ [[ -n 1 ]]
+ return
+ USAGE=USAGE: migration_orig.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=20080513
+ 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
+ db2 -x select tabname from syscat.tables where tabschema='DAYA' and type='T'
+ 1> table.lst
+ cat table.lst
DIM_COMPANY
DIM_DATE
+ cat table.lst
+ 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.20080513.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.20080513.sql
+ tr a-z A-Z
+ 1> dim_company.20080513.sql
+ read tname
+ cat dim_company.20080513.sql
+ egrep -v ^--|^$|CONNECT|COMMIT|TERMINATE
+ 1> /tmp/tmpfile_cln
+ cat /tmp/tmpfile_cln
+ egrep -v SET CURRENT
+ 1> dim_company.20080513.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.20080513.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.20080513.sql
+ typeset -l source_schema
+ typeset -l target_schema
+ cat dim_company.20080513.sql
+ sed -e s/daya/opsdm002/g
+ 1> /tmp/tmpfile_cln
+ cat dim_company.20080513.sql
+ 1> /tmp/tmpfile_cln
+ [ daya=DAYA ]
+ sed -e s/\(TEST[^"]*\)/\1_S02/g /tmp/tmpfile_cln
+ 1> dim_company.20080513.sql
+ grep VIEW dim_company.20080513.sql
+ echo file does not have view in it
file does not have view in it
+ cat dim_company.20080513.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
+ 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_2') and btype in ('T','V','S')
+ 1>> /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
+ read tname
+ cat /tmp/tmpfile_tab_dim_date
+ db2look -d TST103 -z DAYA -t dim_date -e -x
+ 1>> dim_date.20080513.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.20080513.sql
+ tr a-z A-Z
+ 1> dim_date.20080513.sql
+ read tname
+ db2look -d TST103 -z DAYA -t DIM_DATE_2 -e -x
+ 1>> dim_date.20080513.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.20080513.sql
+ tr a-z A-Z
+ 1> dim_date.20080513.sql
+ read tname
+ db2look -d TST103 -z DAYA -t DIM_DATE_VIEW -e -x
+ 1>> dim_date.20080513.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
+ tr a-z A-Z
+ cat dim_date.20080513.sql
+ 1> dim_date.20080513.sql
+ read tname
+ egrep -v ^--|^$|CONNECT|COMMIT|TERMINATE
+ cat dim_date.20080513.sql
+ 1> /tmp/tmpfile_cln
+ egrep -v SET CURRENT
+ cat /tmp/tmpfile_cln
+ 1> dim_date.20080513.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.20080513.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.20080513.sql
+ typeset -l source_schema
+ typeset -l target_schema
+ cat dim_date.20080513.sql
+ sed -e s/daya/opsdm002/g
+ 1> /tmp/tmpfile_cln
+ cat dim_date.20080513.sql
+ 1> /tmp/tmpfile_cln
+ [ daya=DAYA ]
+ sed -e s/\(TEST[^"]*\)/\1_S02/g /tmp/tmpfile_cln
+ 1> dim_date.20080513.sql
+ grep VIEW dim_date.20080513.sql
CREATE VIEW OPSDM002.DIM_DATE_VIEW AS SELECT DT_SYS_ID , FULL_DT FROM OPSDM002.DIM_DATE;
CREATE VIEW OPSDM002.DIM_DATE_2 AS SELECT WEEK_BEGN_DT,QUARTER_NBR,DT_SYS_ID
+ + cut -d . -f2
+ awk /VIEW/ {print $3} dim_date.20080513.sql
VW_VAR=DIM_DATE_VIEW
DIM_DATE_2
+ echo VW_DIM_DATE_VIEW
DIM_DATE_2
VW_DIM_DATE_VIEW
DIM_DATE_2
+ sed -e s/DIM_DATE_VIEW DIM_DATE_2/VW_DIM_DATE_VIEW DIM_DATE_2/g dim_date.20080513.sql
+ 1> /tmp/tmpfile_cln
sed: Function s/DIM_DATE_VIEW cannot be parsed.
+ cat /tmp/tmpfile_cln
+ 1> dim_date.20080513.sql
+ 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
+ db2 terminate
DB20000I The TERMINATE command completed successfully.

The file comes as zero byte as the view changes are not parsed .

Thanks as always !

Looks like you have a quoting issue or something here; look into that.

Generally if you have a variable interpolation, put it in double quotes. You have judicious single quotes around parts which don't need any quoting at all, but don't quote the "hard" parts -- the variable values.

Code:

's/'"$SOME_VARIABLE"'/'"$SOME_OTHER_VARIABLE"'/g'  # actually the single quotes are completely needless
s/"$SOME_VARIABLE"/"$SOME_OTHER_VARIABLE"/g   # slightly careless, but actually equivalent
"s/$SOME_VARIABLE/$SOME_OTHER_VARIABLE/g"    # ditto

Era ,
Its stil the same . I took out the section of code with view change and hardcoded it. the sh -x is as below

sh -x view_add.shl
+ [[ -n 1 ]]
+ return
+ grep VIEW db2look_dim_provider.ddl1
CREATE VIEW OPSDM001.PROVIDER_MBR_PRI ( MBR_PRI_PROV_SYS_ID, MBR_PRI_COS_PROV_SPCL_CD,
CREATE VIEW OPSDM001.PROVIDER_REF ( REF_PROV_SYS_ID, REF_COS_PROV_SPCL_CD,
CREATE VIEW OPSDM001.PROVIDER_SRVC ( SRVC_PROV_SYS_ID, SRVC_COS_PROV_SPCL_CD,
+ + awk /VIEW/ {print $3} db2look_dim_provider.ddl1
+ cut -d . -f2
VW_VAR=PROVIDER_MBR_PRI
PROVIDER_REF
PROVIDER_SRVC
+ echo VW_PROVIDER_MBR_PRI
PROVIDER_REF
PROVIDER_SRVC
VW_PROVIDER_MBR_PRI
PROVIDER_REF
PROVIDER_SRVC
+ sed -e s/PROVIDER_MBR_PRI
PROVIDER_REF
PROVIDER_SRVC/VW_PROVIDER_MBR_PRI
PROVIDER_REF
PROVIDER_SRVC/g db2look_dim_provider.ddl1
+ 1> db2look_dim_provider.ddl2
sed: Function s/PROVIDER_MBR_PRI cannot be parsed.

Daya

Your variable contains multiple lines, is that on purpose? sed can't cope with that really.