EXECSH at the end of a job called by a sequence
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
EXECSH at the end of a job called by a sequence
I have a unitary job which calls an EXECSH script on successful job completion. This job is called by a sequence. The subsequent job in the sequence is executed only on this job terminating correctly, so the trigger is defined accordingly in the job activity in my sequence.
Now when the EXECSH fails I notice that the return code is still 1, i.e; $JobStatus = 1.
I need to avoid this, i.e. even is the unitary job runs alright but the EXECSH at the end fails I would like to catch this message somehow.
Ideas?
Thanks in advance
Now when the EXECSH fails I notice that the return code is still 1, i.e; $JobStatus = 1.
I need to avoid this, i.e. even is the unitary job runs alright but the EXECSH at the end fails I would like to catch this message somehow.
Ideas?
Thanks in advance
Tony
BI Consultant - Datastage
BI Consultant - Datastage
If the EXECSH fails it creates a warning in the job log, isn't that sufficient to trigger a different action?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
I tested it again today, if an after-job call to EXECSH fails it returns a warning to the job - on Windows at 8.7
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Exactly. This is a classic 'problem' because the command itself is not failing, it ran properly. It has no clue the sqplus session you called within the command failed either as sqlplus itself ran fine. It's just that pesky SQL statement you ran via sqlplus that didn't work quite as well as you hoped.
As Ray noted, you need a wrapper around the call to sqlplus, one that can interrogate the results of the sqlplus session and grep for ORA errors and the like. If any are found you need to specific return a non-zero status to the calling program - in this case DataStage. Then it will notice.
NOTE that I'm inferring a great deal of what I think is going on here but it's all based on past experience with getting bit by (what I assume is) this same issue.
As Ray noted, you need a wrapper around the call to sqlplus, one that can interrogate the results of the sqlplus session and grep for ORA errors and the like. If any are found you need to specific return a non-zero status to the calling program - in this case DataStage. Then it will notice.
NOTE that I'm inferring a great deal of what I think is going on here but it's all based on past experience with getting bit by (what I assume is) this same issue.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
I get the idea but am kind of unsure how to implement it. As promised here are more details:
The SQL script that is launched after successful completion of the job is in a .sql file and is something like this:
MERGE INTO TABLE_A USING TABLE_B ON (A.NUMR_CRED=B.NUMR_CRED AND A.CODE_.....)(
I have purposely included the open and close brackets in order to make the thing fail.
In the director the message I have is:
Jx_L_ORA_SACCEF_PERM..AfterJob (ExecSH): Executed command: sqlplus CEGC_BPCE/********@PDTM_BPCE @/data/SACCEF_CEGC/DEV/SRC/SQL/SACCEF_PERM_TEST.sql 201205
*** Output from command was: ***
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Aug 9 10:57:19 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
A.NUMR_CRED=B.NUMR_CRED AND A.CODE_.....)(
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
Commit complete.
So how do I ensure that this line is not green in Director?
The SQL script that is launched after successful completion of the job is in a .sql file and is something like this:
MERGE INTO TABLE_A USING TABLE_B ON (A.NUMR_CRED=B.NUMR_CRED AND A.CODE_.....)(
I have purposely included the open and close brackets in order to make the thing fail.
In the director the message I have is:
Jx_L_ORA_SACCEF_PERM..AfterJob (ExecSH): Executed command: sqlplus CEGC_BPCE/********@PDTM_BPCE @/data/SACCEF_CEGC/DEV/SRC/SQL/SACCEF_PERM_TEST.sql 201205
*** Output from command was: ***
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Aug 9 10:57:19 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
A.NUMR_CRED=B.NUMR_CRED AND A.CODE_.....)(
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
Commit complete.
So how do I ensure that this line is not green in Director?
Tony
BI Consultant - Datastage
BI Consultant - Datastage
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
By detecting the exit status of the sqlplus command in your script and making sure that your script doesn't exit with status 0 if the sqlplus command doesn't.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
Include this in the .sql fileBASU wrote:...and that is exactly what I do not know how to do.
Code: Select all
whenever sqlerror exit sql.sqlcode
Code: Select all
whenever sqlerror exit 67
In the script
Code: Select all
echo $?
Thanks,
Chetan.C
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
Thanks Chetan but I'm not quite sure I understand.
I should include the line:
WHENEVER SQLERROR EXIT SQL.SQLCODE;
ECHO $?
in my script?
So my script becomes:
MERGE INTO SACCEF_PERM A USING SACCEF_PERM_MOIS B ON (A.NUMR_CRED=B.NUMR_CRED AND A.CODE_ORGN_FINN=B.CODE_ORGN_FINN )
WHEN MATCHED THEN UPDATE SET A.NUMR_DOSS=B.NUMR_DOSS, A.NUMR_ENTT_TITL=B.NUMR_ENTT_TITL,
A.IDNT_DOSS_INSTR=B.IDNT_DOSS_INSTR, A.DATE_FIN_CONT_CRDT=B.DATE_FIN_CONT_CRDT)(
WHEN NOT MATCHED THEN INSERT VALUES (B.NUMR_CRED,B.CODE_ORGN_FINN,B.NUMR_DOSS, B.NUMR_ENTT_TITL,B.IDNT_DOSS_INSTR,B.DATE_FIN_CONT_CRDT);
COMMIT;
DELETE FROM SACCEF_PERM
WHERE NVL(DATE_FIN_CONT_CRDT,SYSDATE) <= ADD_MONTHS(TO_DATE('&1','YYYYMM'),-36);
COMMIT;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
ECHO $?
EXIT;
Is this right?
I should include the line:
WHENEVER SQLERROR EXIT SQL.SQLCODE;
ECHO $?
in my script?
So my script becomes:
MERGE INTO SACCEF_PERM A USING SACCEF_PERM_MOIS B ON (A.NUMR_CRED=B.NUMR_CRED AND A.CODE_ORGN_FINN=B.CODE_ORGN_FINN )
WHEN MATCHED THEN UPDATE SET A.NUMR_DOSS=B.NUMR_DOSS, A.NUMR_ENTT_TITL=B.NUMR_ENTT_TITL,
A.IDNT_DOSS_INSTR=B.IDNT_DOSS_INSTR, A.DATE_FIN_CONT_CRDT=B.DATE_FIN_CONT_CRDT)(
WHEN NOT MATCHED THEN INSERT VALUES (B.NUMR_CRED,B.CODE_ORGN_FINN,B.NUMR_DOSS, B.NUMR_ENTT_TITL,B.IDNT_DOSS_INSTR,B.DATE_FIN_CONT_CRDT);
COMMIT;
DELETE FROM SACCEF_PERM
WHERE NVL(DATE_FIN_CONT_CRDT,SYSDATE) <= ADD_MONTHS(TO_DATE('&1','YYYYMM'),-36);
COMMIT;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
ECHO $?
EXIT;
Is this right?
Tony
BI Consultant - Datastage
BI Consultant - Datastage
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
As I feared this will not work. i get the message:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
A.IDNT_DOSS_INSTR=B.IDNT_DOSS_INSTR, A.DATE_FIN_CONT_CRDT=B.DATE_FIN_CONT_CRDT)(
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
Commit complete.
old 2: WHERE NVL(DATE_FIN_CONT_CRDT,SYSDATE) <= ADD_MONTHS(TO_DATE('&1','YYYYMM'),-36)
new 2: WHERE NVL(DATE_FIN_CONT_CRDT,SYSDATE) <= ADD_MONTHS(TO_DATE('201205','YYYYMM'),-36)
0 rows deleted.
Commit complete.
SP2-0042: unknown command "ECHO $?" - rest of line ignored.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ECHO is not really a command that goes here.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
A.IDNT_DOSS_INSTR=B.IDNT_DOSS_INSTR, A.DATE_FIN_CONT_CRDT=B.DATE_FIN_CONT_CRDT)(
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
Commit complete.
old 2: WHERE NVL(DATE_FIN_CONT_CRDT,SYSDATE) <= ADD_MONTHS(TO_DATE('&1','YYYYMM'),-36)
new 2: WHERE NVL(DATE_FIN_CONT_CRDT,SYSDATE) <= ADD_MONTHS(TO_DATE('201205','YYYYMM'),-36)
0 rows deleted.
Commit complete.
SP2-0042: unknown command "ECHO $?" - rest of line ignored.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ECHO is not really a command that goes here.
Tony
BI Consultant - Datastage
BI Consultant - Datastage
BASU wrote:As I feared this will not work. i get the message:
ECHO is not really a command that goes here.
In your .sql file at the beginning you must include the
Code: Select all
whenever sqlerror exit sql.sqlcode
There is a shell script in which you are calling the sqlplus coomand right?
In that shell script the echo $? must be included.
I have used the same way and its working so dont fear
Your .sql file
Code: Select all
WHENEVER SQLERROR EXIT SQL.SQLCODE;
MERGE INTO SACCEF_PERM A USING SACCEF_PERM_MOIS B ON (A.NUMR_CRED=B.NUMR_CRED AND A.CODE_ORGN_FINN=B.CODE_ORGN_FINN )
WHEN MATCHED THEN UPDATE SET A.NUMR_DOSS=B.NUMR_DOSS, A.NUMR_ENTT_TITL=B.NUMR_ENTT_TITL,
A.IDNT_DOSS_INSTR=B.IDNT_DOSS_INSTR, A.DATE_FIN_CONT_CRDT=B.DATE_FIN_CONT_CRDT)(
WHEN NOT MATCHED THEN INSERT VALUES (B.NUMR_CRED,B.CODE_ORGN_FINN,B.NUMR_DOSS, B.NUMR_ENTT_TITL,B.IDNT_DOSS_INSTR,B.DATE_FIN_CONT_CRDT);
COMMIT;
DELETE FROM SACCEF_PERM
WHERE NVL(DATE_FIN_CONT_CRDT,SYSDATE) <= ADD_MONTHS(TO_DATE('&1','YYYYMM'),-36);
COMMIT;
Code: Select all
$ORACLE_HOME/bin/sqlplus -s /nolog pathtoyourfile/*yourfilename*.sql > /dev/null 2>&1
echo $?
Chetan.C
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Not exit alone.
Code: Select all
# Capture exit status of sqlplus immediately following sqlplus command
set errorcode=$?
# To exit from script with that error code
exit $errorcode
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.