How to capture the total no. of rows passed through a link

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
sun786
Participant
Posts: 34
Joined: Mon Feb 07, 2005 5:48 am

How to capture the total no. of rows passed through a link

Post by sun786 »

I need to capture the total number of rows that pass through any link in a job . For eg. 1. The number of rows from Input to transformer 2. The number of rows from Transformer to ouput1. 3. The number of rows from Transformer to ouput2. and so on.
I have tried with DSGetLinkInfo.
Thanks
sun
Top
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:
Contact kduke

Post by kduke »

Download EtlStats.zip from my tips page.
Mamu Kim
Top
sun786
Participant
Posts: 34
Joined: Mon Feb 07, 2005 5:48 am

Post by sun786 »

Hi, I just need to count the the total number of rows passed through any link....this should be done in the transformer stage and the output to another seq file.
let me know if more input is needed.
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I'm curious why this "should be done in the transformer"? Do you really want to collect these stats every time a row comes through your job?

This is typically done after job. If you download Kim's code, you'll see how it can be accomplished. Also, you've said you tried using DSGetLinkInfo... how exactly did you try it? What errors did you get?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
Sainath.Srinivasan
Participant
Posts: 3337
Joined: Mon Jan 17, 2005 4:49 am
Location: United Kingdom

Post by Sainath.Srinivasan »

In that case, maybe you need a simple @OUTROWNUM sent to an agg stage with a max() clause and then written to your output file !!
Top
supernova2005
Participant
Posts: 37
Joined: Mon Jan 24, 2005 10:12 am

Re: How to capture the total no. of rows passed through a li

Post by supernova2005 »

Can you do it on the Unix level? Just use wc -l filename to count the total number of lines on the input file.


sun786 wrote:I need to capture the total number of rows that pass through any link in a job . For eg. 1. The number of rows from Input to transformer 2. The number of rows from Transformer to ouput1. 3. The number of rows from Transformer to ouput2. and so on.
I have tried with DSGetLinkInfo.
Thanks
sun
Top
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:
Contact kduke

Post by kduke »

This is how you call DSGetStageInfo. This code is a part of DsWebMon.

Code: Select all

      subroutine DwnGetMonitor(JobName, MonitorGrid)
* -----------------------------------------------------------------------
* Program Name              : DwnGetMonitor
* Program Dir               : DWNBP
* Created by                : Kim Greggory Duke
* Copyright (C)             : Duke Consulting
* Programmer                : kgd
* Date Created              : 12-01-2002
* -----------------------------------------------------------------------
* Last Modified             : 07-16-2003
* -----------------------------------------------------------------------
* Language                  : UniVerse BASIC
* -----------------------------------------------------------------------
* Usage                     : DwnGetMonitor(JobName, MonitorGrid)
* -----------------------------------------------------------------------
* Notes                     :
* -----------------------------------------------------------------------
* initialize standard variables
* ----------------------------------------------------------------------
      ProgId = 'DwnGetMonitor'
$INCLUDE DSINCLUDE JOBCONTROL.H
$include DwnVersion
* ----------------------------------------------------------------------
      MonitorGrid = ''
* -----------------------------------------------------------------------
      open 'DS_JOBS' to DsJobs else goto TheEnd
* -----------------------------------------------------------------------
      JobStatus = ''
* -----------------------------------------------------------------------
      OldJobName = JobName
      if index(JobName, '.', 1) > 0 then
        MultipleInstace = @true
        InvocationId = field(JobName, '.', 2, 9999)
        JobName = field(JobName, '.', 1)
      end else
        MultipleInstace = @false
        InvocationId = ''
      end
      read JobRec from DsJobs, JobName then
         JobNo = JobRec<5>
         * print 'DSAttachJob(':JobName:',3)'
         RunHandle = DSAttachJob(OldJobName, DSJ.ERRNONE)
         JStat = DSGetJobInfo(RunHandle, DSJ.JOBSTATUS)
         ContinueFlag = @true
         CanRunFlag = 1
         CanStopFlag = 0
         CanResetFlag = 1
