Page 2 of 4
Posted: Thu Feb 03, 2011 3:09 pm
by ray.wurlod
You can use BCI functions from a routine to load data into Oracle.
Search DSXchange for examples.
Posted: Fri Feb 04, 2011 3:33 am
by kirankumarreddydesireddy
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
Posted: Fri Feb 04, 2011 8:03 am
by chulett
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
Posted: Fri Feb 04, 2011 9:43 am
by rsunny
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
Posted: Fri Feb 04, 2011 9:45 am
by rsunny
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
Posted: Fri Feb 04, 2011 1:36 pm
by ray.wurlod
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.
Posted: Fri Feb 04, 2011 2:26 pm
by rsunny
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
Posted: Fri Feb 04, 2011 2:30 pm
by rsunny
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
Posted: Fri Feb 04, 2011 4:04 pm
by chulett
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.
Posted: Sat Feb 05, 2011 11:25 pm
by kirankumarreddydesireddy
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
Posted: Sun Feb 06, 2011 2:58 pm
by kduke
Just download EtlStats. Switch the job to output to a sequential file.
Posted: Mon Feb 07, 2011 2:47 am
by kirankumarreddydesireddy
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
Posted: Mon Feb 07, 2011 7:33 am
by rsunny
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
Posted: Mon Feb 07, 2011 8:09 am
by chulett
Have you verified that you've built a valid path in the routine and your user has the access rights it would need?
Posted: Mon Feb 07, 2011 8:50 am
by rsunny
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