05-30-2008
This is input 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.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 ;
The desired output is :
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP UHCDMTST ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_REF TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_REF TO USER LTREXL1 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_SRVC TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_SRVC TO GROUP UHCDMTST ;
Thanks
10 More Discussions You Might Find Interesting
1. UNIX for Dummies Questions & Answers
There is a requirement that i need to replaced a pattern by another pattern in all the files in my entire file system. there are 1000s of file in the system. let the pattern is "calcuta". i have to replace this pattern by "kolkata" in all those files which contain "calcuta".
I am only able to... (12 Replies)
Discussion started by: palash2k
12 Replies
2. Shell Programming and Scripting
Hi,
I have file 1.txt with following entries as shown:
0152364|134444|10.20.30.40|015236433
0233654|122555|10.20.30.50|023365433
**
**
**
In file 2.txt I have the following entries as shown:
0152364|134444|10.20.30.40|015236433
0233654|122555|10.20.30.50|023365433... (4 Replies)
Discussion started by: imas
4 Replies
3. Shell Programming and Scripting
Hi all,
I'm looking for some help. I have a file (very long) that is organized like below:
>Cluster 0
0 283nt, >01_FRYJ6ZM12HMXZS... at +/99%
1 279nt, >01_FRYJ6ZM12HN12A... at +/99%
2 281nt, >01_FRYJ6ZM12HM4TS... at +/99%
3 283nt, >01_FRYJ6ZM12HM946... at +/99%
4 279nt,... (4 Replies)
Discussion started by: d.chauliac
4 Replies
4. UNIX for Dummies Questions & Answers
Hi I'm a newbie in unix and I'm having trouble in creating a script. I want to search for a pattern '_good' and insert new lines that contains '_bad', '_med', '_fail' while also ensure that the line contains _good is removed
here some of the data
UPDATE SCHOOL SET GRADE =... (1 Reply)
Discussion started by: sexyTrojan
1 Replies
5. Shell Programming and Scripting
Hi
I am having a code snippet
grant permission to all user
sts|ln|uSe|PSG
sajncht|se|Use|PPSPSG
psg|ln|use|TSPSG
sts_user.Me revoke
I need to change all occurance of use (uSe,Use,use) with USE. I am using the following sed command for this
sed 's//USE/g' s_sample.txt
Output: (7 Replies)
Discussion started by: sudeep.id
7 Replies
6. Shell Programming and Scripting
'Hi
I'm using the following code to extract the lines(and redirect them to a txt file) after the pattern match. But the output is inclusive of the line with pattern match.
Which option is to be used to exclude the line containing the pattern?
sed -n '/Conn.*User/,$p' > consumers.txt (11 Replies)
Discussion started by: essem
11 Replies
7. Shell Programming and Scripting
The sample file:
dept1: user1,user2,user3
dept2: user4,user5,user6
dept3: user7,user8,user9
I want to match by '/^dept2.*/' but don't want to have substring 'dept2:' in output. How to compose such regex? (8 Replies)
Discussion started by: urello
8 Replies
8. Shell Programming and Scripting
Hello Everybody,
I need a help in the below pattern matching and replacement issue
I have a file : emp.txt
21356 suresh 12/12/2012
23511 ramesh 11/06/2011
31456 biswajit 09/08/2013
53134 archan 06/02/2009
first field:- employee id, 2nd field is name and third field is date of joining
... (10 Replies)
Discussion started by: shellscripting
10 Replies
9. Shell Programming and Scripting
Hi Techies,
I need a help in finding junk characters and remove them from a Datafile.
we have a file and it had crores of records like below
SGSN_MCC_MNC=01150
but sometime due to the issue with sending server we are getting some junk characters in the middle of data like below
... (6 Replies)
Discussion started by: mahi_mayu069
6 Replies
10. UNIX for Dummies Questions & Answers
Hi all!
Thanks for taking the time to view this!
I want to grep out all lines of a file that starts with pattern 1 but also does not match with the second pattern.
Example:
Drink a soda
Eat a banana
Eat multiple bananas
Drink an apple juice
Eat an apple
Eat multiple apples
I... (8 Replies)
Discussion started by: demmel
8 Replies
LEARN ABOUT REDHAT
create_schema
CREATE
SCHEMA(7) SQL Commands CREATE SCHEMA(7)
NAME
CREATE SCHEMA - define a new schema
SYNOPSIS
CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ]
INPUTS
schemaname
The name of a schema to be created. If this is omitted, the user name is used as the schema name.
username
The name of the user who will own the schema. If omitted, defaults to the user executing the command. Only superusers may create
schemas owned by users other than themselves.
schema_element
An SQL statement defining an object to be created within the schema. Currently, only CREATE TABLE, CREATE VIEW, and GRANT are
accepted as clauses within CREATE SCHEMA. Other kinds of objects may be created in separate commands after the schema is created.
OUTPUTS
CREATE SCHEMA
Message returned if the command is successful.
ERROR: namespace "schemaname" already exists
If the schema specified already exists.
DESCRIPTION
CREATE SCHEMA will enter a new schema into the current database. The schema name must be distinct from the name of any existing schema in
the current database.
A schema is essentially a namespace: it contains named objects (tables, data types, functions, and operators) whose names may duplicate
those of other objects existing in other schemas. Named objects are accessed either by ``qualifying'' their names with the schema name as a
prefix, or by setting a search path that includes the desired schema(s).
Optionally, CREATE SCHEMA can include subcommands to create objects within the new schema. The subcommands are treated essentially the same
as separate commands issued after creating the schema, except that if the AUTHORIZATION clause is used, all the created objects will be
owned by that user.
NOTES
To create a schema, the invoking user must have CREATE privilege for the current database. (Of course, superusers bypass this check.)
Use DROP SCHEMA to remove a schema.
EXAMPLES
Create a schema:
CREATE SCHEMA myschema;
Create a schema for user joe --- the schema will also be named joe:
CREATE SCHEMA AUTHORIZATION joe;
Create a schema and create a table and view within it:
CREATE SCHEMA hollywood
CREATE TABLE films (title text, release date, awards text[])
CREATE VIEW winners AS
SELECT title, release FROM films WHERE awards IS NOT NULL;
Notice that the individual subcommands do not end with semicolons.
The following is an equivalent way of accomplishing the same result:
CREATE SCHEMA hollywood;
CREATE TABLE hollywood.films (title text, release date, awards text[]);
CREATE VIEW hollywood.winners AS
SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;
COMPATIBILITY
SQL92
SQL92 allows a DEFAULT CHARACTER SET clause in CREATE SCHEMA, as well as more subcommand types than are presently accepted by PostgreSQL.
SQL92 specifies that the subcommands in CREATE SCHEMA may appear in any order. The present PostgreSQL implementation does not handle all
cases of forward references in subcommands; it may sometimes be necessary to reorder the subcommands to avoid forward references.
In SQL92, the owner of a schema always owns all objects within it. PostgreSQL allows schemas to contain objects owned by users other than
the schema owner. This can happen only if the schema owner grants CREATE rights on his schema to someone else.
SQL - Language Statements 2002-11-22 CREATE SCHEMA(7)