Page 1 of 1
Fetch job Stats
Posted: Thu Aug 31, 2006 8:58 am
by jayantthakore
Hii,
I need to fetch the job Start time,Job Termination Time and Number of times job ran into a db2 Table.
Can anyone tell me how to do that ??
Thanks
Posted: Thu Aug 31, 2006 9:10 am
by kduke
Do a search on EtlStats.
Posted: Thu Aug 31, 2006 6:07 pm
by ray.wurlod
What precisely do you mean by "number of times job ran". If there's a single termination time then surely this figure is always 1?
Posted: Thu Aug 31, 2006 11:35 pm
by jayantthakore
Ray,
I have figured that thing out.....
Sorry to confuse you.
You can skip the third part .
Can you tell me how to get the Start time & Termination Time ??
Posted: Fri Sep 01, 2006 12:03 am
by kumar_s
Kim has mentioned one way. You can get that from his signature link.
There are some other option from DSGetJobInfo funciton in datstage.
DSJ.JOBELAPSED
DSJ.JOBLASTTIMESTAMP
DSJ.JOBSTARTTIMESTAMP
You can also leverage the
dsjob -log
-logsum
-logdetail options for it.
Posted: Fri Sep 01, 2006 1:32 am
by kduke
Most of what I have in EtlStats is using dsjob -report XML. I can also retrieve old job runs by reading the job log directly. There is no other way to get an old run. The API only deals with the last run. There is a last run for every job instance though. If you autopurge your logs then that option is gone.
Posted: Wed Sep 13, 2006 1:57 pm
by ersunnys
kduke wrote:Most of what I have in EtlStats is using dsjob -report XML. I can also retrieve old job runs by reading the job log directly. There is no other way to get an old run. The API only deals with the last run. There is a last run for every job instance though. If you autopurge your logs then that option is gone.
can you please explain me the syntax of executing the 'dsjob -report' let my project name is abc, my host name is yyy, my userid is user and password is pass... i m new to datastage and do not know much... so it will be a great help if you can help me...
Thx
Sunny
Posted: Wed Sep 13, 2006 2:49 pm
by ray.wurlod
Welcome aboard. :D
You can find the dsjob command documented in the
Server Job Developer's Guide or the
Parallel Job Advanced Developer's Guide
If you're on the DataStage server machine, you can use
If you want to execute from somewhere else it's
Code: Select all
dsjob -host yyy -user user -password pass -report projectname jobname
Posted: Wed Sep 13, 2006 3:20 pm
by kduke
EtlStats has a job named DSJobReportDb. It is in a folder called EtlStats\ETL_Row_Counts. It has a before job ExecDOS command:
Code: Select all
#dsjobcmd# #projName# #jobName# XML >KimD\#jobName#.xml
Where in DOS
dsjobcmd="..\..\Engine\bin\dsjob -report"
If you have UNIX then this needs to change to a ExecSH command and
dsjobcmd="../DSEngine/bin/dsjob -report"
So Ray's syntax is the same except XML is added to the end of the dsjob command.
This job is executed by 2 other jobs DSJobReportDbDriverNoWait which executes DSJobReportDbDriver which executes DSJobReportDb. DSJobReportDbDriverNoWait should be the last step in your sequence. You can copy this last step out of SeqPrepareEtlStats. This sequence should setup all the metadata needed to run EtlStats after each sequence and email you the row counts of successful jobs or logs of all failed jobs. You have source code on all jobs and routines in this DSX.