Franklin ,
May be I should have mentioned it earlier . This particular file doesn't have any grants on table. What if there are grants for the table too in the file . Your code will change all tables but I need only view names to be changed . So my approach is to grep the view name from create view statement and then make changes to that view name in grant part of ddl file . I am going to modify the ddl file and then cat it .
here is the modified file :
#cat dim_provider.sql
CONNECT TO TST103 ;
SET CURRENT SCHEMA OPSDM002 ;
CREATE TABLE OPSDM002.DIM_PROVIDER (
PROV_ID CHAR(7) NOT NULL ,
PROV_SYS_ID INTEGER NOT NULL ,
ZIP_CD CHAR(5) NOT NULL ,
UNIQ_PROV_SYS_ID BIGINT NOT NULL )
COMPRESS YES
IN TSOPS06 INDEX IN TSOPIX06 ;
CREATE ALIAS OPSDM002.PROVIDER FOR OPSDM002.DIM_PROVIDER;
CREATE VIEW OPSDM002.PROVIDER_MBR_PRI ( MBR_PRI_PROV_SYS_ID, MBR_PRI_COS_PROV_SPCL_CD,
MBR_PRI_DEA_ALPH_NBR, MBR_PRI_DEA_NBR, MBR_PRI_MPIN, MBR_PRI_NAT_ASSOC_BD_PHRM_NBR,
MBR_PRI_PROV_FST_NM, MBR_PRI_PROV_ID, MBR_PRI_PROV_LST_NM, MBR_PRI_SITE_CD,
DIM_PROVIDER.LOAD_DT, DIM_PROVIDER.UPDT_DT FROM DIM_PROVIDER;
CREATE VIEW OPSDM002.PROVIDER_REF ( REF_PROV_SYS_ID, REF_COS_PROV_SPCL_CD,
REF_DEA_ALPH_NBR, REF_DEA_NBR, REF_MPIN, REF_NAT_ASSOC_BD_PHRM_NBR,
REF_PROV_FST_NM, REF_PROV_ID, REF_PROV_LST_NM, REF_SITE_CD, REF_TIN,
DIM_PROVIDER.LOAD_DT, DIM_PROVIDER.UPDT_DT FROM DIM_PROVIDER;
CREATE VIEW OPSDM002.PROVIDER_SRVC ( SRVC_PROV_SYS_ID, SRVC_COS_PROV_SPCL_CD,
SRVC_DEA_ALPH_NBR, SRVC_DEA_NBR, SRVC_MPIN, SRVC_NAT_ASSOC_BD_PHRM_NBR,
SRVC_PROV_FST_NM, SRVC_PROV_ID, SRVC_PROV_LST_NM, SRVC_SITE_CD,
SRVC_TIN, SRVC_ZIP_CD, SRVC_UNIQ_PROV_SYS_ID, SRVC_LOAD_DT, SRVC_UPDT_DT)
FROM DIM_PROVIDER ;
GRANT SELECT ON TABLE OPSDM002.DIM_PROVIDER TO GROUP DSDBDEV ;
GRANT INSERT ON TABLE OPSDM002.DIM_PROVIDER TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.PROVIDER_MBR_PRI TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.PROVIDER_MBR_PRI TO GROUP UHCDMTST ;
GRANT CONTROL ON TABLE OPSDM002.PROVIDER_REF TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.PROVIDER_REF TO USER LTREXL1 ;
GRANT SELECT ON TABLE OPSDM002.PROVIDER_SRVC TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.PROVIDER_SRVC TO GROUP UHCDMTST ;
CREATE INDEX OPSDM002.RPL_IDX2_PROVIDER_TIN ON OPSDM002.RPL_DIM_PROVIDER
(TIN ASC)
ALLOW REVERSE SCANS;
CREATE INDEX OPSDM002.RPL_IDX2_TIN_UNIQ_PROV ON OPSDM002.RPL_DIM_PROVIDER
(TIN ASC,
UNIQ_PROV_SYS_ID ASC)
ALLOW REVERSE SCANS;
COMMIT WORK ;
I added the grants on table too which is most likely to be all ddls . How do we handle this .
I thank you for your time on this