Find the "Last_Run_Time" and insert it in a table

Amit_111 · Post by **Amit_111** » Thu Sep 04, 2008 2:31 am

Hi All,

My requirement is to find the Start time, End time of a job and then insert it in an oracle table.
Please can any one help me to know the different options i can try on because even if i take the start time through a Basic function how will i pass that value to insert it in a Database?
Also one more thing...Can I execute a Oracle script through an after job routine?
Thanks in Advance.

ray.wurlod · Post by **ray.wurlod** » Thu Sep 04, 2008 2:51 am

Find the run times by invoking DSGetJobInfo() or equivalent DSMacros. You can do this in a Transformer stage triggered by, say, a SELECT against the DUAL table.

The answer to your second question is yes, but not directly - after all, an Oracle script is not a DataStage after-job subroutine. The after-job subroutine has to invoke the Oracle script appropriately, either via sqlplus or through BCI functions.

Amit_111 · Post by **Amit_111** » Thu Sep 04, 2008 3:08 am

Actually I have a Job which is currently executing and immediately once it finishes I want to get its start time and End time and insert it in a table....The start time i can get but the end time will be given only after the job is finished. I am not able to get this end time...

sachin1 · Post by **sachin1** » Thu Sep 04, 2008 3:28 am

one doubt i have is that do you want to track information even your job fails or their is some error...............as solution differs in that case as you need to track your job first for successful completion

Amit_111 · Post by **Amit_111** » Thu Sep 04, 2008 3:51 am

I want to insert the end_time irrespective of the job is a success or is aborted.

sachin1 · Post by **sachin1** » Thu Sep 04, 2008 3:54 am

you create a custom routine(after job subroutine), in routine use the DSGetJobInfo Function and get the value for DSJ.JOBSTARTTIMESTAMP infotype (this is the start time of your job) and get one value like JobEndTime = DSRTimestamp() in routine which is the end time of your job, then use DSExecute Function to insert value in database.

chulett · Post by **chulett** » Thu Sep 04, 2008 7:12 am

Note that there is also an InfoType of DSJ.JOBLASTTIMESTAMP to get the time the job finished.

ray.wurlod · Post by **ray.wurlod** » Thu Sep 04, 2008 7:18 am

What's the value of DSJ.JOBLASTTIMESTAMP in an after-job subroutine?

chulett · Post by **chulett** » Thu Sep 04, 2008 7:27 am

Trick question.

Never said you could use it 'after job', just that it exists as an InfoType. What's the value of any attempt to find out when a job finished before it actually finishes?

ray.wurlod · Post by **ray.wurlod** » Thu Sep 04, 2008 7:32 am

For someone sufficiently pedantic, the correct answer is 11.

kduke · Post by **kduke** » Thu Sep 04, 2008 8:38 pm

Search for EtlStats.