Get all child status recursively

[leandrohmvieira]

Greetings,



I have some sequences (1,2,3) and server jobs (4,5,6,7)

Its possible to attach some job on sequence 1 which looks for all her childs for aborts statuses? Or any other way to do this?

I would like to add a row on a DB for each abort into this sequence.

Sorry for my bad english and thanks in advance.

[ray.wurlod]

Welcome aboard.

This is most easily done using a routine that invokes functions from the DataStage API.

[leandrohmvieira]

Thank you,

There is a function which give me all the childs under a job?

[ArndW]

No, there is no single function to do this. The documented API calls will return information about a job's status once you know the name of the job. While there is no call to get a list of jobs called from a sequence, there is a call to get log entries. Using the log entries, you can search them for job starts and get the appropriate job name, then get the status of that run and recursively search for further jobs.

DataStage BASIC is a recursive language, so it can be done.

[ray.wurlod]

There are two sets of child jobs; design-time (the job activities in the sequence) and run-time (the jobs that were actually run). How to get these has been discussed in the past on DSXchange so you should be able to find them using a Search.

[kduke]

EtlStats does this. There is code in the get row count driver that if the job name is a sequence then it will loop through all the jobs and send each job name to the get row counts job.

Code: Select all

* ------------------------------------------------------------
* DSJobReportDbDriver

* ------------------------------------------------------------
* Get JobNo
* ------------------------------------------------------------
      if downcase(SeqName) <> 'all' then
         read JobRec from DsJobs, SeqName then
            JobNo = JobRec<5>
            ObjId = 'J\':JobNo:'\ROOT'
            read ObjRec from DsJobObjects, ObjId then
               MutipleInstanceFlag = ObjRec<59>
               JobType = ObjRec<40>
               if JobType = '2' or BatchJob = '1' then
                  Cnt = 0
                  DependJob = SeqName
                  gosub GetRows
* ------------------------------------------------------------
* loop thru dependent job names
* ------------------------------------------------------------
                  NoJobs = dcount(ObjRec<31>, @VM)
                  for i = 1 to NoJobs
                     DependJob = ObjRec<31,i>
                     DependType = ObjRec<32,i>
                     if DependType = '0' then
* ------------------------------------------------------------
* attach, get status, get row counts
* ------------------------------------------------------------
                        gosub GetRows
                     end
                  next i

      goto TheEnd
* ------------------------------------------------------------
GetRows:
      Cnt += 1
      JobReportName = "DSJobReportDb.":convert(".","_",DependJob)
      read DependJobRec from DsJobs, DependJob then
         DependJobJobNo = DependJobRec<5>
         DependJobObjId = 'J\':DependJobJobNo:'\ROOT'
         read DependJobObjRec from DsJobObjects, DependJobObjId then
            MutipleInstanceFlag = DependJobObjRec<59>
            if MutipleInstanceFlag <> 1 or field(DependJob, '.', 1)='GetMaxTableKey' then

               Call DSLogInfo("Getting row counts for ":DependJob, "JobControl")
* ------------------------------------------------------------
* Setup DSJobReportDb, run it, wait for it to finish, and test for success
* ------------------------------------------------------------
               hJob1 = DSAttachJob(JobReportName, DSJ.ERRFATAL)
               If NOT(hJob1) Then
                  ErrMsg = "Error: Job Attach Failed: ":JobReportName
                  gosub ErrRtn
                  goto SkipRun
               End
* ------------------------------------------------------------
* Kim added to reset a bad job status
* ------------------------------------------------------------
               LastRunStatus = DSGetJobInfo(hJob1, DSJ.JOBSTATUS)
               If LastRunStatus = DSJS.RUNFAILED Or LastRunStatus = DSJS.CRASHED Or LastRunStatus = DSJS.STOPPED Then
                  Call DSLogInfo(JobReportName, "Reseting job and last run status")
                  ErrCode = DSRunJob(hJob1, DSJ.RUNRESET)
                  ErrCode = DSWaitForJob(hJob1)
                  ErrCode = DSDetachJob(hJob1)
                  hJob1 = DSAttachJob(JobReportName, DSJ.ERRFATAL)
                  If NOT(hJob1) Then
                     Call DSLogFatal("Job Attach Failed: ":JobReportName, "JobControl")
                     Abort
                  End
               end
