Datastage job control functions

This post gives a basic idea of main Job control functions used in DataStage. Syntax of each function is explained with examples. Finally some more examples of subroutines are shown which use some of these functions. Job control functions are specified in job properties which will enable first job to control other jobs. They also provide status of the current job and are used in active stage expressions and before and after stage routines. Some Job Control functions are:

• DSAttachJob
• DSCheckRoutine
• DSDetachJob
• DSGetJobInfo
• DSGetCustInfo
• DSGetLinkInfo
• DSGetProjectInfo
• DSLogEvent
• DSLogInfo
• DSPrepareJob
• DSRunJob
• DSSendMail
• DSSetGenerateOpMetaData
• DSSetJobLimit
• DSSetParam
• DSStopJob
• DSWaitForFile
• DSWaitForJob

Brief explanation of these functions is given below.

DSAttachJob

• Attaches to a job in order to run it in job control sequence.
• A handle is returned which is used for referring to the job. At a time, only be one handle can be opened for a particular job.

Syntax:

JobHandle = DSAttachJob (JobName, ErrorMode)

• JobHandle is the return value for the function DSAttachJob. Other functions will be using this value to refer to the job ‘JobName’. This may not be an integer value.
• JobName is the name of the job to be attached to. It is a string value.
• ErrorMode is a value specifying how other routines using the handle should report errors. It can take any one value from the following:
  • DSJ.ERRFATAL Logs a fatal message and aborts the controlling job (default).
  • DSJ.ERRWARNING Logs a warning message but still job keeps on running.
  • DSJ.ERRNONE No error message displayed even though there is an error- (but log will show that DSAttachJob has failed).

Example:
This is an example of attaching to Test 1 of the job Ptest:
Ptest_handle = DSAttachJob (“Ptest%Test1”, DSJ.ERRWARNING)
Here Ptest-Test1 is the job with the job handle Ptest_handle which will be called every time to refer to Ptest-Test1.Since Errormode is ErrWarning job will continue running after warning message is logged.

DSCheckRoutine

• This function checks if the given routine is already given in the catalog or called in a VOC.

Syntax:

• 
  RCheck = DSCheckRoutine(RoutineName)
• RoutineName is the name of BASIC routine to check.
• RCheck is a Boolean value. It is False if RoutineName is not there in catalog, else it is True.

Example:
RCheck = DSCheckRoutine (“DSjob.Getvalues”)
If (NOT(RCheck)) Then Message=”Routine not found in catalog”
End

DSDetachJob

This function releases the JobHandle acquired by DSAttachJob if control is not required any more

Syntax:

ErrCode = DSDetachJob (JobHandle)
JobHandle is the handle for the job previously obtained from DSAttachJob.
ErrCode is 0 if DSStopJob is successful, otherwise it may be the following: DSJE.BADHANDLE Invalid JobHandle.
There is only one possible error in trying to close DSJ.ME. Otherwise, ErrCode is always 0.
Example:
The following command detaches the handle for the job
Ptest: ErrDetach = DSDetachJob (Ptest_handle)

DSGetJobInfo

Provides information about a job.Job could be the controlled job or current depending upon the job handle.
Syntax:
JobInfo = DSGetJobInfo (JobHandle, InfoType)

• JobHandle is the handle for the job previously obtained from DSAttachJob.
• Value of JobInfo depends on InfoType used.
• InfoType is the type of information required. Its value can be DSJ.JOBSTATUS, DSJ.JOBNAME, DSJ.JOBCONTROLLER, DSJ.JOBSTARTTIMESTAMP,
  DSJ.JOBWAVENO, DSJ.PARAMLIST, DSJ.STAGELIST, DSJ.USERSTATUS, DSJ.JOBCONTROL, DSJ.JOBPID, DSJ.JPBLASTTIMESTAMP, DSJ.JOBINVOCATIONS, DSJ.JOBINTERIMSTATUS, DSJ.JOBINVOCATIONID, DSJ.JOBDESC, DSJ.JOBFULLDESC, DSJ.STAGELIST2, DSJ.JOBELAPSED, DSJ.JOBEOTCOUNT, DSJ.JOBEOTTIMESTAMP, DSJ.JOBRTISERVICE, DSJ.JOBMULTIINVOKABLE, DSJ.JOBFULLSTAGELIST

