Execute Shell Script

[jo8712]

I wanted to know if and how DS can execute a shell script on a seperate database server other than the one DataStage actually sits on.

[Amos.Rosmarin]

The same as if you have done it from any application ... you can either open a telnet session , use a remote shell (rsh), execute a stored-procedure that will do a system call on the remote DB machine or through any other tool.

Amos

[jo8712]

Can you execute a stored procedure (from DB2) in a DS job....if so, how?

[kcbland]

You can run any command to any database using that database's command line utilities. Oracle has sqlplus, Informix has dbaccess, SQL-Server has isql, so use the DB2 equivalent.

Develop a generic shell script for running database commands. Command line options can be instances, sql scripts, logins, etc.

[vmcburney]

Some of the things I usually want when running Unix scripts:

1) Check that the correct number of command line parameters have been passed to the script:

Code: Select all

# Check Parameters
if [ $# -ne 2 ]
then
    print "Syntax Error!"
    print "Usage: $0 DM_INPUT_FILE DM_OUTPUT_FILE"
    exit 1
fi

2) Trap errors in the script, when an error occurs print a message to the command output and return an error status back to DataStage:

Code: Select all

# Check the Input File Exists
if [[ ! -f ${DM_INPUT_PATH}/${DM_INPUT} ]]
then
    print "Input Error!"
    print "File ${DM_INPUT} does not exist in location ${DM_INPUT_PATH}"
    print "Check FILE INPUT PATH: ${DM_INPUT_PATH}"
    exit ${FAILURE}
fi

3) Run all DSExecutes from a central routine. Trap the return code and show the command output in the log. In this case a failed script creates a warning in the log:

Code: Select all

	Call DSExecute("UNIX", RoutineName, OutPut, RetCode)
      IF RetCode <> 0 then Call DSLogWarn("The Unix script did not work: " :OutPut, DSJobName)
	Call DSLogInfo("Return code is ":RetCode:" ":OutPut, DSJobName)

4) Now you can see Unix errors in your DataStage log with information on what went wrong.

Code: Select all

seqDQCPClean..JobControl (seqDQCPClean): The Unix script did not work: Input Error!
File dpid_CP01customer030929.dat does not exist in location /u01/dev/dqwork/processing/CP
Check FILE INPUT PATH: /u01/dev/dqwork/processing/CP