* ------------------------------------------------------------
               * ErrCode = DSSetParam(hJob1, "dsjobcmd", "dsjob -report")
               ErrCode = DSSetParam(hJob1, "projName", @WHO)
               ErrCode = DSSetParam(hJob1, "jobName", DependJob)
               ErrCode = DSSetParam(hJob1, "TargetDSN", TargetDSN)
               ErrCode = DSSetParam(hJob1, "TargetUser", TargetUser)
               ErrCode = DSSetParam(hJob1, "TargetPwd", TargetPwd)
               ErrCode = DSRunJob(hJob1, DSJ.RUNNORMAL)
               ErrCode = DSWaitForJob(hJob1)
               Status = DSGetJobInfo(hJob1, DSJ.JOBSTATUS)
               If Status = DSJS.RUNFAILED Or Status = DSJS.CRASHED Then
                  * Fatal Error - No Return
                  ErrMsg = "Error: Job Failed: ":JobReportName
                  gosub ErrRtn
               End
            end else
               ErrMsg = 'Error: ':DependJob:' unable to get rows counts for multiple instance job without InvocationId.'
               gosub ErrRtn
            end
         end else
            ErrMsg = 'Error: ':DependJob:' not a valid Job. No ':DependJobObjId:' record.'
            gosub ErrRtn
         end
      end else
         ErrMsg = 'Error: ':DependJob:' not a valid Job.'
         gosub ErrRtn
      end
SkipRun:
      return
* ------------------------------------------------------------
ErrRtn:
      Call DSLogInfo(ErrMsg , "JobControl")
      return
* ------------------------------------------------------------
TheEnd:

I deleted a lot of code not needed but this is close. Just get rid of running my get row counts job and do whatever you want instead. A lot of useless code in this but it should get you close. You need to turn this into a routine. Currently this is a batch job.

[leandrohmvieira]

Hello, im back to this problem, and im trying to do what ArndW said, search in the log entries to find which child has aborted from a parent sequence.

This is what i have so far:

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H 
$INCLUDE DSINCLUDE DSJ_XFUNCS.H 

*get parent job handle
JobHandle = DSAttachJob(Arg1,DSJ.ERRNONE)

*get first and last eventID from the lastest execution
IdList = DSGetLogEventIds (JobHandle, 0, "S")
FirstLog = LEFT(IdList,4)
LastLog = RIGHT(IdList,4)

*Create a list of aborted childs
Found = ""

