Listing jobs called in a Sequence Job (automatically)

joycerecacho · Post by **joycerecacho** » Fri Jun 12, 2015 10:57 am

Hi everyone!

I would like to know how could I automatically list the names of the jobs that are called in a Sequence job.

Thank you very much.

Best regards,

chulett · Post by **chulett** » Fri Jun 12, 2015 1:46 pm

The dssearch command comes to mind. An exact search here for it should turn up conversations to that effect.

ray.wurlod · Post by **ray.wurlod** » Fri Jun 12, 2015 3:15 pm

There is a log entry called "Summary of Sequence Run" near the end of the sequence's log.

Otherwise you need to create a recursive routine. I have one somewhere - I'll see if I can dig it out.

joycerecacho · Post by **joycerecacho** » Tue Jun 16, 2015 7:29 am

Ray,
In DS designer, If we click on 'properties' (right button on the job name), it opens a windows with a sheet called 'Dependencies'.
There you can also find the job names that the Sequence calls, but since there are many of them, the idea is something not that manual, otherwise I would have to copy and paste one by one.

Thanks!

Best regards,

chulett · Post by **chulett** » Tue Jun 16, 2015 7:38 am

<cough> dssearch </cough>

ray.wurlod · Post by **ray.wurlod** » Mon Jun 22, 2015 12:19 am

OK, I've dug out the routine I was thinking of. Note that it's a recursive routine, so I hope you don't have hierarchies of sequences hundreds of sequences deep!

The code is provided "as is".

Code: Select all

      FUNCTION TraceSequence(aSequenceName,aLevel)
