connecting to datastage via unix K shell

godisgreat · Post by **godisgreat** » Thu Jul 22, 2004 1:09 pm

Hi everybody ,

i am apretty new member to dsxchange , and i really find all posts pretty helpful. I just wanted to know the way to connect to datastage when you are on unix prompt. please if somebody could help...

thanks

SP

spriti1 · Post by **spriti1** » Thu Jul 22, 2004 2:00 pm

You can connect through various dsjob functions. This will allow you to run jobs, copy logs, etc. Here is a line from a script which does this:

dsjob -run -mode NORMAL $S_PARAM -warn $S_WARNLIMIT -jobstatus $S_PRJ $S_JOB > $LOGDUMPDIR/$S_PRJ.$S_JOB.log 2>&1

The parameters must be enclosed in double quotes to preserve the spaces.

sgp

sumitgulati · Post by **sumitgulati** » Thu Jul 22, 2004 2:10 pm

What exactly do you want to accomplish by connecting to DS using K shell. If it is simply job maintenance (running, stoppnig, getting log, resetting, etc.) then that can be done using DSJOB. Is it all you want to do?

Regards,
Sumit

ketfos · Post by **ketfos** » Thu Jul 22, 2004 2:17 pm

Hi,
I am sure idea of executing through a UNIX script is more than just executing the dsjob command.
User might be controlling the execution, writing results to log files, recording error files, house cleaning, generating emails...

Ketfos

godisgreat · Post by **godisgreat** » Thu Jul 22, 2004 2:35 pm

Hi ,

thanks for the quick responses. I basicaly want to execute a stored procedure using unix shell scripting (connecting to the sql plus) to drop index on a source table and call it as a before job subroutine. here i am using OraOCI as source stage and getting data from an oracle 8i system . My datastage client is on a win nt machine and server is on a unix machine.

i would appreciate more help and useful links on shell scritping for connecting with oracle to perofm datastage tasks. I am still getting familiarized with linking shell scripts, Pl/sql code and Datastage. i really need a broad overview. If any previous posts are there regarding this topic , it would be wonderful.

thanks a again
SP

[quote="godisgreat"]Hi everybody ,

i am apretty new member to dsxchange , and i really find all posts pretty helpful. I just wanted to know the way to connect to datastage when you are on unix prompt. please if somebody could help...

thanks

SP[/quote]

ketfos · Post by **ketfos** » Thu Jul 22, 2004 2:42 pm

Hi,
This might help
1. Define enviornmental variables like hostname, database name, project directories.

2. Define SQLPLUS path in the enviornmental variables.

3.Check for the input file if any.

4.Call your SQLPlus Script which has stored preocedure.

5.Call Datastage job to check for any errors as the result of execution in step 4.

6.Call your Master Datastage job.

7. Do housekeeping.

Ketfos

kduke · Post by **kduke** » Fri Jul 23, 2004 2:25 am

You can run DataStage from a UNIX script or you can run a UNIX script from DataStage. I built a generic shell script to call store procedures. You can run this in a routne with code:

Code: Select all

Cmd = 'SH -c run_sql.ksh '
execute Cmd capturing output returning RtnValue

The variable RtnValue will have the exit value of the shell script. The variable output will have what would of gone to the screen.

Here is the shell script:

Code: Select all

#!/bin/ksh
# run_sql.ksh userid passwd dsn sqlscript parameters
# ----------------------------------------------------------------------
if [ $# -lt 4 ]
then
   echo "Error: Usage $0 sqlscript userid passwd dsn parameters"
   exit 3
fi
# ----------------------------------------------------------------------
DIR=`dirname $0`
if [ -d $DIR ]
then
   cd $DIR
fi
# ----------------------------------------------------------------------
SQLSCRIPT=$1
shift
USERID=$1
shift
PASSWD=$1
shift
DSN=$1
shift
PROCEDURE=""
##shift

FIRST=1
while [ "$1"x != x ]
do
   if [ $FIRST -ne 1 ]
   then
      PROCEDURE="$PROCEDURE,"
   else
      FIRST=0
   fi
   case $1 in
     [0-9]*)
        PROCEDURE="$PROCEDURE$1"
        ;;
     *)
        PROCEDURE="$PROCEDURE'$1'"
        ;;
   esac
   shift
done
PROCEDURE="$PROCEDURE "

# ----------------------------------------------------------------------
OUTFILE=/tmp/run_sql.$$
echo "$ORACLE_HOME/bin/sqlplus $USERID/$PASSWD@$DSN @$SQLSCRIPT $PROCEDURE $* " >$OUTFILE 
$ORACLE_HOME/bin/sqlplus $USERID/$PASSWD@$DSN @$SQLSCRIPT $PROCEDURE $*  >>$OUTFILE
# grep -i err $OUTFILE >/dev/null
# if [ $? -eq 0 ]
if [ $? -gt 0 ]
then
   echo "Error: In processing $PROCEDURE "
   grep -i err $OUTFILE
   echo "rm $OUTFILE"
#   rm $OUTFILE
   exit 3
fi
# ----------------------------------------------------------------------
echo "rm $OUTFILE"
# rm $OUTFILE

It has been a few years since I ran this script but I am sure it used to work. If you cannot get it to work then next week I can debug it and fix it.

godisgreat · Post by **godisgreat** » Mon Jul 26, 2004 4:20 pm

Hi everybody,

thanks a lot , it gave me quite a fair idea of what needs to be done. i visited your website Mr Duke . it was cool to have a lot of info about datastage at one place. thanks . though it will take me time to learn unix shell scripting with respect to datatsage , but thanks to everybody for quick answers.

regards

SP

DSXchange

connecting to datastage via unix K shell

connecting to datastage via unix K shell

Re: connecting to datastage via unix K shell

thanks