EXECSH at the end of a job called by a sequence

[TonyInFrance]

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

[ArndW]

If the EXECSH fails it creates a warning in the job log, isn't that sufficient to trigger a different action?

[TonyInFrance]

Exactly my worry.

The EXECSH is not failing although the SQL command that it calls (Oracle) is not successfully running. The line in my Director is green (so no warning)...

[ArndW]

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

[TonyInFrance]

I'm on Linux (and thus EXECSH and not EXECDOS) using Datastage 8.5

I'll be able to give the exact message tomorrow from the office which I promise to do.

Lets hope we can find a solution.

[ray.wurlod]

Make sure that the script returns a non-zero exit code if it "fails".

[chulett]

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.

[TonyInFrance]

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?

[ray.wurlod]

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.

[TonyInFrance]

...and that is exactly what I do not know how to do.

[chetan.c]

...and that is exactly what I do not know how to do.

Include this in the .sql file

Code: Select all

whenever sqlerror exit sql.sqlcode

Above can also be customized like

Code: Select all

whenever sqlerror exit 67 

This returns the number of the associated Oracle error.
In the script

Code: Select all

echo $?

Gives the return code of command executed ,which is sqlplus.

Thanks,
Chetan.C

[TonyInFrance]

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?

[TonyInFrance]

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.

[chetan.c]

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

echo $? does not come in the .sql file.By script I meant the Unix script.

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; 

And your shell script.

Code: Select all

$ORACLE_HOME/bin/sqlplus -s /nolog pathtoyourfile/*yourfilename*.sql > /dev/null 2>&1

echo $?

Thanks,
Chetan.C

[ray.wurlod]

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