Shell script for MV script call

learnerlearner · 06-02-2009, 01:22 PM

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?

Tinkster · 06-02-2009, 04:04 PM

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

onebuck · 06-02-2009, 05:51 PM

Hi,

Tink, that's not fair. You didn't sign his homework.

learnerlearner · 06-03-2009, 02:42 AM

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 :-)