* -----------------------------------------------------------------------
         begin case
            case JStat = 0
               JobStatus = 'Running'
               CanRunFlag = 0
               CanResetFlag = 0
               CanStopFlag = 1
            case JStat = 1
               JobStatus = 'Finished'
            case JStat = 2               ; * Finished with warnings
               JobStatus = 'Finished'
            case JStat = 3
               ContinueFlag = @false
               JobStatus = 'Aborted'
               CanRunFlag = 0
            case JStat = 11
               JobStatus = 'Finished'
            case JStat = 12              ; * Validated with warnings
               JobStatus = 'Finished'
            case JStat = 13
               * ContinueFlag = @false
               JobStatus = 'Finished'
            case JStat = 21
               JobStatus = 'Finished'
            case JStat = 96
               ContinueFlag = @false
               JobStatus = 'Aborted'
               CanRunFlag = 0
            case JStat = 97
               * ContinueFlag = @false
               JobStatus = 'Stopped'
               CanRunFlag = 0
            case JStat = 98
               JobStatus = 'Finished'
            case JStat = 99
               JobStatus = 'Finished'
            case @true
               ContinueFlag = @false
               JobStatus = 'Not Compiled'
               CanRunFlag = 0
               CanResetFlag = 0
         end case
* -----------------------------------------------------------------------
         if ContinueFlag then
            StageName = ''
            StageStatus = ''
            LinkName = ''
            LinkType =''
            RowCount = ''
            StartedTime = ''
            ElapsedTime = ''
            RowsPerSec = ''
            PercentCP = ''
* -----------------------------------------------------------------------
            StartedDateTime = DSGetJobInfo(RunHandle, DSJ.JOBSTARTTIMESTAMP)
            iStartedDate = iconv(StartedDateTime[1,10], 'D4-YMD')
            iStartedTime = iconv(StartedDateTime[12,8], 'MT')

            if JobStatus = 'Running' then
               FinishedDateTime = oconv(date(), 'D4-YMD'):' ':oconv(time(), 'MTS')
            end else
               FinishedDateTime = DSGetJobInfo(RunHandle, DSJ.JOBLASTTIMESTAMP)
            end
            iFinishedDate = iconv(FinishedDateTime[1,10], 'D4-YMD')
            iFinishedTime = iconv(FinishedDateTime[12,8], 'MT')
            
            * print iFinishedTime:' - ':iStartedTime
            if FinishedDateTime<>'' and StartedDateTime<>'' and iFinishedTime<>'' and iStartedTime<>'' then
               ElapsedTime = ((iFinishedDate-iStartedDate)*60*60)+iFinishedTime-iStartedTime
            end
            if ElapsedTime<0 then
               ElapsedTime = ''
            end
* -----------------------------------------------------------------------
* Get stage names
* -----------------------------------------------------------------------
            Cnt = 0
            StageNames = DSGetJobInfo(RunHandle, DSJ.STAGELIST)
            NoStages = dcount(StageNames, ',')
            for i=1 to NoStages
               StageName = field(StageNames, ',', i)
               * print i:'. ':StageName
* -----------------------------------------------------------------------
* Get link names per stage
* -----------------------------------------------------------------------
               LinkType = DSGetStageInfo(RunHandle, StageName, DSJ.STAGETYPE)
               LinkNames = DSGetStageInfo(RunHandle, StageName, DSJ.LINKLIST)
               NoLinks = dcount(LinkNames, ',')
               for j=1 to NoLinks
                  LinkName = field(LinkNames, ',', j)
                  * print j:'. ':LinkName
* -----------------------------------------------------------------------
                  RowCount = DSGetLinkInfo(RunHandle, StageName, LinkName, DSJ.LINKROWCOUNT)
                  if ElapsedTime>0 and ElapsedTime<>'' then
                     RowsPerSec = (RowCount / ElapsedTime)
                     RowsPerSec = oconv(RowsPerSec, 'MD0')
                  end
* -----------------------------------------------------------------------
                  Cnt += 1
                  if j=1 then
                     MonitorGrid < 1, Cnt> = StageName
                     MonitorGrid < 2, Cnt> = JobStatus
                  end else
                     MonitorGrid < 1, Cnt> = ' '
                     MonitorGrid < 2, Cnt> = ' '
                  end
                  MonitorGrid < 3, Cnt> = LinkName
                  MonitorGrid < 4, Cnt> = LinkType
                  MonitorGrid < 5, Cnt> = RowCount
                  MonitorGrid < 6, Cnt> = StartedDateTime
                  MonitorGrid < 7, Cnt> = ElapsedTime
                  MonitorGrid < 8, Cnt> = RowsPerSec
                  MonitorGrid < 9, Cnt> = PercentCP
                  MonitorGrid <10, Cnt> = CanRunFlag
                  MonitorGrid <11, Cnt> = CanStopFlag
                  MonitorGrid <12, Cnt> = CanResetFlag
                  MonitorGrid <13, Cnt> = FinishedDateTime
               next j
            next i
         end
         JStat = DSDetachJob(RunHandle)
      end
* -----------------------------------------------------------------------
TheEnd:
         return
Mamu Kim
Top
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:
Contact kduke

Post by kduke »

You will have to do some work to convert it to a routine but it will get you close.
Mamu Kim
Top
Titto
Participant
Posts: 148
Joined: Tue Jun 21, 2005 7:49 am

Post by Titto »

kduke wrote:This is how you call DSGetStageInfo. This code is a part of DsWebMon.
Hi Kim,

could you please guide me to write the MonitorGrid to a Flat file by Stage name, LinkName and other details

Thanks in advance

Code: Select all

      subroutine DwnGetMonitor(JobName, MonitorGrid)
* -----------------------------------------------------------------------
* Program Name              : DwnGetMonitor
* Program Dir               : DWNBP
* Created by                : Kim Greggory Duke
* Copyright (C)             : Duke Consulting
* Programmer                : kgd
* Date Created              : 12-01-2002
* -----------------------------------------------------------------------
* Last Modified             : 07-16-2003
* -----------------------------------------------------------------------
* Language                  : UniVerse BASIC
* -----------------------------------------------------------------------
* Usage                     : DwnGetMonitor(JobName, MonitorGrid)
* -----------------------------------------------------------------------
* Notes                     :
* -----------------------------------------------------------------------
* initialize standard variables
* ----------------------------------------------------------------------
      ProgId = 'DwnGetMonitor'
$INCLUDE DSINCLUDE JOBCONTROL.H
$include DwnVersion
* ----------------------------------------------------------------------
      MonitorGrid = ''
* -----------------------------------------------------------------------
      open 'DS_JOBS' to DsJobs else goto TheEnd
* -----------------------------------------------------------------------
      JobStatus = ''
* -----------------------------------------------------------------------
      OldJobName = JobName
      if index(JobName, '.', 1) > 0 then
        MultipleInstace = @true
        InvocationId = field(JobName, '.', 2, 9999)
        JobName = field(JobName, '.', 1)
      end else
        MultipleInstace = @false
        InvocationId = ''
      end
      read JobRec from DsJobs, JobName then
         JobNo = JobRec<5>
         * print 'DSAttachJob(':JobName:',3)'
         RunHandle = DSAttachJob(OldJobName, DSJ.ERRNONE)
         JStat = DSGetJobInfo(RunHandle, DSJ.JOBSTATUS)
         ContinueFlag = @true
         CanRunFlag = 1
         CanStopFlag = 0
         CanResetFlag = 1
* -----------------------------------------------------------------------
         begin case
            case JStat = 0
               JobStatus = 'Running'
               CanRunFlag = 0
               CanResetFlag = 0
               CanStopFlag = 1
            case JStat = 1
               JobStatus = 'Finished'
            case JStat = 2               ; * Finished with warnings
               JobStatus = 'Finished'
            case JStat = 3
               ContinueFlag = @false
               JobStatus = 'Aborted'
               CanRunFlag = 0
            case JStat = 11
               JobStatus = 'Finished'
            case JStat = 12              ; * Validated with warnings
               JobStatus = 'Finished'
            case JStat = 13
               * ContinueFlag = @false
               JobStatus = 'Finished'
            case JStat = 21
               JobStatus = 'Finished'
            case JStat = 96
               ContinueFlag = @false
               JobStatus = 'Aborted'
               CanRunFlag = 0
            case JStat = 97
               * ContinueFlag = @false
               JobStatus = 'Stopped'
               CanRunFlag = 0
            case JStat = 98
               JobStatus = 'Finished'
            case JStat = 99
               JobStatus = 'Finished'
            case @true
               ContinueFlag = @false
               JobStatus = 'Not Compiled'
               CanRunFlag = 0
               CanResetFlag = 0
         end case
* -----------------------------------------------------------------------
         if ContinueFlag then
            StageName = ''
            StageStatus = ''
            LinkName = ''
            LinkType =''
            RowCount = ''
            StartedTime = ''
            ElapsedTime = ''
            RowsPerSec = ''
            PercentCP = ''
