Job dependency report

[ureddy]

Hi All,

I am trying to extract the job dependency list from sequence job by using the below query in universe stage, but i am getting the below error.

Query:
SELECT DS_JOBS.NAME, EVAL DS_JOBOBJECTS.'@RECORD<31>' FMT '32L' COL.HDG 'DEPENDENT_JOBS'
FROM DS_JOBS, DS_JOBOBJECTS
WHERE DS_JOBS.NAME = '<<SequenceName>>'
AND DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
AND DS_JOBOBJECTS.OBJTYPE = 'J'
AND DS_JOBOBJECTS.OBJNAME = 'ROOT'
AND DS_JOBS.JOBTYPEIND = '2';

Error: syntax error. Unexpected verb. Token was "SELECT".
Scanned command was SELECT

Can you please help me how to overcome this error?

Thanks,
ureddy

[ArndW]

I declared 2 columns and then added a Job Parameter SequenceName and then edited your SELECT to read "SELECT DS_JOBS.NAME, DS_JOBOBJECTS.NAME FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.NAME = '#SequenceName#' AND DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO AND DS_JOBOBJECTS.OBJTYPE = 'J' AND DS_JOBOBJECTS.OBJNAME = 'ROOT' AND DS_JOBS.JOBTYPEIND = '2'; "

(all on one line).

[ureddy]

Its working after writing the query on one line. Thanks ArndW
I cannot list all the dependent jobs from the master sequence,few jobs are not listing.

Master_seq
|
JobA
|
JobB
/ \
JobC JobD
|
JobE

Note: JobB to JobC and JobD

Except JobD I am getting all the jobs in dependency list. I checked the JobD and is in compiled state, but still its missing in the report.

I could see all the jobs(JobA,JobB,JobC,JobE and JobF) in JobProperties->Dependency for Master_seq job except JobD.

Any idea why the job is missing from the report?

Thanks in advance.

Regards,
ureddy

[ArndW]

Field 31 is a multivalue one which is expanded correctly when I perform this query manually in my environment and all elements are returned. In your case somehow the first and last elements are skipped. Is it the same behaviour if you choose another job sequence?

[kduke]

If you are running this through a job then you can use this routine to extract the real dependencies.

Code: Select all

* -----------------------------------------------------------------
* EtlStatsGetDependJobs(SeqJobName, LeaveInvocationID)
* Decription: Returns Jobs run by SeqJobName.
* Written by: Kim Duke
* Notes:
* -----------------------------------------------------------------
      Ans = ''
      RoutineName = "EtlStatsGetDependJobs"
      Usage = RoutineName:'(SeqJobName)'
* -----------------------------------------------------------------
* initialize variables
* -----------------------------------------------------------------
      True = 1
      False = 0
      Other = True
      Forever = True
      Today = oconv(@DATE, "D4-")
      ErrMsg = ''
      NullRec = ''
      CrLf = char(13):char(10)
      JobList = ''
      Cnt = 0
* -----------------------------------------------------------------
      DebugSw = @FALSE
      * LeaveInvocationID = @TRUE
      FileName = 'DS_JOBS'
      open FileName to FilePtr else
         ErrMsg = 'Can not open ':FileName:' file.'
         goto ErrRtn
      end
      DsJobsPtr = FilePtr
* -----------------------------------------------------------------
      FileName = 'DS_JOBOBJECTS'
      open FileName to FilePtr else
         ErrMsg = 'Can not open ':FileName:' file.'
         goto ErrRtn
      end
      DsJobObjectsPtr = FilePtr
* ----------------------------------------------------------------------
      dummy = ""
      TheSeqJobName = field(SeqJobName, '.', 1)
      read JobRec From DsJobsPtr, TheSeqJobName else
         ErrMsg = 'No JOB: ':TheSeqJobName
         goto ErrRtn
      end
      JobNo = JobRec<5>
* ----------------------------------------------------------------------
      Id = 'J\':JobNo:'\ROOT'
      if dummy = "X" then goto TheEnd
