How to find the job run times?

vij · Post by **vij** » Thu Jun 18, 2009 12:23 pm

Hi,

I would like to find out the list of datastage jobs that run before 6 months from the current date. I can hardcode the date/time value..

is there a way I can use some where clause in the below SQL in the adminstrator tool to get that information?

Eg., SELECT COUNT(*) FROM DS_JOBS

please let me know.

Thanks in advance,
Vij

vij · Post by **vij** » Thu Jun 18, 2009 1:01 pm

Is it possible to get this from dsjob command? and also I am not sure if I am in the correct path in unix to execute the dsjob command.

I tried the dsjob -logsum command, but I got the below error message:

ksh: dsjob: not found

chulett · Post by **chulett** » Thu Jun 18, 2009 1:38 pm

So... you're trying to identify jobs that have not run in the last six months?

That information won't be in DS_JOBS but rather in each individual job's RT_STATUSnnn hashed file, I believe. And it is not something that is openly documented but certain people here probably know exactly where that would be.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 18, 2009 4:57 pm

Not RT_CONFIGnnn, but either of RT_LOGnnn or RT_STATUSnnn.

Depends how you want to go about it. You can use the DataStage API (from DataStage or from an external C program), or you can use shell script using dsjob commands.

chulett · Post by **chulett** » Thu Jun 18, 2009 5:04 pm

Not sure where the RT_CONFIGnnn mention came from, but good point about the two sources - RT_STATUSnnn for the most recent information (which seems to be all that is needed here) and RT_LOGnnn for runs as far back as you have logs.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 18, 2009 5:36 pm

chulett wrote:Not sure where the RT_CONFIGnnn mention came from.

(off line)

kduke · Post by **kduke** » Thu Jun 18, 2009 11:33 pm

Download EtlStats and get row counts for all jobs in a project. There is a job to load ETL_JOB table. You can do an other join fromETL_JOB to ETL_JOB_HIST to get your list. If you want a more complete list there is a job to get row counts from log files. Run it too. This will get row counts on job which have been compiled but the log has not been cleared recently.

Do a search on EtlStats on what it is and how to install it.