DSXchange

Posted: **Thu Jun 15, 2006 11:43 pm**

Hi Folks,

I'm trying to use Unix shell script before-job subroutine using ExecSH
I'm not fluent with unix scripting

Background:
I have a server job that contains a Sequential File,Transformer and ODBC stage.It simply loads data from txt file to a table. My script creates Index on a column.It works fine. I have used Before-job subroutine - ExecSH for this script

QUESTION::
Now if at any time my script fails or it cant create index before job I want to stop job execution.
In my case even if index already present and my script cant create it(it should not) the job runs succesfully.
In essence is there any option in DS to run job after sucessful execution of script ?

or it should be handled in script only and How do I do that??
Any ideas are most welcome, thanks...

Posted: **Fri Jun 16, 2006 12:25 am**

you need to have error handling in your script so that it return non-zero return code - then datastage will understand this as an aborted script and stop before running the job.

i.e.

EXIT 1

Posted: **Fri Jun 16, 2006 1:47 am**

Become fluent with UNIX scripting. It will stand you in good stead not just with DataStage but with all your interactions with UNIX systems.

If your script exits with a non-zero exit status, ExecSH will cause execution of the job to be halted. So that part of it's fairly automated.

Posted: **Fri Jun 16, 2006 3:18 am**

Hi

One thing I felt that I can ad is - If the SQL scrript called from a Unix shell run via Datastage Exec Activity fails- You need to have two things done to trap the SQL error and fail your shell>

When connecting to Oracke use the following call.

Code: Select all

tab=`$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF >>$LOG_FILE
                CONNECT ${DB_USER}/${DB_PASSWORD}@${DB_NAME}
		set lines 250
		set trims off
		set wrap off
		set head off
                    set pages 0
		spool $SPOOL_FILE
		
		WHENEVER SQLERROR EXIT SQL.SQLCODE

Exiting with SQL error is very important and would cause the failure of your SHell for SQL issues.

While exiting you can use this piece of code which would handle Shell or SQL error

Code: Select all

SHELL_STATUS=$?
 
        if [ ${SHELL_STATUS} -ne 0 ]
        then
                echo "Error in Calling Shell ..." >>$LOG_FILE
                exit ${SHELL_STATUS}
        else
                echo "Shell and SQL  successfully completed ..." >>$LOG_FILE
               exit ${SHELL_STATUS}
        fi

Note that Datastage cannot show the correct ORA error. So if say the SQL fail but you would get a error code as 174 which cannot help you to debug your SQL properly.

Always write to Spool and cat it to log for this. Hope this helps. :D

Posted: **Fri Jun 16, 2006 4:10 am**

hey thanks guys,

thanks bondcaliwood

it worked, job aborts when script returns nonzero value..

DSXchange

handling error of Unix script in DS Before-job subroutine

handling error of Unix script in DS Before-job subroutine

Failing the shell for SQL error