How can i get all the jobs name in a sequence

[jerrynn]

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!

[ray.wurlod]

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

[jerrynn]

"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_JOB ...

lol it was a typo. i meant hardcode

[kumar_s]

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.

[kduke]

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
;

[kumar_s]

Yea... now i could get the list with simple select query. But I dont know why the same didnt worked previously.

[murali]

Hi kduke

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
;

Will this also work on windows plat form.

[ArndW]

Yes, the query is a DataStage SQL one and is hardware and platform independant.

[kduke]

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 
;

[ray.wurlod]

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.

[kduke]

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.

[jerrynn]

Thanks for all the helps. I am really appreciate it.

May i know where can i get EtlStats?

[kumar_s]

You can find a link to it in Kum Duke's signature.

http://www.Duke-Consulting.com/DataStage_Tips.htm

[dwblore]

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.

Hello I have been trying to fetch the all calling sequences ( direct/indirect ) of a job using a routine with the SQL extract provided ...

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

[kduke]

You maybe looping back on yourself. So you are in an infinite loop.