trobinson
I think there are some flaws in your thought process. First of all the first row comes through after the select is done. You lost that time. How do you know when a link finishes? If you are using the start time of the next link then that time will include its time that it took to do the select. The times that are in the DataStage Director when you monitor a job are the best times. What I do is run a process after a job finishes that writes parameter history, row history, job history to six hash files. You run a TCL command like:
DWN_RT_ROW_COUNT.uvb JobName
This program is included with DsWebMon. Ken Bland had a acticle about profiling DataStage processes to get performance trends on jobs. If a job took 6 hours to run then why all of a sudden does it take 12 hours. If I am doing performance tuning then what are your slowest running jobs in rows per second? What are your longest running jobs? I run this program after each job sequence runs after I start to get an idea of what jobs to try to optimize.
I would like to share this code but I may need Ascential's approval because it shows how the internal files of DataStage work. It is very complex at a low level and their routines do not give you the same results. Like:
StartedDateTime = DSGetJobInfo(RunHandle, DSJ.JOBSTARTTIMESTAMP)
iStartedDate = iconv(StartedDateTime[1,10], 'D4-YMD')
iStartedTime = iconv(StartedDateTime[12,8], 'MT')
Plus:
StageNames = DSGetJobInfo(RunHandle, DSJ.STAGELIST)
NoStages = dcount(StageNames, ',')
for i=1 to NoStages
StageName = field(StageNames, ',', i)
* ------------------------------------------------------------------
* 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)
RowCount = DSGetLinkInfo(RunHandle, StageName, LinkName, DSJ.LINKROWCOUNT)
if ElapsedTime>0 and ElapsedTime'' then
RowsPerSec = (RowCount / ElapsedTime)
RowsPerSec = oconv(RowsPerSec, 'MD0')
end
This code will get you closer to your desired result. You need to fill the gaps. Unless someone from Ascential like Ernie says I can show you the real way.
Thanks Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com