Routine

[arun_im4u]

Hi,

I have an after-job routine that captures the row count stats in a job based on the link names (SRC, TGT & REJ). The routine works for most cases, but for some types of jobs (Jobs that have a seq file as a target) the row counts are doubled. I tried my best not to calculate row counts for links that have already been calculated but it doesnt seem to work. I have pasted my code below. If someone can point out what is wrong or any new ideas it would be great.

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H

      Equate pJobName to "EtlAfterJob"

      JobHandle = ''
      Stage = ''
      JobName = DSGetJobInfo(DSJ.ME,DSJ.JOBNAME)
      AfterJob = ''
      AfterJob = "EtlAfterJob" :'.':Arg1:'_': JobName

      JobHandle = DSAttachJob(AfterJob, DSJ.ERRNONE)

      StageList = DSGetJobInfo(DSJ.ME, DSJ.STAGELIST)
      StageCount = DCount(StageList, ",")
      Src = 0
      Total = 0
      Rej = 0
      For Count = 1 to StageCount
         StageName = Field(StageList, ",", Count)
         LinkList = DSGetStageInfo(DSJ.ME, StageName, DSJ.LINKLIST)
         LinkCount = DCount(LinkList, ",")
            For LCount = 1 to LinkCount
              LinkName = Field(LinkList, ",", LCount)
              LinkRowCount = DSGetLinkInfo(DSJ.ME, StageName, LinkName , DSJ.LINKROWCOUNT)
              If (Index(LinkName, Stage, 1)=0 and Index(LinkName, 'TGT',1)<>0)
               then Total = Total + LinkRowCount
               Else if (Index(LinkName, Stage, 1)=0 and Index(LinkName, 'SRC',1)<>0)
               then Src = Src + LinkRowCount
               Else if (Index(LinkName, Stage, 1)=0 and Index(LinkName, 'REJ',1)<>0) then Rej = Rej + LinkRowCount else 0
                    Stage = Stage :'-': LinkName
                                        
             Next LCount

      Next Count

      ErrCode1 = DSSetParam(JobHandle, "SrcRowCount", Src)
      ErrCode1 = DSSetParam(JobHandle, "OutRowCount", Total)
      ErrCode1 = DSSetParam(JobHandle, "RejRowCount", Rej)
      ErrCode1 = DSRunJob(JobHandle, DSJ.RUNNORMAL)


      ErrorCode = 0                      ; * set this to non-zero to stop the stage/job

[kduke]

Why are you

Code: Select all

JobHandle = DSAttachJob(AfterJob, DSJ.ERRNONE) 

I would do this at the end. You are still attached to DSJ.ME. I have had issues attaching to multiple jobs.

As a comment, I hate this way of getting row counts. IBM consults do this a lot. First of all you slow down your job stream by getting stats on the job currently running. I would do this in the background on a job following the job running. That way you do not slow down your job stream. We find that in run EtlStats that it adds one to two minutes per job. If you run hundreds of jobs this adds up.

[ray.wurlod]

Keep a dynamic array of link names that you have processed and only process if the name is not already on the list. This approach will not be appropriate if your job has duplicate link names.

Code: Select all

* Initialization
LinksProcessed = ""

* Have we seen this link already (it connects two active stages)?
Locate LinkName In LinksProcessed By "AL" Setting Pos
Then
   * Link already processed - do nothing
   NULL
End
Else
   Ins LinkName Before LinksProcessed<Pos>
   LinkRowCount = DSGetLinkInfo(...
End