HKBoyz
asked on
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,eff
AS
SELECT /*+ FULL(ITEM_T) FULL(ACCOUNT_T) FULL(SERVICE_T) FULL (ACCOUNT_PRODUCTS_T)
FULL(PROFILE_XM_CREDIT_CLA
DISTINCT
account_t.poid_id0, account_t.account_no, service_t.poid_id0,
service_t.login, billinfo_t.pay_type,servic
SUM(item_t.due) AS due,
ACCOUNT_T.ACCESS_CODE2,PRO
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_
WHERE ACCOUNT_T.POID_ID0=ITEM_T.
AND ITEM_T.BILLINFO_OBJ_ID0=BI
AND ACCOUNT_T.POID_ID0=BILLINF
AND ACCOUNT_T.POID_ID0=PROFILE
AND ACCOUNT_T.POID_ID0=SERVICE
AND (PROFILE_T.POID_ID0=PROFIL
AND (PROFILE_T.POID_ID0 = PROFILE_XM_FLAGS_WRITEOFF_
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_C
AND ACCOUNT_T.STATUS=10100
AND service_t.status = 10100
AND UPPER(service_t.poid_type)
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.EFF
OR
(BILLINFO_T.PAY_TYPE = '10001' AND
SYSDATE - (TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(ITEM_T.EFF
)
AND NOT (PROFILE_XM_CREDIT_CLASS_T
AND (PROFILE_XM_FLAGS_WRITEOFF
OR PROFILE_XM_FLAGS_WRITEOFF_
AND NOT EXISTS
(SELECT SERVICE_OBJ_ID0
FROM ACCOUNT_PRODUCTS_T
WHERE ACCOUNT_PRODUCTS_T.SERVICE
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.
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.
CREATE TABLE AT_STG (ACCT_POID_ID0, ACCOUNT_NO, SRVC_POID_ID0, RADIO_ID,
PAY_TYPE,last_status_t,eff
AS
SELECT /*+ FULL(ITEM_T) FULL(ACCOUNT_T) FULL(SERVICE_T) FULL (ACCOUNT_PRODUCTS_T)
FULL(PROFILE_XM_CREDIT_CLA
DISTINCT
account_t.poid_id0, account_t.account_no, service_t.poid_id0,
service_t.login, billinfo_t.pay_type,servic
SUM(item_t.due) AS due,
ACCOUNT_T.ACCESS_CODE2,PRO
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_
WHERE ACCOUNT_T.POID_ID0=ITEM_T.
AND ITEM_T.BILLINFO_OBJ_ID0=BI
AND ACCOUNT_T.POID_ID0=BILLINF
AND ACCOUNT_T.POID_ID0=PROFILE
AND ACCOUNT_T.POID_ID0=SERVICE
AND (PROFILE_T.POID_ID0=PROFIL
AND (PROFILE_T.POID_ID0 = PROFILE_XM_FLAGS_WRITEOFF_
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_C
AND ACCOUNT_T.STATUS=10100
AND service_t.status = 10100
AND UPPER(service_t.poid_type)
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.EFF
OR
(BILLINFO_T.PAY_TYPE = '10001' AND
SYSDATE - (TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(ITEM_T.EFF
)
AND NOT (PROFILE_XM_CREDIT_CLASS_T
AND (PROFILE_XM_FLAGS_WRITEOFF
OR PROFILE_XM_FLAGS_WRITEOFF_
AND NOT EXISTS
(SELECT SERVICE_OBJ_ID0
FROM ACCOUNT_PRODUCTS_T
WHERE ACCOUNT_PRODUCTS_T.SERVICE
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_pin
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?
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_pin
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?
ASKER