Get all child status recursively
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 44
- Joined: Wed Sep 02, 2015 7:19 am
- Location: Brasilia, Brazil
Get all child status recursively
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.
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.
Leandro Vieira
Data Expert - Brasilia, Brazil
Data Expert - Brasilia, Brazil
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 44
- Joined: Wed Sep 02, 2015 7:19 am
- Location: Brasilia, Brazil
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.
DataStage BASIC is a recursive language, so it can be done.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
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:
Mamu Kim
-
- Participant
- Posts: 44
- Joined: Wed Sep 02, 2015 7:19 am
- Location: Brasilia, Brazil
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:
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
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
Leandro Vieira
Data Expert - Brasilia, Brazil
Data Expert - Brasilia, Brazil
-
- Participant
- Posts: 44
- Joined: Wed Sep 02, 2015 7:19 am
- Location: Brasilia, Brazil
-
- Participant
- Posts: 44
- Joined: Wed Sep 02, 2015 7:19 am
- Location: Brasilia, Brazil
The code have some issues, and wasn't tested for every case yet, but here it is:
It's not the optimal solution i guess, but is working fine until now.
If someone have some suggestions, please go ahead.
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)
If someone have some suggestions, please go ahead.
Leandro Vieira
Data Expert - Brasilia, Brazil
Data Expert - Brasilia, Brazil
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
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');
-
- Participant
- Posts: 44
- Joined: Wed Sep 02, 2015 7:19 am
- Location: Brasilia, Brazil