Fetch job Stats

jayantthakore · Post by **jayantthakore** » Thu Aug 31, 2006 8:58 am

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

kduke · Post by **kduke** » Thu Aug 31, 2006 9:10 am

Do a search on EtlStats.

ray.wurlod · Post by **ray.wurlod** » Thu Aug 31, 2006 6:07 pm

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?

jayantthakore · Post by **jayantthakore** » Thu Aug 31, 2006 11:35 pm

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 ??

kumar_s · Post by **kumar_s** » Fri Sep 01, 2006 12:03 am

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.

kduke · Post by **kduke** » Fri Sep 01, 2006 1:32 am

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.

ersunnys · Post by **ersunnys** » Wed Sep 13, 2006 1:57 pm

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

ray.wurlod · Post by **ray.wurlod** » Wed Sep 13, 2006 2:49 pm

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

Code: Select all

dsjob -report projectname jobname

If you want to execute from somewhere else it's

Code: Select all

dsjob -host yyy -user user -password pass -report projectname jobname

kduke · Post by **kduke** » Wed Sep 13, 2006 3:20 pm

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.