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...
handling error of Unix script in DS Before-job subroutine
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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.
Failing the shell for SQL error
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.
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
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
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
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
Always write to Spool and cat it to log for this. Hope this helps. :D
Avishek Mukherjee
Data Integration Architect
Chicago, IL, USA.
Data Integration Architect
Chicago, IL, USA.