* ----------------------------------------------------------------------
      read DsObjectsRec From DsJobObjectsPtr, Id else
         ErrMsg = Id:' not found in ':TheSeqJobName:' DS_JOBOBJECTS ':JobNo
         goto ErrRtn
      end
      read JobRec From DsJobsPtr, TheSeqJobName then
         JobNo = JobRec<5>
         ObjId = 'J\':JobNo:'\ROOT'
         read ObjRec From DsJobObjectsPtr, ObjId then
            SourceCode = convert(CrLf, @FM, ObjRec<11>)
            Occurrence = 1
            AttrNo = 1
            MvNo = 1
            SvNo = 1
* ----------------------------------------------------------------------
* Looking for:
*    jb$V0S0 = "Affiliate_03_E_AS400"
*    h$V0S0 = DSAttachJob(jb$V0S0, DSJ.ERRNONE)
* ----------------------------------------------------------------------
FindJobs:
            FindStr " = DSAttachJob(" In SourceCode, Occurrence Setting AttrNo, MvNo, SvNo Then
               Cnt += 1
* ----------------------------------------------------------------------
* Backup 1 line:
*    jb$V0S0 = "Affiliate_03_E_AS400"
* ----------------------------------------------------------------------
               DependJob = SourceCode<AttrNo - 1, 1, 1>
* ----------------------------------------------------------------------
* Strip off junk:
* "Affiliate_03_E_AS400"
* ----------------------------------------------------------------------
               DependJob = field(DependJob, "=", 2)
               DependJob = trim(DependJob)
               DependJob = convert('"', '', DependJob)
* ----------------------------------------------------------------------
* Strip off junk:
* ETL_Checkpoint:'.':(CheckpointInvocationIDBegin)
* ----------------------------------------------------------------------
               if LeaveInvocationID then
                  DependJob = convert("':()", '', DependJob)
               end else
* ----------------------------------------------------------------------
* Strip off InvocationID:
* ETL_Checkpoint:'.':(CheckpointInvocationIDBegin)
* ----------------------------------------------------------------------
                  DependJob = field(DependJob, ':', 1)
               end
               JobList<Cnt> = DependJob
               Occurrence += 1
               goto FindJobs
            end
            if Cnt > 0 then Ans = JobList
         end
      end

ExitRtn:
      goto TheEnd
* ----------------------------------------------------------------------
ErrRtn:
      if DebugSw then print
      if DebugSw then print ErrMsg
      if DebugSw then print
      * Ans = ErrMsg
* ----------------------------------------------------------------------
TheEnd:
      if DebugSw then print
      if DebugSw then print
      if DebugSw then print Ans
      if DebugSw then print

[srinath0253]

Its premium message. Can someone post the kduke, ArndW code [OR] PM me. Im also searching for query to fetch the job dependency.
Thanks in advance.

[kduke]

This is in response to an message sent to me.

When you use designer to build a sequence it will build out a dependency field in the ROOT record. You can see what is stored in this field in the job properties page. You can edit this field and make it so it is not accurate. Why someone would do that, I do not know. The first post will will work most if time unless someone has manually changed the dependent items under job properties.

The code I posted below will look through the code generated by the Designer. You can see this code under the code tab in job properties. The FindStr will look for any string within another string. Kind of like the index function. It is looking for DSAttachJob because that is when it runs another job from this sequence. All you need to is turn this into a DataStage routine. Test it. Make sure it works. Then write you a Server job which loops through all the jobs and writes this list out to a table with sequence and depend job. Once in a table you can write any report you need.

Otherwise you can take the SELECT in the first post and remove the ";" and run it. This SELECT is NOT a SQL select therefore does not need a ";" on the end.

Cut and paste the code above into a DataStage routine. Give it the 2 arguments listed in the comments. Test it. Posts results. If you need more help then let us know.

There is a job in EtlStats which does all this. You can try to load it and run it. The job name has DEPEND in it. There are lots of jobs in EtlStats which pull this kind of metadata. Some will no longer work because EtlStats was written 10 years ago. But it should help you get started.