LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 06-02-2009, 02:22 PM   #1
learnerlearner
LQ Newbie
 
Registered: Apr 2009
Posts: 6

Rep: Reputation: 0
Shell script for MV script call


Hi,
I have a sql file for creating MV (Materialized views) in sql and also partition by using partition by range method. The sample of the code is as below:

CREATE MATERIALIZED VIEW "USER1_ADMIN"."CONTENT_METRICS_TEST_MV"
PARALLEL PARTITION BY RANGE (occurred)
(PARTITION April1 VALUES LESS THAN (TO_DATE('01-04-2009', 'DD-MM-YYYY')),
PARTITION April2 VALUES LESS THAN (TO_DATE('11-04-2009', 'DD-MM-YYYY')),
PARTITION April3 VALUES LESS THAN (TO_DATE('21-04-2009', 'DD-MM-YYYY')),
PARTITION May1 VALUES LESS THAN (TO_DATE('01-05-2009', 'DD-MM-YYYY')),
PARTITION May2 VALUES LESS THAN (TO_DATE('11-05-2009', 'DD-MM-YYYY')),
PARTITION May3 VALUES LESS THAN (TO_DATE('21-05-2009', 'DD-MM-YYYY')),
PARTITION June1 VALUES LESS THAN (TO_DATE('01-06-2009', 'DD-MM-YYYY')),
PARTITION June2 VALUES LESS THAN (TO_DATE('11-06-2009', 'DD-MM-YYYY')),
PARTITION June3 VALUES LESS THAN (TO_DATE('21-06-2009', 'DD-MM-YYYY')),
PARTITION Others VALUES LESS THAN (maxvalue))

Now, the requirement goes like this:

1. I should execute this sql with the above mentioned dates (for a quarter).
2. I have to increment the date by 10 days in each of the statements and also the partition names ( E.g. April1,April2,April3.......) to the particular month and also the date corresponding to it.

I was successfull in achieving the first requirement. But the second requirement is a bit complex one.
Can anyone please help me out in this?
 
Old 06-02-2009, 05:04 PM   #2
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,067
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
Is this enough to get you going/give you an idea?

Code:
 for i in 2008 2009 2010; do for j in {1..12}; do date -d "${i}-${j}-01" "+%d-%m-%Y";date -d "${i}-${j}-11" "+%d-%m-%Y"; date -d "${i}-${j}-21" "+%d-%m-%Y"  ;done ; done

Cheers,
Tink
 
Old 06-02-2009, 06:51 PM   #3
onebuck
Moderator
 
Registered: Jan 2005
Location: Midwest USA, Central Illinois
Distribution: SlackwareŽ
Posts: 12,601
Blog Entries: 25

Rep: Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981
Hi,

Tink, that's not fair. You didn't sign his homework.
 
Old 06-03-2009, 03:42 AM   #4
learnerlearner
LQ Newbie
 
Registered: Apr 2009
Posts: 6

Original Poster
Rep: Reputation: 0
Thanks for the quick reply!!
But the command only prints the date. this is not what i was looking for. The output should be as below:

(PARTITION jul1 VALUES LESS THAN (TO_DATE('01-07-2009', 'DD-MM-YYYY')),
PARTITION jul2 VALUES LESS THAN (TO_DATE('11-07-2009', 'DD-MM-YYYY')),
PARTITION jul3 VALUES LESS THAN (TO_DATE('21-07-2009', 'DD-MM-YYYY')),
PARTITION aug1 VALUES LESS THAN (TO_DATE('01-08-2009', 'DD-MM-YYYY')),
PARTITION aug2 VALUES LESS THAN (TO_DATE('11-08-2009', 'DD-MM-YYYY')),
PARTITION aug3 VALUES LESS THAN (TO_DATE('21-08-2009', 'DD-MM-YYYY')),
PARTITION sep1 VALUES LESS THAN (TO_DATE('01-09-2009', 'DD-MM-YYYY')),
PARTITION sep2 VALUES LESS THAN (TO_DATE('11-09-2009', 'DD-MM-YYYY')),
PARTITION sep3 VALUES LESS THAN (TO_DATE('21-09-2009', 'DD-MM-YYYY')),

I tried it out with a simple for loop and have hardcoded the values and replaced the older ones with the above mentioned values by sed

for line in `cat file1.sql`;
do
cat contentmetrics_mv_final.sql | sed "s/April/$var7/g" | sed "s/May/$var8/g" | sed "s/June/$var9/g" | sed "s/01-04-2009/$date1/g" | sed "s/11-04-2009/$date2/g" | sed "s/21-04-2009/$date3/g" | sed "s/01-05-2009/$date4/g" | sed "s/11-05-2009/$date5/g" | sed "s/21-05-2009/$date6/g" | sed "s/01-06-2009/$date7/g" | sed "s/11-06-2009/$date8/g" | sed "s/21-06-2009/$date9/g" > file2.sql

done


But with this for loop i have to change it every quarter which is not the requirement.

I am trying to automate the execution of the SQL as below:

1. Execute the SQL for the current quarter.
2. Mention a check in the script to check inside the fie1.sql whether the date (last entry of the date in the partition name) is greater than the current date. If true, then increment all the values as mentioned in the post#1 and execute the file2.sql. If the check is false, simple;DO NOTHING.

Can this be done in an easy way?
Hope it is not confusing :-)
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Call One shell script from another shell script Sundaram Linux - Software 5 10-13-2006 04:59 AM
Shell Script call API treotan Programming 1 08-22-2005 11:59 PM
call a c program in a shell script jagman Programming 4 04-05-2005 05:58 PM
Call a shell script from php? jharper101 Programming 2 02-15-2005 01:51 AM
My shell script can't seem to call another script kakho Programming 3 04-17-2004 10:21 AM


All times are GMT -5. The time now is 06:13 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration