DSLinkInfo and Performance Statistics

[davidnemirovsky]

I am wondering if it's possible to obtain the performance statistics (rows per sec) from all links of a job using DSLinkInfo.

I have read through the DS Documentation and can't seem to find a LinkStartTime and LinkEndTime variables that would be used to calculate the performance.

You can view the performance statistics in Designer and Director so that information must be somewhere!

Any ideas?

[ray.wurlod]

These statistics relate to active stages. More information is available through DSGetStageInfo in version 7 than in (your) version 5, for example DSJ.STAGESTARTTIMESTAMP, DSJ.STAGEENDTIMESTAMP and DSJ.STAGECPU.

You don't really want to know about how to extract them from the Repository tables, since this is not in the public domain, and can get ugly for multi-instance jobs.

Links are passive, and so do not generate statistics.

[davidnemirovsky]

Thanks Ray.

So potentially (in V7.x) if you could get the LinkRowCount (From one stage to the next), DSJ.STAGESTARTTIMESTAMP & DSJ.STAGEENDTIMESTAMP you would be able to calculate the row/sec statistic that appears in the DS Clients?

[kduke]

Please download EtlStats.zip from ADN or my tips page. There are several jobs which you can get rows per sec on one job or all jobs. The results are stored in several tables in the target data warehouse.

[davidnemirovsky]

Kim, have downloaded the zip file and it looks interesting but I am using 5.2 where I am on-site currently so therefore can't import the DSX file. Hopefully the client will eventually move to 7.5 and then can implement this type of performance statistics.

Anything I can use for 5.2?

[kduke]

Sorry but I think there have been several routines posted which will get row counts. These routines are included with DwNav. You can download a copy from my web site and email me for a free 30 day eval copy. Otherwise you need to do a search to get the code to do it in BASIC.

Code: Select all

         RunHandle = DSAttachJob(OldJobName, DSJ.ERRNONE)
         JStat = DSGetJobInfo(RunHandle, DSJ.JOBSTATUS)

Plus

Code: Select all

            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

Code: Select all

            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)

This should get you close. You will need to put the pieces together.

[davidnemirovsky]

Thanks Kim. If the client is happy with this approach and I piece the bits all together I'll add the code into this discussion.