Unix Script to exec. and schedule SQL query and Crystal report

Here is the scenario:

A staging table will be populated everyday from several huge bas table;
3 Crystal reports will be pulling data from the staging table;
The reports are scheduled to run at different times to avoid loads, from ePortfolio.

Here is the sequence fo what sohuld be happening:
1. Run SQL script to create  and populate the staging table;
2. Run report A when the staging table is created and populated; ie. the report can't be run until it receveid the status of the SQL script is completed;
3. Refreshed/repopulated the staging table when repot A is completed. ie. upon receiving the status of the report is compelted;
4. Run report B when receveid the status of the SQL script is completed;
5. Run reprot C when  receveid the status of the report B is completed.

Looking for a UNIX script that can schedule and execute the above jobs in the specified sequence.

Thanks.
HKBoyzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ahoffmannCommented:
write down your SQL statements for 1. to 5., then execute each with the proper command line tool (sqlplus, mysql, whatever). If these commands succedd as you expect, then simply write them in a shell script which you start using cron at the time you want.
0
HKBoyzAuthor Commented:
Do you have a sample script?
0
ahoffmannCommented:
no
you need to provide much more details: type of database, which table, which rows, which conditions, ...
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

HKBoyzAuthor Commented:
It's an Oracle DB. Here is the SQL to create and poupulate the staging table:

CREATE TABLE AT_STG (ACCT_POID_ID0, ACCOUNT_NO, SRVC_POID_ID0, RADIO_ID,
PAY_TYPE,last_status_t,effective_t,due,access_code2,credit_class)
AS
SELECT          /*+ FULL(ITEM_T) FULL(ACCOUNT_T) FULL(SERVICE_T) FULL (ACCOUNT_PRODUCTS_T)
FULL(PROFILE_XM_CREDIT_CLASS_T) FULL(BILLINFO_T) FULL(PROFILE_T) FULL (PROFILE_XM_FLAGS_WRITEOFF_T) */
DISTINCT
account_t.poid_id0, account_t.account_no, service_t.poid_id0,
service_t.login, billinfo_t.pay_type,service_t.last_status_t,MIN(item_t.effective_t) AS effective_t,
SUM(item_t.due) AS due,
      ACCOUNT_T.ACCESS_CODE2,PROFILE_XM_CREDIT_CLASS_T.CREDIT_CLASS
FROM
ACCOUNT_T ACCOUNT_T,
ITEM_T ITEM_T,
BILLINFO_T BILLINFO_T,
PROFILE_T PROFILE_T,
SERVICE_T SERVICE_T,
PROFILE_XM_CREDIT_CLASS_T PROFILE_XM_CREDIT_CLASS_T,
PROFILE_XM_FLAGS_WRITEOFF_T PROFILE_XM_FLAGS_WRITEOFF_T
WHERE ACCOUNT_T.POID_ID0=ITEM_T.ACCOUNT_OBJ_ID0
AND ITEM_T.BILLINFO_OBJ_ID0=BILLINFO_T.POID_ID0
AND ACCOUNT_T.POID_ID0=BILLINFO_T.ACCOUNT_OBJ_ID0
AND ACCOUNT_T.POID_ID0=PROFILE_T.ACCOUNT_OBJ_ID0 (+)
AND ACCOUNT_T.POID_ID0=SERVICE_T.ACCOUNT_OBJ_ID0
AND (PROFILE_T.POID_ID0=PROFILE_XM_CREDIT_CLASS_T.OBJ_ID0 (+))
AND (PROFILE_T.POID_ID0 = PROFILE_XM_FLAGS_WRITEOFF_T.OBJ_ID0(+))
AND SERVICE_T.POID_ID0 = ITEM_T.SERVICE_OBJ_ID0 (+)
AND ACCOUNT_T.POID_ID0<>1
AND ITEM_T.DUE<>0
AND ITEM_T.EFFECTIVE_T <> 0
AND (SUBSTR(ACCOUNT_T.ACCESS_CODE2,1,10)='Commercial' OR SUBSTR(ACCOUNT_T.ACCESS_CODE2,1,8 )='Consumer')
AND ACCOUNT_T.STATUS=10100
AND service_t.status = 10100
AND UPPER(service_t.poid_type) = '/SERVICE/RADIO'
AND LENGTH(service_t.login) < 9
AND (
(BILLINFO_T.PAY_TYPE = '10003' AND
 SYSDATE - (TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(ITEM_T.EFFECTIVE_T, 'SECOND')) > 30)
OR
(BILLINFO_T.PAY_TYPE = '10001' AND
 SYSDATE - (TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(ITEM_T.EFFECTIVE_T, 'SECOND')) > 60)
)
AND NOT (PROFILE_XM_CREDIT_CLASS_T.CREDIT_CLASS='GMAC' OR PROFILE_XM_CREDIT_CLASS_T.CREDIT_CLASS='VIP')
AND (PROFILE_XM_FLAGS_WRITEOFF_T.XM_DNT_FLAG = 0
OR PROFILE_XM_FLAGS_WRITEOFF_T.XM_DNT_FLAG IS NULL)
AND NOT EXISTS
(SELECT SERVICE_OBJ_ID0
FROM ACCOUNT_PRODUCTS_T
WHERE ACCOUNT_PRODUCTS_T.SERVICE_OBJ_ID0 = SERVICE_T.POID_ID0
AND DESCR LIKE '%Call XM%')
GROUP BY ACCOUNT_T.POID_ID0,
ACCOUNT_T.ACCOUNT_NO,
SERVICE_T.POID_ID0,
SERVICE_T.LOGIN,
BILLINFO_T.PAY_TYPE,
SERVICE_T.LAST_STATUS_T,
ACCOUNT_T.ACCESS_CODE2,
PROFILE_XM_CREDIT_CLASS_T.CREDIT_CLASS
HAVING SUM(due) > 5

