How to store Job start time,end time,job name in Oracle
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 110
- Joined: Mon Jan 11, 2010 4:22 am
How to store Job start time,end time,job name in Oracle
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
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
-
- Premium Member
- Posts: 13
- Joined: Fri Jun 22, 2007 1:29 am
- Location: Perth
-
- Participant
- Posts: 110
- Joined: Mon Jan 11, 2010 4:22 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Download ETLstats (for free) from Kim Duke's website - it will do all that and more.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 110
- Joined: Mon Jan 11, 2010 4:22 am
-
- Premium Member
- Posts: 301
- Joined: Thu Jul 14, 2005 10:27 am
- Location: Melbourne, Australia
- Contact:
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.
<b>John McKeever</b>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
You will need DSGetJobInfo() function. The InfoType argument for end date and time is DSJ.JOBLASTTIMESTAMP
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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")
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 110
- Joined: Mon Jan 11, 2010 4:22 am
-
- Participant
- Posts: 110
- Joined: Mon Jan 11, 2010 4:22 am
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
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
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.
I personally would never try to load anything to Oracle from a routine.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers