DSXchange

Posted: **Fri Nov 07, 2003 8:22 am**

For those familiar with my methodologies, you'll see how this script is used as the main interface between an enterprise scheduler and the single controlling job for a jobstream. This script is responsible for fetching a batch number from a process metadata table and other asundry items. I hope it gives a clear enough example of some of the things we've had to do to make sure dsjob works to our satisfaction.

Code: Select all

#!/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}

Posted: **Tue Nov 11, 2003 10:32 am**

Thank you so much Bland.

Posted: **Tue Jul 15, 2008 8:55 pm**

in this way, i think the actual DB password will be visible using ps -ef.

Posted: **Wed Jul 16, 2008 7:17 pm**

Thanks keshav0307 - very insightful - especially considering that the initial post is 5 years old!

Posted: **Thu Jul 17, 2008 10:37 am**

Well, don't blame me. dsjob requires credentials in most environments to be exposed on the command line. Only later functionality allows you to indirect this information into the process via a file. Command line exposure of passwords has been a long running issue for unix environments.

Posted: **Mon Jun 22, 2009 1:34 pm**

Hey Bland,
I am new to DS Scripting and Scheduling, could you help me find some ref. material, i wanted to start with some basic scripts like running a job from unix, getting a log file, and the finish status.

Thanks

Posted: **Mon Jun 22, 2009 1:42 pm**

Search for dsjob command

Posted: **Mon Jun 22, 2009 1:45 pm**

QuestExpert:

Please don't quote the entire contents of every message you reply to. There is a perfectly lovely Reply to topic button at the bottom of the post that will start you off with a clean slate. It's fine to do so to make a point or to ensure people know exactly who / what you are replying to, but don't think the "Reply with quote" is the only thing that works here.

Thanks.

<nuked offending quote with newly gained Moderator super-powers!>

Posted: **Thu Dec 09, 2010 10:16 pm**

I may have missed something but it looks to me as if this script is not complete by itself. Don't we also need comSQLPlus.ksh and comPLSQL.ksh and the stored proc GET_NEW_BATCH_NBR? Great job though.

I know the script is 7 years old but I just joined a month ago and need a template dsjob script as a starter.

Posted: **Thu Dec 09, 2010 10:32 pm**

Not so much a starter as an ender IMHO. The one I typically use is much... simpler.

Posted: **Thu Dec 09, 2010 11:29 pm**

Some tweaks may be needed for version 8, such as -file and/or -domain options for dsjob.

Posted: **Mon Mar 19, 2012 3:14 am**

Version 8.7 allows for encrypted passwords to be used on the command line.

Posted: **Mon Jun 16, 2014 10:56 am**

I am trying to understand the script.Not sure what are values i need to pass
for the following parameters.

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}"

Posted: **Mon Jun 16, 2014 11:21 am**

You would need to have access to his 'KBA Job Control Utility' code for those to make any sense. I would imagine they could be... ignored.

DSXchange

Shell script to start a job using dsjob

Shell script to start a job using dsjob

Re: Shell script to start a job using dsjob