I want to put all the steps togetehr in a shell script.
let's say if we start at 6am, the script should kick off the above query. Next step is to check if the query is completed, it should start running  report A. Then it follows all of the specified sequence to check job status and kick off each subseqent job.

I am not sure the exact file path and server locations yet. I just need a "shell" script where I can plug in the path and server info. once I have them

Thanks.
0
ahoffmannCommented:
write your SQL command to a file, for example /path/tmp.sql
then use following command from command line of your shell (user and secret are your login credentials to Oracle)
  sqlplus -s user/secret @/path/tmp.sql

If it works, do the same for your other SQL statements, then finally write a script as follows (assuming that each step has its own tmporary file):

#! /bin/sh
/path/to/sqlplus -s user/secret @/path/tmp1.sql
/path/to/sqlplus -s user/secret @/path/tmp2.sql
/path/to/sqlplus -s user/secret @/path/tmp3.sql
# .. more such sqlplus if you like/need


then to startthjat at 6am write to your cronfile, using  crontab -e
* 6 * * * /path/to/script-above
0
HKBoyzAuthor Commented:
ahoffmann
Thanks for all the info. Now we have decided to use Windows Task Scheduler to schedule the and execute the jobs using a .bat file. The sequence is:

1. Run SQL script to create  and populate the staging table;
2. Run report A when the staging table is created and populated; ie. the report can't be run until it receveid the status of the SQL script is completed;
3. Refreshed/repopulated the staging table when repot A is completed. ie. upon receiving the status of the report is compelted;
4. Run report B when receveid the status of the SQL script is completed;
5. Run reprot C when  receveid the status of the report B is completed.

Would appreciate if you have any idea of how the .bat file should look like, since I don't have any exposures to .bat file.
Thanks.
0
ahoffmannCommented:
what is Windows? is the usual question in this TA ;-)
If you need a script for that, please post yoiur question in a Windows-related TA.

I.g the solution is the same, except the dragons to beat in cmd.exe (if you mean that with .bat file).
I guess that my suggestion in http:#16363327 except the final cron part works for Windows also.
0
rbinklCommented:
#************************************************************/

echo "\n\n"

sql="/scripts/oracle/"

ls -lt $sql | head

echo "\n\nEnter the name of the sql file ................ \c"
read ans
if [ ! -r $sql/$ans ]
then
        echo "You must enter a full file name which you have read permissions"
        echo "from the directroy: $sql"
        exit 1
fi
sqlfile=$ans

#echo "Enter the name of the output (formatted) file ... \c"
#read out

echo "\nEnter the output file directory. Otherwise it will go in your "
echo "current directory: \c"
read dir

if [ "$dir" = "" ]
then
        outfile=$out
else
        outfile=$dir/$out
fi


#these lines added to set up the oracle system environment
PATH=$PATH:/home/app/oracle/product/7.3.4/bin:/usr/local/bin
export PATH
echo $PATH
export ORACLE_SID=prd1
export ORACLE_HOME=/home/app/oracle/product/7.3.4
export ORAENV_ASK=NO
export ORA_NLS=$ORACLE_HOME/ocommon/nls/admin/data
export ORA_CMS_MODE=line
export ORACLE_BASE=/home/app/oracle
sqlplus -s <<EOF
userid/passwd t
@$ans
EXIT
EOF



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HKBoyzAuthor Commented:
Thanks all for your inputs. I created a .bat file to exec. a sql file successfully when I ran it in my local drive.

However, when I ran it in the server I first got the SP2-0333 "bad character" error. It was caused by the space in the server path. So I changed the .bat file to the following:

@ECHO OFF
subst g: "C:\Program Files\Crystal Decisions\Enterprise 9\win32_x86"
sqlplus pinprd/pintst_pin05@xm_pintst @"C:\Program Files\Crystal Decisions\Enterprise 9\win32_x86\AT_Sql.sql"

Now I get the SP2-0332 error: cannot create spool file.

I can't change the space int he path as that's what the server path is, and the spool file can't be place anywhere else as it will be used there.

Is there any work around?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Unix OS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.