Retrieve Warning messages at run time

[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.

[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?

[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.

[ArndW]

.. 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?

[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

[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.

[rachitha]

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.

[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.

[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

[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.

[rachitha]

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.

[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.

[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.!

[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

WARNING : YOUR REQUIREMENT MAY REQUIRE QUITE A BIT OF MODIFICATION!

[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