How to store Job start time,end time,job name in Oracle

[kirankumarreddydesireddy]

Hi,

After completing of my job execution,how to store the job name,job start date and time and end date and time in to oracle.

If it is in unix environment,I know to get this information using dsjob - report functionality

But we are using Windows environment.Can anyone please suggest me.

Thanks
Kiran

[Lovesh_Patni]

For job name, start date and start time you can call DataStage macros DSJobName, DSJobStartDate and DSJobStartTime in a transformer stage and pass them to Oracle stage to be stored. Not sure for end date and end time.

[kirankumarreddydesireddy]

Yes.but how to get end date and time?




Thanks
Kiran

[ray.wurlod]

Download ETLstats (for free) from Kim Duke's website - it will do all that and more.

[kirankumarreddydesireddy]

Thanks Ray

Since our environment is highly secured,we should'nt dowload any external applications.

Can we get this info by writing a rouitne and calling this after the job is executed

Do anyone have this routine ready with them?

Thanks
Kiran

[jhmckeever]

A simple solution would be an after-job routine which used DSGetJobInfo to get the information you require. You could then fire this at Oracle using your preferred method.

[ray.wurlod]

Probably, but you're not allowed to download any external applications.

You will need DSGetJobInfo() function. The InfoType argument for end date and time is DSJ.JOBLASTTIMESTAMP

[Shruthi]

Here is the after job routine that would display job name, job start timestamp and job end timestamp in log

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H

JobHandle = DSJ.ME
JobName = DSGetJobInfo(JobHandle,DSJ.JOBNAME)
JobStarted = convert(" :-","_",DSGetJobInfo(JobHandle,DSJ.JOBSTARTTIMESTAMP))
JobEnd = convert(" :-","_",DSGetJobInfo(JobHandle,DSJ.JOBLASTTIMESTAMP))

call DSLogInfo("Job Name: ":JobName,"Job")
call DSLogInfo("Job Start TimeStamp : ":JobStartTS, "Job")
call DSLogInfo("Job End TimeStamp : ":JobEndTS, "Job")

[ray.wurlod]

Apart, of course, from the mismatched variable names.

[Shruthi]

Thats right!

JobStartTS should be JobStarted
And JobEndTS should be JobEnd

Hope you got it Kiran

[kirankumarreddydesireddy]

Thanks Shruthi.I have included this after job subroutine.can you please let me know what is the "input value" i need to pass in the Job properties tab.

Thanks
Kiran

[Shruthi]

Neednt pass anything. Leave it as default

[kirankumarreddydesireddy]

Thanks.I have got the info in log.how can insert that in oracle table?

I hava a routine which does this.but I am not sure the arguments i need to pass to this routine.

Can anyone let me know on this


$INCLUDE DSINCLUDE JOBCONTROL.H

Equate RoutineName To 'GetJobInfo'

Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"

JobHandle = ''

JobHandle = DSAttachJob(JobName, DSJ.ERRNONE)

* Message = DSRMessage('DSTAGE_TRX_I_0014', 'Attaching job for processing - %1 - Status of Attachment = %2', JobName:@FM:JobHandle )

* Call DSLogInfo(Message, RoutineName)


Int = DSGetJobInfo(JobHandle, DSJ.JOBSTATUS)

Begin Case
Case Int = DSJS.RUNFAILED
Info = 'Aborted'
Case Int = DSJS.RUNOK
Info = 'Completed'
Case Int = DSJS.RUNWARN
Info = 'Warning'
Case Int = DSJS.RUNNING
Count = DSGetJobInfo(JobHandle, DSJ.DSJ.JOBEOTCOUNT)
Info = 'Running' : 'Count of Rows Complete' : Count
Case @True ; * all other values
Info = 'Not Defined'
End Case

StartTime = DSGetJobInfo(JobHandle, DSJ.JOBSTARTTIMESTAMP)
EndTime = DSGetJobInfo(JobHandle, DSJ.JOBLASTTIMESTAMP)

ErrCode = DSDetachJob(JobHandle)

Ans = Info : '|' : StartTime : '|' : EndTime



Thanks
Kiran

[chulett]

You are (IMHO) way better off gathering this information in a separate step rather than plugging up your 'after job' area in every job. Build something that sweeps the information from each job after all jobs have run and which loads that information to a flat file. After it completes, a simple load job can pick that file up and get it into your Oracle table.

I personally would never try to load anything to Oracle from a routine.

[rsunny]

Hi ,

The end date and time can be get with the function

Oconv(Date(), "D-YMD[4,2,2]") : " " : Oconv(Time(), "MTS")

Thanks