Example:
Ptest_Info= DSGetJobInfo (Ptest_handle, DSJ.JOBDESC)
In above example, Ptest_Info will give Job description of Ptest job

DSExecute

• This function is used to run a DOS or WebSphere DataStage engine command in a before/after subroutine.

Syntax:
Call DSExecute (ShellType, Command, Output, SystemReturnCode)

• ShellType (input field) specifies the type of command which will be executed and whether environment is either Win NT or UV (for WebSphere DataStage engine).
• Command (input filed) is the command which will be executed. Command should not prompt for input when it is executed.
• Output (output field) is any output from the input command. Each line of output is separated by a field mark; @FM. Each Output has an entry in job log file as an information message.
• SystemReturnCode (output field) is a code indicates the result of execution of input command. SystemReturnCode = 0 means the command is executed successfully. A value of 1 (for a DOS command) indicates that the command was not found. Any other value marks exit from the command.

Example:

Sample routine which can execute the sql statement from command line prompt using the routine,

* ExecOraScript( “NT”, OracleDsn, OracleUser, OraclePassword, ExecSql )

* Prepare command line 
ExecCommand = “echo “: ExecSql :”; | sqlplus -S “: OracleUser :”/”: OraclePassword :”@”: OracleDsn

*call DSLogInfo(ExecCommand,””)

* Run the SQL 
Call DSExecute( “NT”, ExecCommand, OutputCode, ReturnCode )

* Check for any errors 
If ReturnCode Then Call DSLogFatal( ‘There is fatal error in running following SQL: ‘: ExecSql, ‘ExecOraScript’ )

* Output the result 
result = OutputCode

DSGetCustInfo

• Fetches information displayed at the end of execution of some parallel stages. This information is specified during design of stages. For example Triggers tab of Transformer stage properties specifies information about Transformer stage.

Syntax:

Result = DSGetCustInfo (JobHandle, StageName, CustInfoName, InfoType)

• JobHandle is the handle for the job previously obtained from DSAttachJob. or it may be DSJ.ME to refer to the current job.
• StageName is the name of the stage from which information is obtained. It may also be DSJ.ME to refer to the current stage if necessary.
• CustinfoName
  is name of the variable to be interrogated (as set up on the Triggers tab).This is a string value.
• InfoType specifies the type of information required and its value can be one of: DSJ.CUSTINFOVALUE

DSJ.CUSTINFODESC
Depending on InfoType value of Result can be:

• DSJ.CUSTINFOVALUE String – the value of the specified custinfo item.
• DSJ.CUSTINFODESC String – description of the specified custinfo item.

There can also be some error reported by Result as follows:

• DSJE.BADHANDLE- Invalid JobHandle.
• DSJE.BADTYPE- unrecognized InfoType.
• DSJE.NOTINSTAGE- Name of stage is DSJ.ME and the calling function is not running within a stage.
• DSJE.BADSTAGE- StageName not found in the job.
• DSJE.BADCUSTINFO- CustInfoName
  not found in custinfo item.

Example:
Voutput=DSGetCustInfo(Ptest_Handle,”transformer”,Test_Result,DSJ.CUSTINFODESC)
In the above example Voutput will display the description about Test_Result variable obtained from the transformer stage of Ptest_Handle job

DSGetLinkInfo

• Fetches information about a link on an active stage, which is again specified during design of job control and stages.

Syntax:

Result = DSGetLinkInfo (JobHandle, StageName, LinkName, InfoType)

• JobHandle is the handle for the job previously obtained from DSAttachJob. or it may be DSJ.ME to refer to the current job.
• StageName is the name of the active stage from which information is to be obtained. It may also be DSJ.ME to refer to the current stage if necessary.
• LinkName is the name of a link (input or output) associated with the stage. It may also be DSJ.ME to refer to current link (e.g. when used in a Transformer expression or transform function called from link code).
• InfoType specifies the type of information required and its value can be one of: DSJ.LINKLASTERR DSJ.LINKNAME DSJ.LINKROWCOUNT
• Depending on InfoType value of Result can be:
  • DSJ.LINKLASTERR String – last error message from the link.
  • DSJ.LINKNAME String – Gives link name
  • DSJ.LINKROWCOUNT Integer – current number of rows in the link
  • DSJ.LINKSQLSTATE – the SQL state for the last error on this link.
  • DSJ.LINKDBMSCODE – the DBMS code for the last error on this link.
  • DSJ.LINKDESC – description of the link.
  • DSJ.LINKSTAGE – name of the stage at the other end of the link.
  • DSJ.INSTROWCOUNT –list of rowcounts
  • DSJ.LINKEOTROWCOUNT – row count since last EndOfTransmission block.
  There can also be some errors reported from result as follows:
  • DSJE.BADHANDLE- Invalid JobHandle.
  • DSJE.BADTYPE- Unrecognized InfoType.
  • DSJE.BADSTAGE- StageName not found in job.
  • DSJE.NOTINSTAGE- Name of stage is DSJ.ME and the calling function is not running within a stage.
  • DSJE.BADLINK- LinkName not found in stage.

