UNIX Script to execute DataStage job

From Kenneth Bland:

#!/bin/ksh
#######
################################################################################
#######
####### FILE: MasterControl.ksh
#######
####### DESCRIPTION: Starts a DataStage MasterControl type job passing
#######              all runtime parameter values
#######
#######
####### Date       Version   Developer      Description
####### ---------- --------- -------------- ------------------------------------
####### 2002-05-15 1.0       Ken Bland      Initial release
####### 2002-06-27 2.2       Ken Bland      FileSetDate/ProcessDate modifications
####### 2002-07-10 2.2       Steve Boyce    Added ProcessDate as 14th parameter
####### 2002-08-16 2.3       Steve Boyce    Now calls stored procedure
#######                                     GET_NEW_BATCH_NBR in datastage schema
#######                                     instead of deriving it and inserting
#######                                     here.
#######                                     Uses comSQLPlus.ksh and comPLSQL.ksh
#######                                     instead of SQLPlusStub.ksh.ksh
#######

PROG=`basename ${0}`
EXIT_STATUS=0

NOW=`date`
echo "${NOW} ${PROG} Initialization..."
echo

#######
####### CONFIGURATION ##########################################################
#######

if [ ${#} -ne 14 ]; then
   echo "${NOW} ${PROG} : Invalid parameter list."
   echo "${NOW} ${PROG} : The script needs 14 parameters:"
   echo "${NOW} ${PROG} :    JobName"
   echo "${NOW} ${PROG} :    ParameterFile"
   echo "${NOW} ${PROG} :    FileSetDate (YYYY-MM-DD)"
   echo "${NOW} ${PROG} :    BatchNumber"
   echo "${NOW} ${PROG} :    JobHierarchyFile"
   echo "${NOW} ${PROG} :    SourceSystemList"
   echo "${NOW} ${PROG} :    SubjectAreaList"
   echo "${NOW} ${PROG} :    ClearWorkArea"
   echo "${NOW} ${PROG} :    StartingMilestone"
   echo "${NOW} ${PROG} :    EndingMilestone"
   echo "${NOW} ${PROG} :    DebugMode"
   echo "${NOW} ${PROG} :    JobLinkStatisticChecksFile"
   echo "${NOW} ${PROG} :    ResurrectLogFile"
   echo "${NOW} ${PROG} :    ProcessDate (NULL|YYYY-MM-DD H24:MI:SS)"
   exit 99
fi

JobName="${1}"
ParameterFile="${2}"
FileSetDate="${3}"
BatchNumber="${4}"
JobHierarchyFile="${5}"
SourceSystemList="${6}"
SubjectAreaList="${7}"
ClearWorkArea="${8}"
StartingMilestone="${9}"
EndingMilestone="${10}"
DebugMode="${11}"
JobLinkStatisticChecksFile="${12}"
ResurrectLogFile="${13}"
ProcessDate="${14}"

echo "${NOW} ${PROG} JobName ${JobName}"
echo "${NOW} ${PROG} ParameterFile ${ParameterFile}"
echo "${NOW} ${PROG} FileSetDate ${FileSetDate}"
echo "${NOW} ${PROG} BatchNumber ${BatchNumber}"
echo "${NOW} ${PROG} JobHierarchyFile ${JobHierarchyFile}"
echo "${NOW} ${PROG} SourceSystemList ${SourceSystemList}"
echo "${NOW} ${PROG} SubjectAreaList ${SubjectAreaList}"
echo "${NOW} ${PROG} ClearWorkArea ${ClearWorkArea}"
echo "${NOW} ${PROG} StartingMilestone ${StartingMilestone}"
echo "${NOW} ${PROG} EndingMilestone ${EndingMilestone}"
echo "${NOW} ${PROG} DebugMode ${DebugMode}"
echo "${NOW} ${PROG} JobLinkStatisticChecksFile ${JobLinkStatisticChecksFile}"
echo "${NOW} ${PROG} ResurrectLogFile ${ResurrectLogFile}"
echo "${NOW} ${PROG} ProcessDate ${ProcessDate}"
echo

# Below will look in the parameters.ini file to determine the directory path each.
UserID=`whoami`
BinFileDirectory=`cat /.dshome`/bin
LogFileDirectory=`grep -w LogFileDirectory ${ParameterFile}|cut -d "=" -f2`
TempFileDirectory=`grep -w TempFileDirectory ${ParameterFile}|cut -d "=" -f2`
CommonScriptFileDirectory=`grep -w CommonScriptFileDirectory ${ParameterFile}|cut -d "=" -f2`
CommonLogFileDirectory=`grep -w CommonLogFileDirectory ${ParameterFile}|cut -d "=" -f2`
LogFileName=${CommonLogFileDirectory}/${PROG}_${JobName}.log
TEMPBATCHNBRLOG=${TempFileDirectory}/${PROG}_${JobName}_start.log
DATASTAGEPROJECT=`grep -w DATASTAGEPROJECT ${ParameterFile}|cut -d "=" -f2`
DSSERVER=`grep -w DSSERVER ${ParameterFile}|cut -d "=" -f2`
DSUSERID=`grep -w DSUSERID ${ParameterFile}|cut -d "=" -f2`
DSPASSWORD=`grep -w DSPASSWORD ${ParameterFile}|cut -d "=" -f2`

NOW=`date`
echo "${NOW} ${PROG} UserID ${UserID}"
echo "${NOW} ${PROG} BinFileDirectory ${BinFileDirectory}"
echo "${NOW} ${PROG} LogFileDirectory ${LogFileDirectory}"
echo "${NOW} ${PROG} TempFileDirectory ${TempFileDirectory}"
echo "${NOW} ${PROG} CommonScriptFileDirectory ${CommonScriptFileDirectory}"
echo "${NOW} ${PROG} CommonLogFileDirectory ${CommonLogFileDirectory}"
echo "${NOW} ${PROG} LogFileName ${LogFileName}"
echo "${NOW} ${PROG} TEMPBATCHNBRLOG ${TEMPBATCHNBRLOG}"
echo "${NOW} ${PROG} DATASTAGEPROJECT ${DATASTAGEPROJECT}"
echo "${NOW} ${PROG} DSSERVER ${DSSERVER}"
echo "${NOW} ${PROG} DSUSERID ${DSUSERID}"
echo "${NOW} ${PROG} DSPASSWORD *Protected*"
echo

#######
####### PARAMETER BUILD Without batch number ##################################
#######

if [ "${ProcessDate}" = "NULL" ]; then
   StartTimestamp=`date '+%Y-%m-%d %H:%M:%S'`
else
   StartTimestamp="${ProcessDate}"
fi
ParamList=" -param ParameterFile=${ParameterFile}"
ParamList="${ParamList} -param ProcessDate=\"${StartTimestamp}\""
ParamList="${ParamList} -param FileSetDate=${FileSetDate}"
ParamList="${ParamList} -param JobHierarchyFile=${JobHierarchyFile}"
ParamList="${ParamList} -param SourceSystemList=${SourceSystemList}"
ParamList="${ParamList} -param SubjectAreaList=${SubjectAreaList}"
ParamList="${ParamList} -param ClearWorkArea=${ClearWorkArea}"
ParamList="${ParamList} -param StartingMilestone=${StartingMilestone}"
ParamList="${ParamList} -param EndingMilestone=${EndingMilestone}"
ParamList="${ParamList} -param DebugMode=${DebugMode}"
ParamList="${ParamList} -param JobLinkStatisticChecksFile=${JobLinkStatisticChecksFile}"
ParamList="${ParamList} -param ResurrectLogFile=${ResurrectLogFile}"

#######
####### Get Batch Number and create ETL_BATCH_AUDIT record #####################
#######

echo "${NOW} ${PROG} About to get new BATCH_NBR and insert it into ETL_BATCH_AUDIT..."
${CommonScriptFileDirectory}/comPLSQL.ksh ${ParameterFile} "IRDSN" "IRUserID" \
                                           GET_NEW_BATCH_NBR \
                                              "${JobName}" \
                                              "${StartTimestamp}" \
                                              "${UserID}" \
                                              "${SourceSystemList}" \
                                              "${SubjectAreaList}" \
                                              "${ParamList}" \
                                              "${FileSetDate}" > ${TEMPBATCHNBRLOG}
SQL_EXIT_STATUS=$?
cat ${TEMPBATCHNBRLOG}
if [ "${SQL_EXIT_STATUS}" != 0 ]; then
   NOW=`date`
   echo "${NOW} ${PROG} Failure to connect/insert into ETL_Batch_Audit table!"
   exit ${SQL_EXIT_STATUS}
fi

#######
####### Get BATCH_NBR from batch number log file ##############################
#######
BatchNumber=`grep -w BATCH_NBR ${TEMPBATCHNBRLOG}|cut -d "=" -f2`
if [ -z "${BatchNumber}" ]; then
   NOW=`date`
   echo "${NOW} ${PROG} Failure to retrieve BATCH_NBR from ${TEMPBATCHNBRLOG}"
   exit ${SQL_EXIT_STATUS}
fi

#######
####### Add batch number to list of parameters #################################
#######
ParamList="${ParamList} -param BatchNumber=${BatchNumber}"

NOW=`date`
echo
echo ${NOW} ${PROG} Parameter list: ${ParamList}
echo

#######
####### DataStage EXECUTION ####################################################
#######

NOW=`date`
echo "${NOW} ${PROG} Executing DataStage dsjob program..."

echo ${BinFileDirectory}/dsjob -server ${DSSERVER} -user ${DSUSERID} -password ${DSPASSWORD} -run -wait ${ParamList} ${DATASTAGEPROJECT} ${JobName} 2>&1 > ${LogFileName}
echo
echo "${BinFileDirectory}/dsjob -server ${DSSERVER} -user ${DSUSERID} -password ${DSPASSWORD} -run -wait ${ParamList} ${DATASTAGEPROJECT} ${JobName} 2>&1 > ${LogFileName}"
echo

eval ${BinFileDirectory}/dsjob -server ${DSSERVER} -user ${DSUSERID} -password ${DSPASSWORD} -run -wait ${ParamList} ${DATASTAGEPROJECT} ${JobName} 2>&1 >> ${LogFileName}

jobwaiting=`grep "Waiting for job..." ${LogFileName}`
if [ "${jobwaiting}" != "Waiting for job..." ]; then
   NOW=`date`
   echo ${NOW} ${PROG} "DataStage failed to start the job"
   failedstart=1
else
   NOW=`date`
   echo ${NOW} ${PROG} "DataStage successfully started the job"
   failedstart=0
fi
NOW=`date`
echo ${NOW} ${PROG} "Retrieving job information"

${BinFileDirectory}/dsjob -server ${DSSERVER} -user ${DSUSERID} -password ${DSPASSWORD} -jobinfo ${DATASTAGEPROJECT} ${JobName} >> ${LogFileName}

#######
####### CHECK STATUS ###########################################################
#######

ERROR=`grep "Job Status" ${LogFileName}`
ERROR=${ERROR##*\(}
ERROR=${ERROR%%\)*}

if [ "${failedstart}" != 0 ]; then
   NOW=`date`
   echo ${NOW} ${PROG} "The job failed to start"
   AuditStatus="FAILURE"
   Comments="MasterControl aborted"
   EXIT_STATUS=1
else
   if [ "${ERROR}" = 1 -o "${ERROR}" = 2 ]; then
      NOW=`date`
      echo ${NOW} ${PROG} "The job completed successfully"
      AuditStatus="SUCCESS"
      Comments=""
      EXIT_STATUS=0
   else
      NOW=`date`
      echo ${NOW} ${PROG} "The job aborted"
      AuditStatus="FAILURE"
      Comments="MasterControl aborted"
      EXIT_STATUS=1
   fi
fi

FailedJobCount=`grep -i FAILED ${LogFileDirectory}/${JobName}.log|wc -l|cut -b1-9`
FailedJobCount=`expr ${FailedJobCount} + 0`
echo ${NOW} ${PROG} The number of failed jobs is [${FailedJobCount}]

if [ "${FailedJobCount}" != 0 ]; then
   NOW=`date`
   echo ${NOW} ${PROG} "The job had failed processes"
   AuditStatus="FAILURE"
   Comments="MasterControl had ${FailedJobCount} failed processes"
   EXIT_STATUS=1
fi
StoppedJobStreamCount=`grep "JOB STREAM STOPPED" ${LogFileDirectory}/${JobName}.his|wc -l|cut -b1-9`
StoppedJobStreamCount=`expr ${StoppedJobStreamCount} + 0`
if [ "${StoppedJobStreamCount}" != 0 ]; then
   NOW=`date`
   echo ${NOW} ${PROG} "The job stream was STOPped or KILLed"
   AuditStatus="FAILURE"
   Comments="MasterControl job stream was STOPped or KILLed"
   EXIT_STATUS=1
fi

#######
####### AUDIT ##################################################################
#######

echo
echo "${NOW} ${PROG} About to update ETL_BATCH_AUDIT with status information..."
EndTimestamp=`date '+%Y-%m-%d %H:%M:%S'`

SQLstring="UPDATE ETL_BATCH_AUDIT A \
              SET A.END_TIMESTAMP = TO_DATE('${EndTimestamp}','YYYY-MM-DD HH24:MI:SS'), \
                         A.STATUS = '${AuditStatus}', \
                       A.COMMENTS = '${Comments}', \
                A.RUNTIMESETTINGS = '${ParamList}' \
            WHERE (A.BATCH_NBR = ${BatchNumber});"

NOW=`date`
echo ${NOW} ${PROG} Audit SQL ${SQLstring}
SQLScriptFileName=${TempFileDirectory}/${PROG}_${JobName}_end.sql
echo ${SQLstring} > ${SQLScriptFileName}

${CommonScriptFileDirectory}/comSQLPlus.ksh ${ParameterFile} IRDSN IRUserID ${SQLScriptFileName}
SQL_EXIT_STATUS=$?
if [ "${SQL_EXIT_STATUS}" != 0 ]; then
   NOW=`date`
   echo ${NOW} ${PROG} Failure to connect/update into ETL_Batch_Audit table!
   exit ${SQL_EXIT_STATUS}
fi

#######
####### EXIT ###################################################################
#######

NOW=`date`
echo ${NOW} ${PROG} Complete, exiting with status [${EXIT_STATUS}]
exit ${EXIT_STATUS}

How to find and replace string in multiple files in UNIX environment

When you work with DataStage in UNIX environment, you will find a lot of daily activities for find and replace string in multiple files. There is simple step to do with use SED syntax and combine with Find.

Syntax:
$ find <directory> -type f -exec sed -i 's/<old_string>/<new_string>/g' {} \;

Hope it helps

UV Help & UVSQL Help

For complete reference about UV you can go to this link:
                                  http://www.isecor.com/kb/documentation/uvhelp.html

For UVSQL Help:
                                  http://www.isecor.com/kb/documentation/uvsqlhelp.html

Thanks

ORCHADMIN Foundation

Before Orchadmin can be used, we have to configure first some of the DataStage variables, that are:

1. run dsenv (. $DSHOME/dsenv)
2. export LD_LIBRARY_PATH=$APT_ORCHHOME/lib
3. export APT_CONFIG_FILE=$DSHOME/../Configurations/default.apt
4. export PATH=$DSHOME/bin:$APT_ORCHHOME/bin:/$PATH

ORCHADMIN MANUAL

NAME
     orchadmin - delete, copy, describe and dump ORCHESTRATE files

SYNOPSIS
     orchadmin command [ -options... ] descriptor-files...

     orchadmin command [-help]     # prints help message for one command

     orchadmin [-help]             # prints help message for all commands

     orchadmin -f command-file     # executes commands from specified file

     orchadmin -                   # executes commands from standard input

DESCRIPTION
     orchadmin executes commands which delete, copy, and describe
     ORCHESTRATE files.  These commands may be given on the command
     line or read from a file or the standard input.

     command             delete, copy, describe, dump or check.

     -f command-file     Path of a file containing orchadmin commands.
                         The file may have multiple commands separated
                         by semicolons.  A command may be spread over
                         multiple lines.  C and C++ style comments and
                         csh style quotation marks are allowed.

     -                   Read commands from the standard input as if it
                         were a command file.

     -help | -h          Write usage information to the standard output.

    In addition there are the following NLS related options:

    -input_charset map-name     Specifies the encoding of option values.
    -output_charset map-name    Specifies the encoding of orchadmin output.
    -os_charset map-name        Specifies the encoding of data passed to or
                                received from the operating system via
                                "char *".
    -escaped            Allows command line characters to be presented
                        in a two-byte Unicode hex format.


COMMAND: copy | cp  source-descriptor-file  target-descriptor-file

     Copy the schema, contents and preserve-partitioning flag of the
     specified ORCHESTRATE file dataset.  If the preserve-partitioning
     flag is set, the copy will have the same number of partitions and
     record order as the original.  If the target file already exists,
     it will be truncated first.  If the preserve-partitioning flag of
     the source file is set and the target file already exists, it must
     have the same number of partitions as the source file.

     The copy command has no options.  A warning message is issued if
     the target does not already exist.  This is a bug, not a feature.


COMMAND: delete | del | rm  [ -options... ]  descriptor-files...

     Delete the specified descriptor files and all of their data files.

OPTIONS:
     -f             Force.  Proceed even if some partitions of the
                    dataset are on nodes that are inaccessible from the
                    current configuration file.  This will leave orphan
                    data files on those nodes.  They must be deleted by
                    some other means.

     -x             Use the system config file rather than the one
                    stored in the dataset.

EXAMPLE:
     Delete all datasets in the current directory that end in ".ds".

          orchadmin rm *.ds


COMMAND: truncate  [ -options... ]  descriptor-files...

     Remove data from the specified datasets.

OPTIONS:
     -f             Force.  Proceed even if some partitions of the
                    dataset are on nodes that are inaccessible from the
                    current configuration file.  This will leave orphan
                    data files on those nodes.  They must be truncated by
                    some other means.

     -x             Use the system config file rather than the one
                    stored in the dataset.

     -n segment     Leave this many segments.  The default is 0.

EXAMPLE:
     Truncate big.ds to 10 segments:

          orchadmin truncate -n 10 big.ds

     Remove all data from small.ds:

          orchadmin truncate small.ds


COMMAND: dump  [ -options... ]  descriptor-files...

     Dump the specified ORCHESTRATE parallel files as text to the
     standard output.  If no options are specified, all records are
     dumped in order from the first record of the first partition to
     the last record of the last partition.  Each field value is
     followed by a space, and each record is followed by a newline.
     Specific top-level fields may be dumped with the -field option.

OPTIONS:
     -field name    Dump the specified top-level field.  The default is
                    to dump all fields.  This option can occur multiple
                    times.  Each occurrence adds to the list of fields.

     -name          Precede each value by its field name and a colon.

     -n numrec      Limit the number of records dumped per partition.
                    The default is not to limit.

     -part N        Dump only the specified partition.  The default is
                    to dump all partitions.

     -p period      Dump every N'th record in a partition, starting
                    with the first record not skipped (see -skip).
                    The period must be greater than 0.  The default
                    is 1.

     -skip N        Skip the first N records in each partition.  The
                    default is 0.

     -x             Use the system config file rather than the one
                    stored in the dataset.

     If an option occurs multiple times, the last one takes effect.
     The -field option is an exception: each occurrence adds to the
     list of fields to be dumped.

EXAMPLES:
     Dump all records of all partitions of a parallel file named
     small.ds.  Precede each value by its field name and a colon.

          orchadmin dump -name small.ds

     Dump the value of the customer field of the first 99 records
     of partition 0 of big.ds.

          orchadmin dump -part 0 -n 99 -field customer big.ds


COMMAND: describe | lp | lf | ls | ll  [ -options... ]  descriptor-files...

     Print a report about each of the specified parallel files.
     lp = describe -p; lf = describe -f;  ls = describe -s;  ll = describe -l

OPTIONS:
     -p             List partitioning information (except for datafile info).

     -c             Print the stored config file, if any.

     -f             List the data files.

     -s             Print the schema.

     -x             Use the system config file rather than the one
                    stored in the dataset.

     -e             Describe segments individually.

     -v             Describe all segments, valid or otherwise

     -d             Print numbers exactly, not in pretty form

     -l             Means -p -f -s -e -v -c.

EXAMPLE:
     List the partitioning info, data files and schema of file1 and file2.

          orchadmin ll file1 file2


COMMAND: diskinfo [ -a | -np nodepool | -n node... ] diskpool

     Print a report about the specified disk pool

OPTIONS:
     -a             Print information for all nodes

     -np            Print information for just the specified node pool

     -n             Print information for the specified nodes

     -q             Print summary of information only

     If no options are supplied, the default node pool is used.

EXAMPLE:
     Describe disk pool pool1 in node pool bignodes

          orchadmin diskinfo -np bignodes pool1


COMMAND: check

Check the configuration file for any problems.  \

Converting Hexa to Decimal Value in UNIX

To convert content of hexa value into decimal value in a file, you can use this function:

Example:
There is a file with this format:
              date|description|hexa1|hexa2


and We want to convert it into:
              date|description|decimal1|decimal2

Function:
          awk -F\| -v OFS='|' '{ print $1,$2,strtonum("0x"$3),strtonum("0x"$4) }' ${Filename}

Generating DataStage Job Info

This syntax is used to generating job info status from a job:
dsjob -jobinfo
                  ${SERVER_NAME}
                  ${JOB_NAME} > ${OUTPUT_DIRECTORY}.txt

Generate DataStage Log from UNIX command

To Generate DataStage Log File from UNIX, you can use dsjob syntax.

dsjob syntax:
dsjob -logdetail
              {Server_Name}
              {Job_Name}
               `dsjob -logsum -type {Type} -max 2 {Server_Name} {Job_Name}|cut -f1` > {Destination File}.txt

Notes:
{Type} = [ <INFO | WARNING | FATAL | REJECT | STARTED | RESET | BATCH>]

DS Unix Command

There are some Unix command for DataStage :

Check for DataStage client connections:

ps -ef | grep dsapi_slave
ps -ef | grep dscs

Check for jobs processes:

ps -ef | grep DSD

ps -ef | grep phantom

Check datastage shared memory segments:

ipcs - mop | grep ade

to remove them : ipcrm -m ID , where ID is the number from the second column of ipcs -mop

Check if there is any port hung using:

netstat -a | grep dsrpc

Others:

ps -ef | grep osh

ps -ef | grep dscs