We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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

HKBoyz
HKBoyz asked
on
Medium Priority
877 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.
Comment
Watch Question

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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Do you have a sample script?
no
you need to provide much more details: type of database, which table, which rows, which conditions, ...

Author

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.
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

Author

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.
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.
Commented:
#************************************************************/

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



Author

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?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.