FOR Counter = FirstLog TO LastLog

	*get summary from log
	Info = DSGetLogEntry(JobHandle,Counter)
	Detail = FIELD(Info,"\",4)

	*If some Detail contains "status = 3 (Aborted)", get the job name
	IF Detail MATCH "...'status = 3 (Aborted)'..." then
		*Found some aborted job, store it
		Found = Found:",":MATCHFIELD (Detail, "...'Job '...' has'", 1)

NEXT Counter

But, im having some problems to get the job name from a log summary.

Example:

"SeqDataWarehouse..JobControl (DSWaitForJob): Job SeqDimension has finished, status = 3 (Aborted)" is a example line which contains a aborted child.

But i can't figure out how MATCHFIELD() works, does it accept 'text' entries?

Another further question, how to make this routine recursive? I still don't get it either

[leandrohmvieira]

I've made a recursive routine which look for all child jobs with a breadth search, thx everyone who helped.

:D

[chulett]

Care to post it for the greater good?

[leandrohmvieira]

The code have some issues, and wasn't tested for every case yet, but here it is:

Code: Select all

*//FUNCTION GetAbortedJobs(Arg1,Arg2)

*//Make it recursive
DEFFUN GetAbortedJobs(Arg1,Arg2) CALLING 'DSU.GetAbortedJobs'
$INCLUDE DSINCLUDE JOBCONTROL.H 
$INCLUDE DSINCLUDE DSJ_XFUNCS.H

*//Could not figure out how to call it from a sequence with empty Arg2, help me on http://www.dsxchange.com/viewtopic.php?t=155819
IF Arg2 = "START" then Arg2 = ""
AnswerList = Arg2

InList = ""

LoopJobs = COUNT(Arg1,',')+1

*//For each job, look his log for abort information e.g: "Job Seq_Insere_final has finished, status = 3 (Aborted)"
FOR CounterJobs = 1 to LoopJobs
	
	Flag = 0
	ParentJob = FIELD(Arg1,',',CounterJobs)
	JobHandle = DSAttachJob(ParentJob,DSJ.ERRNONE)
	IdList = DSGetLogEventIds (JobHandle, 0, "I")
	LoopLogs = COUNT(IdList,'\')+1

	FOR Counterlogs = 1 to LoopLogs

		Info = DSGetLogEntry(JobHandle,FIELD(IdList,'\',Counterlogs))
		Detail = FIELD(Info,"\",4)
		*// If it find some aborted logs, get jobname and add it no next step of recursion
		IF Detail MATCH "...'status = 3 (Aborted)'..." THEN 
 			Result = MATCHFIELD(Detail,"0X'Job '0X' has'0X",3)
 			Flag = 1
 			IF InList = "" THEN InList = Result
 			ELSE  InList :=",":Result
 			
		END
	NEXT Counterlogs
	*//if it doesn't find any aborted logs, probably its a aborted "leaf", so add ParentJob to solution
	IF Flag = 0 THEN
			IF AnswerList = "" THEN AnswerList = ParentJob
 			ELSE  AnswerList :=",":ParentJob
 	END

NEXT CounterJobs

*// if InList is empty, all aborted child jobs under controller have been checked, if not, go to next step
IF InList = "" THEN Ans = AnswerList
ELSE Ans = GetAbortedJobs(InList,AnswerList)


*//RETURN(Ans)

It's not the optimal solution i guess, but is working fine until now.

If someone have some suggestions, please go ahead.

[rkashyap]

If you are using Operations Console then requested (and lot more) information is getting logged in DSODB tables.

Job that are triggered in DataStage, under a single sequence or via job control from another job, are grouped in DSODB.JobRun table. This information/relationship can be extracted by doing a self-join on this table using keys RunId and Controlling_RunId. Controlling_RunId column is foreign key to another entry in JobRun, which has triggered this job. Various jobs started by a particular sequence are related by common Controlling_RunId and form a tree-structured set of relationships for nested job/subsequences.

Sample PL/SQL Code

Code: Select all

WITH RUNHIER AS
  (SELECT X.RUNID, X.JOBID, X.CONTROLLING_RUNID, X.RUNMAJORSTATUS, X.RUNMINORSTATUS,
    X.NUMMESSAGESFATAL, X.NUMMESSAGESTOTAL, X.NUMMESSAGESWARNING, X.RUNENDTIMESTAMP,
    X.RUNSTARTTIMESTAMP, X.RUNTYPE, X.USERSTATUS, X.INVOCATIONID
  FROM DSODB.JOBRUN X
  WHERE  X.JOBID  IN
    (SELECT E.JOBID FROM DSODB.JOBEXEC E WHERE E.JOBNAME = #YOUR_SEQ_JOBNAME#)
    CONNECT BY prior X.RUNID = x.controlling_runid
  )
SELECT Y.PROJECTNAME, Y.JOBNAME, Y.JOBTYPE, R.RUNID, R.JOBID, R.CONTROLLING_RUNID,
  R.RUNMAJORSTATUS, R.RUNMINORSTATUS, R.NUMMESSAGESFATAL, R.NUMMESSAGESTOTAL,
  R.NUMMESSAGESWARNING, R.RUNENDTIMESTAMP, R.RUNSTARTTIMESTAMP,
  R.RUNTYPE, R.USERSTATUS, R.INVOCATIONID
FROM RUNHIER R, DSODB.JOBEXEC Y
WHERE R.JOBID         = Y.JOBID
AND R.RUNMINORSTATUS IN ('FWF','STP','CRA','SYN');

[leandrohmvieira]

I can't acess the Operations Console, but thanks anyway.