Example:

Result_Testlink = DSGetLinkInfo(Ptest_handle, “loader”, “Test_Number”, DSJ.LINKROWCOUNT)
In the above example Result_Testlink will display the LinkRowcount information for Test_Number Link in the loader stage of Ptest job.

DSGetProjectInfo

• Provides information about the current project.

Syntax:

Result = DSGetProjectInfo (InfoType)

• InfoType specifies the type of information required and can be one of:
  DSJ.JOBLIST, DSJ.PROJECTNAME, DSJ.HOSTNAME
• Result is a string value. Depending on the specified InfoType Result can be as follows:
  • DSJ.JOBLIST- comma-separated list of names of all jobs associated with the project.
  • DSJ.PROJECTNAME- Current project name.
  • DSJ.HOSTNAME- the host name of the server where current project is saved.

There can also be some errors reported from result as follows:

DSJE.BADTYPE- Unrecognized InfoType.

Example:
Project_Info=DSGetProjectInfo(DSJ.HOSTNAME)
In the above example Project_Info will give the Host name of the currently running project.

DSLogEvent

• Logs an event message to a job which is not currently active. It uses DSLogInfo, DSLogFatal or DSLogWarn for event logging.

Syntax:
ErrorMsg = DSLogEvent (JobHandle, EventType, EventMsg)

• JobHandle is the handle for the job previously obtained from DSAttachJob.
• EventType is the type of event logged and is one of:

DSJ.LOGINFO Information message

DSJ.LOGWARNING Warning message

• EventMsg is a string value containing the event message.
• ErrorMsg is 0 if no error. In case of error its value can be:

DSJE.BADHANDLE- Invalid JobHandle.
DSJE.BADTYPE- Invalid EventType
Example:
ErrorMsg = DsLogEvent (Ptest_handle, DSJ.LOGWARNING,”Warning:Error in Test_result”)
In the above example this command is executed from Preport job and it logs the warning error message in Ptest job.

DSLogInfo

• Logs an information message in a job’s log file.

Syntax:

Call DSLogInfo (msg, func_name)

• msg (input string) is the information message which gets logged. it is automatically prefixed with the name of the current stage and the calling program.
• func_name (input string) is the name of the transform function or before/after subroutine that in turn calls the DSLogInfo subroutine.

Example:

Call DSLogInfo(“Saving Result of Test1: “:Result1, “Ptest_Result1”)
In the above example the message “Saving Result of Test1:”Result1 will get saved in log file of Ptest job. This function gets called by the function Ptest_Result1

DSPrepareJob

• Used to check that a compiled job is ready to be executed or validated.

Syntax:
JobHandle = DSPrepareJob (JobHandle)

• JobHandle is the handle for the job previously obtained from DSAttachJob, which is to be checked. JobHandle is either the original handle or a new one. It returns 0 in case of an error.

Example:
Ptest = DSPrepareJob (Ptest)
This command will prepare the job Ptest for validation or execution.

DSRunJob

• Starts running a job. But this call is asynchronous and instruction is passed to run time engine. Users are not aware of the progress of the running job.this job must be locked before calling DSRunJob by DSLockJob function.

Syntax:
ErrorMsg = DSRunJob (JobHandle, RunMode)

• JobHandle is the handle for the job previously obtained from DSAttachJob.
• RunMode-Job is run in the mode specified by RunMode. It can be:
  • DSJ.RUNNORMAL-Default mode
    
  • DSJ.RUNRESET-Specified job reset.
  • DSJ.RUNVALIDATE-Specified job is to be validated only. DSRunJob is successful if ErrorMsg is 0,In other cases it can be:
    • DSJE.BADHANDLE- Invalid JobHandle.
    • DSJE.BADSTATE- Job is not running or in compiled state
    • DSJE.BADTYPE- Invalid RunMode.
    • DSJE.SERVER_ERROR-Internal Error in DataStage server

