How can i get all the jobs name in a sequence
Moderators: chulett, rschirm, roy
How can i get all the jobs name in a sequence
Hello,
i need to generate a report for the datastage jobs. For some reason, i am not allowed to modified the sequence/jobs itself. All i can do is to create a new job/routine.
I am facing a problem when i need to get all the jobs name in the main sequence, sub-sequence or sub-sub-sequence. There are around a total of 200-300 jobs in that particular main sequence. For that reason, i could not hardcode it myself. There are also few main sequence in the job and i just cannot select all the job by the command "select jobname from dsjob".
Can that be achieve by put constaint in the statement? Any Guru here willing to share the knowledge doing that?
Thanks in advance!
i need to generate a report for the datastage jobs. For some reason, i am not allowed to modified the sequence/jobs itself. All i can do is to create a new job/routine.
I am facing a problem when i need to get all the jobs name in the main sequence, sub-sequence or sub-sub-sequence. There are around a total of 200-300 jobs in that particular main sequence. For that reason, i could not hardcode it myself. There are also few main sequence in the job and i just cannot select all the job by the command "select jobname from dsjob".
Can that be achieve by put constaint in the statement? Any Guru here willing to share the knowledge doing that?
Thanks in advance!
Last edited by jerrynn on Mon Apr 03, 2006 2:33 am, edited 1 time in total.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
"Hardcore" is a good word, if inadvertent. To do this in a single query would be impossible (recursive SQL does not exist in DataStage Repository). You would need to identify job sequences in DS_JOBS then, for each, generate a list of the jobs run under its control based on records in DS_JOBOBJECTS. I don't have access to DataStage just at the moment. It can be done (I have done it in the past), but you need to store intermediate results, if I recall correctly. It can definitely be done with a recursive server routine.
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.
Iam not sure what is the feild which will help to identify the dependcy among the job in DS_JOBOBJECTS. I could not find a use full output form 'DEPENDS' feild in DS_JOBOBJECTS. But dsjob -logdetail for the main sequence will give you the full log. If you manage to grep within the log you can find the list of jobs being called. Of in the individual jobs you can find '(Main Sequnce) <- Job name: Job under control finished.' as the last log.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Kumar is on the right track. I think the SQL below could be better. I will send a better version later today.
Code: Select all
SELECT
NAME,
CATEGORY,
EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS DEPEND_JOBS FMT '35L',
EVAL "if TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',40,'X') = '2' then 'Y' else 'N'" AS SEQ
FROM
DS_JOBS
WHERE
SEQ = 'Y'
ORDER BY
NAME
;
Mamu Kim
Hi kduke
Will this also work on windows plat form.SELECT
NAME,
CATEGORY,
EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS DEPEND_JOBS FMT '35L',
EVAL "if TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',40,'X') = '2' then 'Y' else 'N'" AS SEQ
FROM
DS_JOBS
WHERE
SEQ = 'Y'
ORDER BY
NAME
;
Yes, the query is a DataStage SQL one and is hardware and platform independant.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
That SQL is based on the job dependencies in job properties page. This is not always accurate. This is more accurate.
Code: Select all
select
DS_JOBS.NAME JobName FMT '40L',
DS_JOBOBJECTS.NAME LinkName FMT '30L',
EVAL DS_JOBOBJECTS."@RECORD<12>" AS JobActivtyJobName FMT '40L'
from
DS_JOBOBJECTS,
DS_JOBS
where
DS_JOBOBJECTS.OLETYPE = 'CJSJobActivity'
and DS_JOBS.NAME = 'Control_Sequence'
and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
;
Mamu Kim
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
As noted earlier, it's then up to you to put the data together in such a fashion as to identify sub-sub-sequences and lower. The query does not do that. It can be done with a routine.
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.
I would use this SQL in a UV stage. Output the results into a staging table then you can report on it any way you want. EtlStats has jobs like this. I populate ETL_JOB which has the category and a field that says this job is a sequence.
DwNav lets you drill down into sequences. So you can go from the top sequence down to the lowest level.
EtlStats also has reports like all the run times for the last run of all sequences by date or by job name. This report is very useful in getting the run order. If you run this on a regular basis then you will notice when a sequence runs longer than normal.
DwNav lets you drill down into sequences. So you can go from the top sequence down to the lowest level.
EtlStats also has reports like all the run times for the last run of all sequences by date or by job name. This report is very useful in getting the run order. If you run this on a regular basis then you will notice when a sequence runs longer than normal.
Mamu Kim
You can find a link to it in Kum Duke's signature.
http://www.Duke-Consulting.com/DataStage_Tips.htm
http://www.Duke-Consulting.com/DataStage_Tips.htm
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Hello I have been trying to fetch the all calling sequences ( direct/indirect ) of a job using a routine with the SQL extract provided ...ray.wurlod wrote:As noted earlier, it's then up to you to put the data together in such a fashion as to identify sub-sub-sequences and lower. The query does not do that. It can be done with a routine.
For ex1
SeqA calls SeqB calls Jobd
ex2
SeqA calls Jobx
So if i execute the routine with
Jobd -> Ans = SeqB,SeqA
Jobx -> Ans = SeqA
This is the routine ...
Dependacy(aSearchString)
DEFFUN Dependancy(aSearchstring) CALLING 'DSU.Dependancy'
NAME = aSearchString
Ans = ''
i=0
CMD = "SELECT DS_JOBS.NAME JobName from DS_JOBOBJECTS,DS_JOBS where DS_JOBOBJECTS.OLETYPE = 'CJSJobActivity' and EVAL DS_JOBOBJECTS.'@RECORD<12>' = '":NAME:"' and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO"
EXECUTE CMD CAPTURING CMDRST ;
x = convert (@FM , '|', CMDRST )
If Index(x,"0 records",1) Then
Return(Ans)
End
Else
Str = Trim(Field(x,'|',4,(count(x,'|')-5)))
for i=1 to (count(Str,'|')+1)
Ans=Field(Str,'|',i):Dependancy(Field(Str,'|',i))
Next i
End
This works fine for Ex2 but whenever there is a recursive call this bombs
any ideas where i am going wrong?
thanks