?
Solved

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

Posted on 2006-04-03
9
Medium Priority
?
851 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:HKBoyz
  • 4
  • 4
9 Comments
 
LVL 51

Assisted Solution

by:ahoffmann
ahoffmann earned 1000 total points
ID: 16361446
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
 

Author Comment

by:HKBoyz
ID: 16361498
Do you have a sample script?
0
 
LVL 51

Assisted Solution

by:ahoffmann
ahoffmann earned 1000 total points
ID: 16361571
no
you need to provide much more details: type of database, which table, which rows, which conditions, ...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:HKBoyz
ID: 16361652
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
 
LVL 51

Assisted Solution

by:ahoffmann
ahoffmann earned 1000 total points
ID: 16363327
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
 

Author Comment

by:HKBoyz
ID: 16420154
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
 
LVL 51

Assisted Solution

by:ahoffmann
ahoffmann earned 1000 total points
ID: 16420868
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
 
LVL 1

Accepted Solution

by:
rbinkl earned 1000 total points
ID: 16440921
#************************************************************/

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
 

Author Comment

by:HKBoyz
ID: 16448420
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A metadevice consists of one or more devices (slices). It can be expanded by adding slices. Then, it can be grown to fill a larger space while the file system is in use. However, not all UNIX file systems (UFS) can be expanded this way. The conca…
Why Shell Scripting? Shell scripting is a powerful method of accessing UNIX systems and it is very flexible. Shell scripts are required when we want to execute a sequence of commands in Unix flavored operating systems. “Shell” is the command line i…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
Suggested Courses
Course of the Month13 days, 20 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question