Example:

ErrorMsg = DSRunJob(Ptest_handle, DSJ.RUNVALIDATE)
By running above command, Ptest job is validated and if there is an error it gets logged in ErrorMsg otherwise result is 0.

DSSendMail

• This function invokes a sendmail program that is lies somewhere in the search path of the current user (on the server). It is used for sending text in a simple interface.

Syntax:
Mail_Result = DSSendMail (Parameters)

• Parameters are a set of name: value parameters, separated by either a mark character or ″\n″. Examples of parameters are “From”, “To”,” Subject”, “Server” & “Body”
• Mail_Result can be:
  • DSJE.NOERROR (0)- OK
  • DSJE.NOPARAM-Parameter name missing – field does not look like ‘name: value’
  • DSJE.NOTEMPLATE- Cannot find template file
  • DSJE.BADTEMPLATE- Error in template file

Example:
Mail_Result = DSSendMail(“From:A@Infosys.com\nTo:B@Infosys.com\nSubject:Project Details\nBody:Project Name is\nDataStage”)

DSSetGenerateOpMetaData

• Use this to specify whether the job generates operational metadata or not. This setting overrides the default setting for the project. Additionally process metabroker should be installed in the system to generate metadata.

Syntax:


ErrCode = DSSetGenerateOpMetaData (JobHandle, value)

• JobHandle is the handle for the job previously obtained from DSAttachJob.
• value is TRUE to generate operational metadata, FALSE indicates not to generate operational metadata.
• if DSRunJob is successful ErrorMsg is 0, otherwise it is one of the following negative integers:

DSJE.BADHANDLE- Invalid JobHandle.
DSJE.BADTYPE- Invalid value.
Example:

ErrCode = DSSetGenerateOpMetaData(Ptest_handle, TRUE)
In the above example Operational metadata will be generated for Ptest job overriding the project settings

DSSetJobLimit

• By default a controlled job inherits any row or warning limits from the controlling job. But DSSetJobLimit function can override these limits.

Syntax:


ErrorMsg = DSSetJobLimit (JobHandle, LimitType, LimitValue)

• JobHandle is the handle for the job previously obtained from DSAttachJob.
• LimitType is the name of the limit to be applied to the running job according to LimitValue and its value can be:
  • DSJ.LIMITWARN-Job stops after LimitValue number of warning events occur.
  • DSJ.LIMITROWS-Job stops after LimitValue number of rows in stages
• LimitValue is an integer specifying the value to set the limit to. Set this to 0 to specify unlimited warnings.
• ErrorMsg is 0 if DSSetJobLimit is successful; otherwise it is one of the following negative integers:
  • DSJE.BADHANDLE- Invalid JobHandle.
  • DSJE.BADSTATE- Job is not in the right state (compiled, not running).
  • DSJE.BADTYPE- Invalid LimitType
  • DSJE.BADVALUE Invalid LimitValue

Example:
ErrorMsg = DSSetJobLimit (Ptest_handle, →DSJ.LIMITROWS, 500)
The above command sets a limit of 500 rows on the stages in Ptest job before it is stopped

DSSetParam

• Used to declare job parameters before executing a job. The parameters which are not set will have default values.

Syntax:

ErrorMsg = DSSetParam (JobHandle, ParamName, ParamValue)

• JobHandle is the handle for the job previously obtained from DSAttachJob.
• ParamName is a string specifying the name of the parameter.
• ParamValue is a string specifying the value for the parameter.
• if DSSetParam is successful ErrorMsg is 0, else it can be:
  • DSJE.BADHANDLE Invalid JobHandle.
  • DSJE.BADSTATE Job is not in the right state (compiled, not running).
  • DSJE.BADPARAM ParamName is not a known parameter of the job.
  • DSJE.BADVALUE ParamValue is not appropriate for that parameter type.

Example:
ErrorMsg = DSSetParam (Ptest_handle, “No_of_Tests”, “10”)
ErrorMsg = DSSetParam (Ptest_handle, “CutoffPercent”, →“70”)
The above commands set the No_of_Tests parameter to 10 and the CutoffPercent parameter to 70 for the Ptest job:

DSStopJob

