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

ray.wurlod · Post by **ray.wurlod** » Thu Feb 03, 2011 3:09 pm

You can use BCI functions from a routine to load data into Oracle.

Search DSXchange for examples.

kirankumarreddydesireddy · Fri Feb 04, 2011 3:33 am

Thanks.Oconv(Date(), "D-YMD[4,2,2]") : " " : Oconv(Time(), "MTS")
is not giving the correct timestamp when the job is ended,but it is giving exactly the time when transformer stage ended.

As earlier told by Shruthi

we have after job subroutine that would write this information in log....Can anyone suggest how to edit this routine so that it would write to a sequential file.

The routine is

$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")

Thanks
Kiran

chulett · Post by **chulett** » Fri Feb 04, 2011 8:03 am

Echo the string and redirect that to your file of choice. Do that using the DSExecute() function. That or search here or in your docs for the Sequential File handling functions to OPENSEQ, WRITESEQ and CLOSESEQ a sequential file but the first way would be less... difficult.

Here is Ray's all encompassing example of the latter technique for the Premium folks in the audience, otherwise search for the keywords noted above.

viewtopic.php?t=95306

rsunny · Post by **rsunny** » Fri Feb 04, 2011 9:43 am

Hi craig ,

I tried using this routine which was in dsxchnage forums to write the log into file.

the code is

Code: Select all

ErrorCode = 0      ;* set this to non-zero to stop the stage/job

$INCLUDE DSINCLUDE JOBCONTROL.H

DirName = InputArg

Suffix = ".txt"

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

FileName = JobName:"_":JobStarted:Suffix

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

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



OpenSeq FileName to FileVar then
write GetLogSum to FileVar,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

Ans=FileName
close FileVar

when i compile run and, i got an error saying Failed to open directory

so can you tell me what might be the issue and when i use writeSeq instead of write and when i compile the routine, i got an eror which is

writeSeq GetLogSum to FileVar,FileName else

^
',' unexpected, Was expecting: "ELSE", "ON", "THEN", "THENEOL", "ELSEEOL"
0030 end

^
WARNING: Text found after final END statement

1 Errors detected, No Object Code Produced.

thanks in advance

rsunny · Post by **rsunny** » Fri Feb 04, 2011 9:45 am

Hi ,

I would like to know is there any possibility of using more than one after job subroutine in the same job?

thanks in advance

ray.wurlod · Post by **ray.wurlod** » Fri Feb 04, 2011 1:36 pm

Your syntax for WriteSeq is incorrect - you have tried to use the syntax for Write. Check the DataStage BASIC manual.

You can use as much code as you like, but only one call from the after-job subroutine area in job properties. That, however, can be a wrapper that invokes as many routines as you wish.

rsunny · Post by **rsunny** » Fri Feb 04, 2011 2:26 pm

Thanks ray,

I will try to use the correct syntax fo write and see what is the result i am going to get and will update.

Thanks in adavance

rsunny · Post by **rsunny** » Fri Feb 04, 2011 2:30 pm

Hi ray ,

but i am getting the error in code saying failed to open the directory,

is this error because of write syntax?

thanks in advance

chulett · Post by **chulett** » Fri Feb 04, 2011 4:04 pm

You define 'DirName' and then don't use it anywhere except in the error messages. Append FileName to the end of it and then use that in your open statement.

kirankumarreddydesireddy · Sat Feb 05, 2011 11:25 pm

Hi sunny

Can you please post the latest code(after job sub routine) which writes job name,job start date and time,job end date and time tion to seq file

Thanks
Kiran

kduke · Post by **kduke** » Sun Feb 06, 2011 2:58 pm

Just download EtlStats. Switch the job to output to a sequential file.

kirankumarreddydesireddy · Mon Feb 07, 2011 2:47 am

We are not allowed to download any external applications

Sunny

Can you please post the latest code(after job sub routine) which writes job name,job start date and time,job end date and time tion to seq file

Thanks
Kiran

rsunny · Post by **rsunny** » Mon Feb 07, 2011 7:33 am

Hi ,

Kiran this code is to write the log into file

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H

ErrorCode = 0




Suffix = ".txt"

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

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

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

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



OpenSeq DirName to FileVar then
write GetLogSum to FileVar,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

Ans=FileName
close FileVar

but still getting the error.

craig- i tried with what you said but still got the error saying failed to open the directory

Thanks in advance

chulett · Post by **chulett** » Mon Feb 07, 2011 8:09 am

Have you verified that you've built a valid path in the routine and your user has the access rights it would need?

rsunny · Post by **rsunny** » Mon Feb 07, 2011 8:50 am

hi,

the code to store jobname and time 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

but i couldnty able to create a file at runtime

craig- when i run the routine with the existing file name it is working fine but when i tried to create a file at run time , i am getiing error saying Failed to open the directory.

Is datastage cant create a file at run time?

thanks in advance