Page 1 of 2
Retrieve Warning messages at run time
Posted: Mon Aug 21, 2006 3:18 am
by rachitha
Hi..
I want to retrieve warning messages at runtime and assign the reason for rejection to appropriate record key . Waititng for your suggestions.
Thanks In Advance ,
Rachitha.
Posted: Mon Aug 21, 2006 3:31 am
by ArndW
You might get better answers if you explain in more detail. When writing to certain database stages you can capture the SQL error information in a transform stage before that link and redirect this to other output links. Is this what you wish to do?
retrieve warning messages
Posted: Mon Aug 21, 2006 3:38 am
by rachitha
if we run job.. some records may be rejected. i want to fetch the reason for its rejection at datastage level. you can say that we can do it manually of course.. but i want it to be done automatically by the datastage using some dsgetlogsummary macro's. so plz gimme suggestions.
Re: retrieve warning messages
Posted: Mon Aug 21, 2006 3:43 am
by ArndW
rachitha wrote:.. so plz gimme suggestions.
Do you mean to say "please give me suggestions"? Or did I misunderstand?
First off, are you trying to analyze or act on these warnings from another job or within the job itself? DSGetLogSummary() can be called from a routine or the same functionality can be called from the
dsjob command line utility.
What do you want to do with the messages once you have them? Act on each row in some job?
Posted: Mon Aug 21, 2006 3:57 am
by rachitha
Hi..
First of all i would like to say that am new to datastage i dn't know much abt this. i have just done one simple datamart.
If we think from end user side,
for ex : assume a report contains some rejected records too... and the enduser must know why those records have been rejected.
We can implement this manually by writing diff conditions. am asking you can we get this job done at datastage level using any of the datasttage macros's or routines.. etcc.
"i explored on this to some extent and found that we can retrieve warning messages using dsj.logwarning which is to be passed as a parameter for dsgetlogsummary macro which in turn shld be implemented using routines.and this macro will return array of messages. this is one idea i got. but am not able to write routines."
So, i posted this topic , so that i can get some more suggestions and guidance .
thank you
rachitha
Posted: Mon Aug 21, 2006 4:26 am
by ArndW
The easiest method is to capture those rejected records with their warning messages from the job itself and write them to a sequential file. This can then be used for error tracking. It is much easier to implement and maintain than a separate job that extracts warning messages from log files and then processes them.
Posted: Mon Aug 21, 2006 4:39 am
by rachitha
ArndW wrote:The easiest method is to capture those rejected records with their warning messages from the job itself and write them to a sequential file. This can then be used for error tracking. It is much easier to implement and maintain than a separate job that extracts warning messages from log files and then processes them.
yeah i want to implement it in a same job. but where am facing prb is to get warning messages. i think you got my query.
Thank you
rachitha.
Posted: Mon Aug 21, 2006 7:11 am
by chulett
Post some examples of these 'warnings' that you are having a problem with. A Reject link should handle anything that has an issue with the database.
Posted: Mon Aug 21, 2006 1:32 pm
by DSguru2B
You can also get DMBSCODE of rejected records. But even then it will just be a code. You can have a small table with dbms codes and what it means. Once you have the dbms codes, you can do the lookup and get the message.
You can also write a bat script and get all the warning messages from a particular jobs log file by using
dsjob -logsum -type warning
Posted: Mon Aug 21, 2006 9:58 pm
by ray.wurlod
Depending on the stage type you can also get LASTERR as well as DBMSCODE (these are output link variables from the link attempting to put the row into the target database). Capture these onto a separate output link from the immediately-preceding Transformer stage.
hi
Posted: Tue Aug 22, 2006 3:53 am
by rachitha
ray.wurlod wrote:Depending on the stage type you can also get LASTERR as well as DBMSCODE (these are output link variables from the link attempting to put the row into the target database). Capture these onto a separ ...
Can you plz be much more clear.
Thank You
rachitha.
Posted: Tue Aug 22, 2006 4:39 am
by ray.wurlod
Isn't Plz a town in the Balkans somewhere?
Do a search of DataStage help looking for the Link Variables topic. This should be as clear as you're going to get.
Posted: Tue Aug 22, 2006 6:03 am
by rameshrr3
Rachita , before you start looking for
Plz in zchkoslivakijza or syldavia ,
open the help file in data stage designer and type "links" as the keyword in index tab,then look for the variables section under "links". That should be as "clear" as you could ever get.!
Posted: Tue Aug 22, 2006 6:14 am
by rameshrr3
Alternatively , if you could make sense of this code, yuo can modify it and use it
Code: Select all
$INCLUDE DSINCLUDE JOBCONTROL.H
DET = 0
hJob = Trim(Job)
DirName = Field(FileVar,';',1,1)
File = Field(FileVar,';',2,1)
ReportText = ''
PathName = ''
RetVal = 0
*----------Get Basic Job Info-------------------------------------*
JobHandle = DSAttachJob(hJob,DSJ.ERRFATAL)
JobName = DSGetJobInfo(JobHandle,DSJ.JOBNAME)
JobStarted = DSGetJobInfo(JobHandle,DSJ.JOBSTARTTIMESTAMP)
JobEnded = DSGetJobInfo(JobHandle,DSJ.JOBLASTTIMESTAMP)
JobStatus = DSGetJobInfo(JobHandle,DSJ.JOBSTATUS)
*--------------Get Job Log Info -------------------------------*
Begin Case
Case JobStatus = 3
LogEvent1 = DSGetLogSummary(JobHandle,DSJ.LOGWARNING,JobStarted,JobEnded,0);
LogEvent2 = DSGetLogSummary(JobHandle,DSJ.LOGFATAL,JobStarted,JobEnded,0);
NumWarn = Count(LogEvent1,@FM)+1
For I = NumWarn To 1 Step -1;
LogText = Field(LogEvent1,@FM,I,1)
ReportText = ReportText:"Warning ,":JobName:",Aborted,":JobStarted:",":JobEnded:",":Ereplace(Field(LogText,"\",6,1),",",";"):Char(10)
Next I
ReportText = ReportText:"Fatal ,":JobName:",Aborted,":JobStarted:",":JobEnded:",":Ereplace(Field(LogEvent2,"\",6,1),",",";"):Char(10);
Case JobStatus = 2
LogEvent1 = DSGetLogSummary(JobHandle,DSJ.LOGREJECT,JobStarted,JobEnded,0);
LogEvent2 = DSGetLogSummary(JobHandle,DSJ.LOGWARNING,JobStarted,JobEnded,0);
NumRejects = Count(LogEvent1,@FM)+1
NumWarn = Count(LogEvent2,@FM)+1
For I = NumRejects To 1 Step -1;
LogText = Field(LogEvent1,@FM,I,1)
If Len(LogText)<> 0 Then
ReportText = ReportText:"Reject ,":JobName:",Warn,":JobStarted:",":JobEnded:",":Ereplace(Field(LogText,"\",6,1),",",";"):Char(10)
End Else
ReportText =""
End
Next I
For J = NumWarn To 1 Step -1
LogText1 = Field(LogEvent2,@FM,I,1)
ReportText = ReportText:"Warning ,":JobName:",Warn,":JobStarted:",":JobEnded:",":Ereplace(Field(LogText1,"\",6,1),",",";"):Char(10);
Next J
End Case
*--------- Write Info To File in Append Mode------------------*
PathName = DirName:File
OpenSeq PathName To F.Var Then
Loop
ReadSeq Dummy From F.Var Else Exit ;* at end-of-file
Repeat
WriteSeq ReportText To F.Var Else
Call DSLogFatal("Cannot write to ":PathName, "MyRoutine")
End
End Else
Call DSLogFatal("Cannot open file ":PathName, "MyRoutine")
End
*------- Release Job ----------------------------------------*
DET = DSDetachJob(JobHandle)
Ans = 0
routines
Posted: Thu Aug 24, 2006 11:34 pm
by rachitha
hi
i want to learn writing routines in datastage.. can any one of you guide me .. plz suggest any websites or books.waiting for your reply.
thanks in advance
rachitha