* -----------------------------------------------------------------------
            StartedDateTime = DSGetJobInfo(RunHandle, DSJ.JOBSTARTTIMESTAMP)
            iStartedDate = iconv(StartedDateTime[1,10], 'D4-YMD')
            iStartedTime = iconv(StartedDateTime[12,8], 'MT')

            if JobStatus = 'Running' then
               FinishedDateTime = oconv(date(), 'D4-YMD'):' ':oconv(time(), 'MTS')
            end else
               FinishedDateTime = DSGetJobInfo(RunHandle, DSJ.JOBLASTTIMESTAMP)
            end
            iFinishedDate = iconv(FinishedDateTime[1,10], 'D4-YMD')
            iFinishedTime = iconv(FinishedDateTime[12,8], 'MT')
            
            * print iFinishedTime:' - ':iStartedTime
            if FinishedDateTime<>'' and StartedDateTime<>'' and iFinishedTime<>'' and iStartedTime<>'' then
               ElapsedTime = ((iFinishedDate-iStartedDate)*60*60)+iFinishedTime-iStartedTime
            end
            if ElapsedTime<0 then
               ElapsedTime = ''
            end
* -----------------------------------------------------------------------
* Get stage names
* -----------------------------------------------------------------------
            Cnt = 0
            StageNames = DSGetJobInfo(RunHandle, DSJ.STAGELIST)
            NoStages = dcount(StageNames, ',')
            for i=1 to NoStages
               StageName = field(StageNames, ',', i)
               * print i:'. ':StageName
* -----------------------------------------------------------------------
* Get link names per stage
* -----------------------------------------------------------------------
               LinkType = DSGetStageInfo(RunHandle, StageName, DSJ.STAGETYPE)
               LinkNames = DSGetStageInfo(RunHandle, StageName, DSJ.LINKLIST)
               NoLinks = dcount(LinkNames, ',')
               for j=1 to NoLinks
                  LinkName = field(LinkNames, ',', j)
                  * print j:'. ':LinkName
* -----------------------------------------------------------------------
                  RowCount = DSGetLinkInfo(RunHandle, StageName, LinkName, DSJ.LINKROWCOUNT)
                  if ElapsedTime>0 and ElapsedTime<>'' then
                     RowsPerSec = (RowCount / ElapsedTime)
                     RowsPerSec = oconv(RowsPerSec, 'MD0')
                  end
* -----------------------------------------------------------------------
                  Cnt += 1
                  if j=1 then
                     MonitorGrid < 1, Cnt> = StageName
                     MonitorGrid < 2, Cnt> = JobStatus
                  end else
                     MonitorGrid < 1, Cnt> = ' '
                     MonitorGrid < 2, Cnt> = ' '
                  end
                  MonitorGrid < 3, Cnt> = LinkName
                  MonitorGrid < 4, Cnt> = LinkType
                  MonitorGrid < 5, Cnt> = RowCount
                  MonitorGrid < 6, Cnt> = StartedDateTime
                  MonitorGrid < 7, Cnt> = ElapsedTime
                  MonitorGrid < 8, Cnt> = RowsPerSec
                  MonitorGrid < 9, Cnt> = PercentCP
                  MonitorGrid <10, Cnt> = CanRunFlag
                  MonitorGrid <11, Cnt> = CanStopFlag
                  MonitorGrid <12, Cnt> = CanResetFlag
                  MonitorGrid <13, Cnt> = FinishedDateTime
               next j
            next i
         end
         JStat = DSDetachJob(RunHandle)
      end
* -----------------------------------------------------------------------
TheEnd:
         return
Top
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:
Contact kduke

Post by kduke »

You should edit your post and ask a question.
Mamu Kim
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Actually, they did. Never understood why people felt the need to quote entire messages for no good reason and then compound the problem by burying their response inside the quote.
Titto wrote:Hi Kim,

could you please guide me to write the MonitorGrid to a Flat file by Stage name, LinkName and other details

Thanks in advance
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:
Contact kduke

Post by kduke »

Thanks Craig. I never saw the question.

You need to probably change this into a job. All this code would go in job control code. You need to openseq, writeseq for each line then closeseq. Ray has posted a lot of code to show you how.
Mamu Kim
Top
Post Reply

Return to “IBM<sup>®</sup> Infosphere DataStage Server Edition”