$COPYRIGHT "Copyright (C) 2008, Ray Wurlod.  All rights reserved."
$IFNDEF JOBCONTROL.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$ENDIF
      DEFFUN TraceSequence(Arg1, Arg2) Calling "DSU.TraceSequence"     ; * for recursive calls
      DEFFUN GetLinkRowCounts(Arg1) Calling "DSU.GetLinkRowCounts"

      Begin Case

         Case UnAssigned(aSequenceName)
            Ans = @NULL

         Case IsNull(aSequenceName)
            Ans = @NULL

         Case Trim(aSequenceName) <= " "
            Ans = @NULL

         Case @TRUE

            hJob = DSAttachJob(aSequenceName, DSJ.ERRNONE)
            JobName = DSGetJobInfo(hJob, DSJ.JOBNAME)

            If JobName = DSJE.BADHANDLE
            Then

               Ans = DSJE.BADHANDLE
               Call DSLogWarn("Unable to attach to job sequence " : Quote(aSequenceName), "TraceSequence")

            End
            Else

               ****************************************************************************************************
               * Construct full job name, including invocation ID of multi-instance job if any.
               ****************************************************************************************************

               FullJobName = JobName
               InvocationID = DSGetJobInfo(hJob,DSJ.JOBINVOCATIONID)
               If Len(InvocationID)
               Then
                  FullJobName = FullJobName : "." : InvocationID
               End


               ****************************************************************************************************
               * Determine job type (server, mainframe, sequence, parallel).
               ****************************************************************************************************

               JobTypeInd = Trans("DS_JOBS", JobName, 6, "X") + 0
               If aLevel = 0 Then Ans = ""


               ****************************************************************************************************
               * Process job type appropriately.  Row counts only for jobs, recursion for sequences.
               * If job finish date/time not reported by DSGetJobInfo() look in the job log.
               * Link row counts obtained by routine GetLinkRowCounts.
               ****************************************************************************************************

               Begin Case

                  Case JobTypeInd = 0    ; * server job

                     JobStart = DSGetJobInfo(hJob, DSJ.JOBSTARTTIMESTAMP)
                     JobFinish = DSGetJobInfo(hJob, DSJ.JOBLASTTIMESTAMP)
                     If Not(JobFinish)
                     Then
                        JobFinish = "(unreported)"
                        FinishEventID = DSGetNewestLogId(hJob, DSJ.LOGSTARTED)
                        FinishEvent = DSGetLogEntry(hJob, FinishEventID)
                        If FinishEvent Then JobFinish = Field(FinishEvent, "\\", 1, 1)
                     End
                     Ans = (If aLevel > 0 Then "   " Else "") : "Server Job " : Quote(FullJobName) : " started " : JobStart
                     Gosub DecodeJobStatus
                     If JobExitStatus <> DSJS.RUNNING
                     Then
                        Ans := ", at " : JobFinish : "."
                        LinkRowCounts = GetLinkRowCounts(FullJobName)
                        Ans := "   Records processed = "
                        Ans := Field(LinkRowCounts, "~", 1, 1) : " input, "
                        Ans := Field(LinkRowCounts, "~", 2, 1) : " output, "
                        Ans := Field(LinkRowCounts, "~", 3, 1) : " reject, "
                        Ans := Field(LinkRowCounts, "~", 4, 1) : " insert, "
                        Ans := Field(LinkRowCounts, "~", 5, 1) : " update, "
                        Ans := Field(LinkRowCounts, "~", 6, 1) : " upsert, "
                        Ans := Field(LinkRowCounts, "~", 7, 1) : " delete"
                     End

                  Case JobTypeInd = 1    ; * mainframe job (should never be executed on UNIX)

                     JobStart = DSGetJobInfo(hJob, DSJ.JOBSTARTTIMESTAMP)
                     JobFinish = DSGetJobInfo(hJob, DSJ.JOBLASTTIMESTAMP)
                     If Not(JobFinish)
                     Then
                        JobFinish = "(unreported)"
                        FinishEventID = DSGetNewestLogId(hJob, DSJ.LOGSTARTED)
                        FinishEvent = DSGetLogEntry(hJob, FinishEventID)
                        If FinishEvent Then JobFinish = Field(FinishEvent, "\\", 1, 1)
                     End
                     Ans = (If aLevel > 0 Then "   " Else "") : "Mainframe Job " : Quote(FullJobName) : " started " : JobStart
                     Gosub DecodeJobStatus
                     If JobExitStatus <> DSJS.RUNNING
                     Then
                        Ans := ", at " : JobFinish : "."
                        LinkRowCounts = GetLinkRowCounts(FullJobName)
                        Ans := "   Records processed = "
                        Ans := Field(LinkRowCounts, "~", 1, 1) : " input, "
                        Ans := Field(LinkRowCounts, "~", 2, 1) : " output, "
                        Ans := Field(LinkRowCounts, "~", 3, 1) : " reject, "
                        Ans := Field(LinkRowCounts, "~", 4, 1) : " insert, "
                        Ans := Field(LinkRowCounts, "~", 5, 1) : " update, "
                        Ans := Field(LinkRowCounts, "~", 6, 1) : " upsert, "
                        Ans := Field(LinkRowCounts, "~", 7, 1) : " delete"
                     End

                  Case JobTypeInd = 2    ; * sequence job

                     JobStart = DSGetJobInfo(hJob, DSJ.JOBSTARTTIMESTAMP)
                     JobFinish = DSGetJobInfo(hJob, DSJ.JOBLASTTIMESTAMP)
                     If Not(JobFinish)
                     Then
                        JobFinish = "(unreported)"
                        FinishEventID = DSGetNewestLogId(hJob, DSJ.LOGSTARTED)
                        FinishEvent = DSGetLogEntry(hJob, FinishEventID)
                        If FinishEvent Then JobFinish = Field(FinishEvent, "\\", 1, 1)
                     End

                     EventIDs = DSGetLogEventIds(hJob, 0, "S")         ; * list of log event IDs for current run
                     StartEventID = Field(EventIDs, "\\", 1, 1)
                     FinishEventID = Field(EventIDs, "\\", 2, 1)

                     Ans = "Sequence " : Quote(FullJobName) : " started " : JobStart
                     Gosub DecodeJobStatus
                     If JobExitStatus <> DSJS.RUNNING
                     Then
                        Ans := " at " : JobFinish : "."
                     End

                     For EventID = StartEventID To FinishEventID
                        Entry = DSGetLogEntry(hJob, EventID)
                        If Index(Entry, "Job run requested", 1)
                        Then
                           SubJob = Field(Field(Entry, ")", 1, 1), "(", 2, 1)
                           SubJobInfo = TraceSequence(SubJob, aLevel + 1)        ; * recursive call
                           Ans<-1> = Str("   ",aLevel) : SubJobInfo
                        End
                     Next


                  Case JobTypeInd = 3    ; * parallel job

                     JobStart = DSGetJobInfo(hJob, DSJ.JOBSTARTTIMESTAMP)
                     JobFinish = DSGetJobInfo(hJob, DSJ.JOBLASTTIMESTAMP)
                     If Not(JobFinish)
                     Then
                        JobFinish = "(unreported)"
                        FinishEventID = DSGetNewestLogId(hJob, DSJ.LOGSTARTED)
                        FinishEvent = DSGetLogEntry(hJob, FinishEventID)
                        If FinishEvent Then JobFinish = Field(FinishEvent, "\\", 1, 1)
                     End
                     Ans = (If aLevel > 0 Then "   " Else "") : "Parallel Job " : Quote(FullJobName) : " started " : JobStart
                     Gosub DecodeJobStatus
                     If JobExitStatus <> DSJS.RUNNING
                     Then
                        Ans := ", at " : JobFinish : "."
                        LinkRowCounts = GetLinkRowCounts(FullJobName)
                        Ans := "   Records processed = "
                        Ans := Field(LinkRowCounts, "~", 1, 1) : " input, "
                        Ans := Field(LinkRowCounts, "~", 2, 1) : " output, "
                        Ans := Field(LinkRowCounts, "~", 3, 1) : " reject, "
                        Ans := Field(LinkRowCounts, "~", 4, 1) : " insert, "
                        Ans := Field(LinkRowCounts, "~", 5, 1) : " update, "
                        Ans := Field(LinkRowCounts, "~", 6, 1) : " upsert, "
                        Ans := Field(LinkRowCounts, "~", 7, 1) : " delete"
                     End

               End Case

            End

      End Case

      RETURN(Ans)

DecodeJobStatus:
      JobExitStatus = DSGetJobInfo(hJob, DSJ.JOBSTATUS)
      Begin Case
         Case JobExitStatus = DSJS.RUNNING
            Ans := "  Running"
         Case JobExitStatus = DSJS.RUNOK
            Ans := "  Finished OK"
         Case JobExitStatus = DSJS.RUNWARN
            Ans := "  Finished with warning(s)"
         Case JobExitStatus = DSJS.RUNFAILED
            Ans := "  Aborted"
         Case JobExitStatus = DSJS.VALOK
            Ans := "  Validated OK"
         Case JobExitStatus = DSJS.VALWARN
            Ans := "  Validated with warning(s)"
         Case JobExitStatus = DSJS.VALFAILED
            Ans := "  Validation failed"
         Case JobExitStatus = DSJS.RESET
            Ans := "  Has been reset"
         Case 1
            Ans := "  Status not determined."
      End Case

      RETURN(Ans)

      RETURN(Ans)

You'll also need GetLinkRowCounts() function.

Code: Select all

FUNCTION GetLinkRowCounts(aJobName)
$COPYRIGHT "Copyright (C) 2008, Ray Wurlod.  All rights reserved."
* Returns tilde-delimited list of link row counts based on link name suffix: "_IN", "_OUT", "_REJ", "_INS", "_UPD", "_UPS", "_DEL"
* Note that the routine assumes that there is only one of each.

$IFNDEF JOBCONTROL.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$ENDIF
$UNDEFINE TESTING


**********************************************************************************************************************
*     Initialise variables.
**********************************************************************************************************************

      RoutineName = "GetLinkRowCounts"
      Ans = "0~0~0~0~0~0~0"
      Temp = Convert("~", @FM, Ans)

**********************************************************************************************************************
*     Attach job whose name was given as routine argument.
**********************************************************************************************************************

      hJob = DSAttachJob(aJobName, DSJ.ERRNONE)

      ActualJobName = DSGetJobInfo(hJob, DSJ.JOBNAME)
      If ActualJobName = DSJE.BADHANDLE
      Then

         Call DSLogWarn("Could not attach job " : Quote(aJobName) : ".", RoutineName)

      End
      Else

         LinksSeen = ""                  ; * list of link names already processed

**********************************************************************************************************************
*        Obtain a list of stage names in the job and convert to dynamic array for REMOVE processing.
**********************************************************************************************************************

         StageList = DSGetJobInfo(hJob, DSJ.STAGELIST)       ; * comma-delimited list of stage names
$IFDEF TESTING
call DSLogInfo('StageList:  ': StageList ,'Debug')
$ENDIF
         Convert "," To @FM In StageList           ; * convert to dynamic array

         Loop
            Remove StageName From StageList Setting MoreStages         ; * process stages one at a time.
$IFDEF TESTING
Call DSLogInfo("Processing stage " : Quote(StageName), "Testing")
$ENDIF

**********************************************************************************************************************
*           Obtain a list of link names attached to the current stage and convert to dynamic array.
**********************************************************************************************************************

            LinkList = DSGetStageInfo(hJob, StageName, DSJ.LINKLIST)   ; * comma-delimited list of link names
$IFDEF TESTING
call DSLogInfo('StageName:  ': StageName ,'Debug')
call DSLogInfo('LinkList:  ': LinkList ,'Debug')
$ENDIF
            Convert "," To @FM In LinkList         ; * convert to dynamic array

            Loop
               Remove LinkName From LinkList Setting MoreLinks

**********************************************************************************************************************
*              Check to see whether link already processed.  If not add link to name list and process the link.
**********************************************************************************************************************

               Locate LinkName In LinksSeen By "AL" Setting Seen
               Else


                  Ins LinkName Before LinksSeen<Seen>

                  LinkNameSuffix = "_" : Field(LinkName, "_", Count(LinkName, "_") + 1, 1)
                  Begin Case

                     Case LinkNameSuffix = "_IN"   ; * Input
                        Temp<1> += DSGetLinkInfo(hJob, StageName, LinkName, DSJ.LINKROWCOUNT)

                     Case LinkNameSuffix = "_OUT"  ; * Output
                        Temp<2> += DSGetLinkInfo(hJob, StageName, LinkName, DSJ.LINKROWCOUNT)

                     Case LinkNameSuffix = "_REJ"  ; * Reject output
                        Temp<3> += DSGetLinkInfo(hJob, StageName, LinkName, DSJ.LINKROWCOUNT)

                     Case LinkNameSuffix = "_INS"  ; * Insert
                        Temp<4> += DSGetLinkInfo(hJob, StageName, LinkName, DSJ.LINKROWCOUNT)

                     Case LinkNameSuffix = "_UPD"  ; * Update
                        Temp<5> += DSGetLinkInfo(hJob, StageName, LinkName, DSJ.LINKROWCOUNT)

                     Case LinkNameSuffix = "_UPS"  ; * Upsert
                        Temp<6> += DSGetLinkInfo(hJob, StageName, LinkName, DSJ.LINKROWCOUNT)

                     Case LinkNameSuffix = "_DEL"  ; * Delete
                        Temp<7> += DSGetLinkInfo(hJob, StageName, LinkName, DSJ.LINKROWCOUNT)

                  End Case               ; * it is possible to exit this Case construct without having processed a count

               End                       ; * end of Locate statement

            While MoreLinks
            Repeat                       ; * end of Links loop


         While MoreStages
         Repeat                          ; * end of Stages loop

         Ignore = DSDetachJob(hJob)

         Ans = Convert(@FM, "~", Temp)

      End
RETURN(Ans)

kduke · Post by **kduke** » Mon Jun 22, 2015 3:52 pm

Very nice looking code.

ray.wurlod · Post by **ray.wurlod** » Fri Jan 08, 2016 7:13 pm

The result of the routine (the Ans variable) contains the list of dependent jobs. Do with this what you will. Perhaps modify the routine to write to a file as Ans is appended to.

Note, however, that this routine returns design time information - that is, jobs that MAY be called from the sequence depending on the sequence's logic.

To get the jobs that are ACTUALLY called, refer to the "summary of sequence run" in the sequence's job log. If you want to do this in a routine, use the DSGetLog... calls.