• Used to send a stop request to runtime engine regardless of current status of job. This should only be used after calling a DSRunJob. The call is asynchronous. To know the actual status of job, call DSWaitForJob or use the Sleep statement and poll for DSGetJobStatus.

Syntax:


ErrorMsg = DSStopJob (JobHandle)

• JobHandle is the handle for the job previously obtained from DSAttachJob.
• if DSStopJob is successful ErrorMsg is 0, else it can be:

DSJE.BADHANDLE- Invalid JobHandle.

Example:
ErrorMsg = DSStopJob(Ptest_handle)
The above command sends a stop request for Ptest job.

DSWaitForFile

• Suspend a job until the specified file is found or not found in the given timeout value.

Syntax:
Wait = DSWaitForFile(Parameter)

• Parameter is the full path to be checked for file existence. It is not checked whether the given path is valid or not. A “-“at the beginning of the path name indicates that the path is to be checked for non existence of the file. Parameters can also end with a timeout value in the form “timeout: Format” or “timeout=Format”. Format can be
  • nnn- number of seconds to wait (from now)
  • nnnS- number of seconds to wait (from now)
  • nnnM- number of minutes to wait (from now)
  • nnnH- number of hours to wait (from now)
  • nn:nn:nn- waits until this time in 24 hour format. If this time has passed, it will wait till next day.
  The format may also terminate “/nn” which indicates poll delay. Else default poll time is used.
• Value of Wait may be:
  • DSJE.NOERROR (0) OK – file now exists or does not exist, depending on flag.
  • DSJE.BADTIME- Unrecognized Timeout format
  • DSJE.NOFILEPATH- File path missing
  • DSJE.TIMEOUT- Timeout threshold exceeded

Examples:

• Wait = DSWaitForFile(“C:\wait.txt timeout:3H”)

(Waits for 3 hours for file on C: to exist before it gives up.)

• Wait = DSWaitForFile(“-wait.txt timeout=18:00”)

(Waits until 6 p.m. for file in local directory to NOT exist.)

• Wait = DSWaitForFile(“wait.txt timeout:3600/60”)

(Waits 1 hour for a local file to exist, looking once a minute.)

DSWaitForJob

• This is used to wait for the completion of the given job run. It is only valid if the current job has called DSRunJob on the given Job Handle. It returns if the job has started since the last DSRunJob and has since finished. The finishing status can be found by calling DSGetJobInfo.

Syntax:

ErrorMsg = DSWaitForJob (JobHandle)

• JobHandle is the string obtained from DSAttachJob.
• ErrorMsg is 0 if no error, else possible negative error values are:
  • DSJE.BADHANDLE- Invalid JobHandle.
  • DSJE.WRONGJOB- Job for this JobHandle was not run from within this job.
  • DSJE.TIMEOUT-Job doesn’t start after waiting for the time specified in timeout.

If ErrorMsg >0 then this implies that the given job has finished waiting from multiple jobs.
Example:
ErrorMsg = DSWaitForJob(Ptest_handle)
The issue of above command will make the current job to wait for the completion of the Ptest job.

Some more Subroutine examples

• This subroutine compiles an aborted job
  $INCLUDE DSINCLUDE JOBCONTROL.H
  Job1_Handle = DSAttachJob (J1, DSJ.ERRFATAL)
  Status = DSGetJobInfo (Job1_Handle, DSJ.JOBSTATUS)
  If Status = DSJS.RUNFAILED Then
  ErrorMsg = DSRunJob (Job1_Handle, DSJ.RUNRESET)
  ErrorMsg = DSWaitForJob (Job1_Handle)
  End
  ErrorMsg = DSDetachJob (Job1_Handle)
  Return
• Displays the job link statistics
  $INCLUDE DSINCLUDE JOBCONTROL.H 
  Job1 = DSAttachJob(Ptest, DSJ.ERRFATAL) 
  Start_Time = DSGetJobInfo (Job1, DSJ.JOBSTARTTIMESTAMP) 
  End_Time = DSGetJobInfo (Job1,DSJ.JOBLASTTIMESTAMP) 
  Job_Elapse_Sec = DSGetJobInfo (Job1,DSJ.JOBELAPSED) 
  Job_Final_Status = DSGetJobInfo (Job1,DSJ.JOBINTERIMSTATUS) 
  User_Final_Status = DSGetJobInfo (Job1,DSJ.USERSTATUS) 
  ErrorMsg = DSDetachJob(Job1)