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

[chulett]

Of course it can, as long as you have the proper permissions in place. Check with a SysAdmin to verify along the full path.

[rsunny]

hi craig ,

I gave the valid path as input and also has the access rights.

when i tried to create a file in the routine and tried to open the file in the directory i am getting an error Failed to open the directory, but the File is creating but getting an error, instead if i give directory path and file name as input parameters , it is working fine . i.e the file is already exisited and i am writing the log into file.


thanks in adavance

[kduke]

EtlStats is not an external app. It is a bunch of DataStage jobs and routines. All you need is to make the job DSJobReportDb and change it to output to a sequential file. It is a simple job. It runs dsjob -report XML to get row counts inot an XML file. It extracts 3 things from the XML file. The first is job name, start time and end time. The second is row counts. The third is parameter names and values. Delete the row counts and parameter names.

DSJobReportDbDriver runs the above job over and over for every job in the project if you use 'ALL' for the job name. Import these 2 jobs and the routines they call or rewrite the routines and you are done. DSJobReportDbDriver could easily be rewritten to run dsjob -ljobs.

[rsunny]

Hi Kim,

I have no idea about these ETLStats. Can you explain me little bit about these.

Thanks in adavnce

[kduke]

Do a search. It is a group of jobs which I give away. They are on my tips page. Click on my sig.

[kirankumarreddydesireddy]

Hi Sunny

Can you post the code you mentioned it worked......
and also can you explain with example teh arguments passed and the file path created....

Thanks
Kiran

[rsunny]

Kiran ,

the code is

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H

ErrorCode = 0


JobHandle = DSJ.ME
JobName1 = DSGetJobInfo(JobHandle,DSJ.JOBNAME)
JobStarted1 = convert(" :-","_",DSGetJobInfo(JobHandle,DSJ.JOBSTARTTIMESTAMP))
JobEnd1 = convert(" :-","_",DSGetJobInfo(JobHandle,DSJ.JOBLASTTIMESTAMP))

call DSLogInfo("Job Name: ":JobName1,"Job")
call DSLogInfo("Job Start TimeStamp : ":JobStarted1, "Job")
call DSLogInfo("Job End TimeStamp : ":JobEnd1, "Job")

DirName = Field(InputArg,",",1)
DirName2 = Field(InputArg,",",2)

StartTimeStamp = DSGetJobInfo (DSJ.ME, DSJ.JOBSTARTTIMESTAMP)
EndTimeStamp = DSGetJobInfo (DSJ.ME, DSJ.JOBLASTTIMESTAMP)

GetLogSum = DSGetLogSummary(DSJ.ME,DSJ.LOGANY,StartTimeStamp,EndTimeStamp,0)

OPENSEQ DirName:'\':DirName2 TO H.FileName THEN
WRITESEQ GetLogSum to H.FileName else
call DSLogWarn("Failed to write file ":" to directory ":DirName, "GetJobLog")
end
end
else
call DSLogWarn("Failed to open directory ":DirName, "GetJobLog")
end


CLOSESEQ H.FileName

you have to input parameters as Diectory path, filename fo eg:

C:\windows,xxx.txt

where C:\windows is the directory path and xxx.txt is the file name
but this one has to have a file ceated before you run the routine

[kirankumarreddydesireddy]

Hi Sunny

Below is the code which wries the log details into .txt file

The input argument is only the directory name.Here no need to create the file before.

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H 

ErrorCode = 0 

DirName = InputArg 

Suffix = ".txt" 

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

FileName = JobName:"_":JobStarted 
FileName := Suffix 

call DSLogInfo("Job Name: ":JobName,"Job") 
call DSLogInfo("Job Start TimeStamp : ":JobStarted, "Job") 
call DSLogInfo("Job End TimeStamp : ":JobEnd, "Job")

StartTimeStamp = DSGetJobInfo (JobHandle, DSJ.JOBSTARTTIMESTAMP) 
EndTimeStamp = DSGetJobInfo (JobHandle, DSJ.JOBLASTTIMESTAMP) 

GetLogSum = DSGetLogSummary(JobHandle,DSJ.LOGANY,StartTimeStamp,EndTimeStamp,0) 

Openpath DirName to t.fvar then 
write GetLogSum to t.fvar, FileName else 
call DSLogWarn("Failed to write file ":FileName:" to directory ":DirName, "GetJobLog") 
end 
end 
else 
call DSLogWarn("Failed to open directory ":DirName, "GetJobLog") 
end 

close t.fvar

[rsunny]

Hi,

Is there anyway that we can use the above code in Sequence job or do we need to use this only in individual job. If i can use it in sequence how can we use it in sequence as there is no option called after job subroutine.

And can anyone tell me if i want to delete the file which is created from the above routine after the job has executed , is there anyway that we can add some code to check if the job has no errors then to delete the file automatically.

Thanks in advance

[rsunny]

Hi ,

Is there anyway that we can log to file when the job is aborted only with the above routine that is mentioned above as i need to log to file only when the job is aborted.

Any help is really appreciated

Thanks in advance

[chulett]

After Job you need to check the JOBINTERIMSTATUS first and only execute the rest of the code if it says the job is in the process of aborting. Or trigger a Routine Activity with this code in a Sequence after the job aborts.

[rsunny]

Hi ray,

Do we need to use JOBINTERIMSTATUS in the routine which is above. If so can you tell me where to use it and i tried using routine activity as it is inbuilt routine , there is nothing written into the log file except '-13'

thanks in advance

[chulett]

Examine the dsapi.h header file in $DSHOME/include for a complete list of codes of that nature.

[rsunny]

Hi ,

I have used x=DSGetJobInfo(DSJ.ME, DSJ.JOBSTATUS)

IF x=DSJS.RUNFAILED then

"Routine i used"

else

""

but it didnt work for me

Thanks in advance

[chulett]

You have to check the INTERIM status as suggested, otherwise all it's ever going to tell you after job is that the job is RUNNING.