How To Trap Reject rows..

rasi · Post by **rasi** » Sun Jan 19, 2003 9:25 pm

Hi,

Inside datastage when we set the constraint to reject rows after 1 records. It aborts the job immediately when this constraint violates for the 1st record.
I need to trap all the reject rows into a separate table. Is there any way to do so.

Thanks
Rasi

ray.wurlod · Post by **ray.wurlod** » Mon Jan 20, 2003 1:42 am

Go back to the grid where you define one link to handle rejected rows. Change the row limit (the rightmost column in the grid) to zero (which means that this link can process an unlimited number of rows without causing the job to abort).
You should also, when submitting the job to run, be aware of the limit on the number of warnings that may be generated, if rejected rows also generate a warning message.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

rasi · Post by **rasi** » Mon Jan 20, 2003 2:27 am

Hi Ray,

I want all the rejected records to be loaded into reject table and also abort the job when first error occours. Is there any way for that.

Thanks
Rasi

ray.wurlod · Post by **ray.wurlod** » Mon Jan 20, 2003 4:02 pm

Yes.
But it depends what you mean by "rejected". Do you mean rejected by the DataStage job itself, because it (the row) fails validation rules, or do you mean rejected by the target database? The solution is different in each case. There is also a question about what you mean by "error". Is this the same as "warning"?
In both cases, however, the job warning limits can be used to abort the job at the first error. Just set "abort job after 1 warning".
To capture all rows rejected by the DataStage job, proceed as per my earlier post.
To capture rows rejected by the target database, use a rejects link with no constraint expression on an output from the Transformer stage that is feeding the target database.
An even better approach would be to load your target database via its bulk loader, which has provision to capture rows rejected during the load, so that your DataStage job does not need to be concerned about monitoring these, and will run faster.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

rasi · Post by **rasi** » Tue Jan 21, 2003 12:25 pm

Hi Ray

thanks for your reply. The rejects i was saying is the rejects from validation. When a rows gets validated and rejected. It has to logged into a reject table with its surrogate key. But also I want to stop my job even if one row gets rejects. But want to log all the rejected records.

Is there any way to solve it

Thanks
Rasi

ray.wurlod · Post by **ray.wurlod** » Tue Jan 21, 2003 4:09 pm

Create an after stage routine that runs after the Transformer stage that rejects the rows. (Insert the name of the routine in the stage properties.)
In the after stage routine, use DSGetLinkInfo() function to determine whether any rows were processed along the link that handles the rejected rows, and set ErrorCode accordingly - a non-zero value will cause the job to abort.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

rasi · Post by **rasi** » Tue Jan 28, 2003 7:37 am

Hi Ray,

I Had Created a after stage routine and it has the ErrorCode=0. While I call this in the after stage routine i gave DSGetLINKINFO as an input value. But is not working. I don't know what should i give as input and how to SET DSGetLinkInfo to 0.

Thanks
Rasi

ray.wurlod · Post by **ray.wurlod** » Tue Jan 28, 2003 3:59 pm

DSLinkInfo() is actually a function, so you can't provide its name in the Input Value field. (Unlike Pascal, DataStage does not permit the passing of functional arguments.)
You will need to create a Routine, whose type is before/after, and provide its name as the name of the after-stage subroutine. You can pass the name of the link as the Input Value.
In the Routine, you obtain the row count processed by this link, e.g. RCount = DSLinkInfo(DSJ.ME, DSJ.ME, DSJ.ME, LinkName)
Then, if RCount is other than zero, log a warning message indicating that one or more (in fact, RCount) rows were rejected, and set ErrorCode to a non-zero value to stop the job.
(There's a "Programming with DataStage BASIC" class scheduled in Sydney in mid February - why not enrol?)

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

rasi · Post by **rasi** » Tue Jan 28, 2003 9:42 pm

Hi Ray,

Thanks for your reply. The following is the code I am using in the after routiune. Still it rejects the records and sends warning. And in the Input value I use DSJ.LINKROWCOUNT.

Thanks
Rasi

$INCLUDE DSINCLUDE JOBCONTROL.H
Reply = DSGetLinkInfo(DSJ.ME,DSJ.ME,DSJ.ME,InputArg)
Begin Case
Case Reply = 1
ErrorCode = 0 ;* set to 0 - file checked OK, job continues
Case Reply = 2
ErrorCode = 0 ;* set to 1 to stop the job if wait times out
Case @True
ErrorCode = 0 ;* some other error, such as bad InputArg, stops the job if after-routine
End Case

ErrorCode = 0

ray.wurlod · Post by **ray.wurlod** » Wed Jan 29, 2003 4:52 am

Shows what happens when you try to do things from memory! I misled you with my last response. Syntax for DSGetLinkInfo is:
DSGetLinkInfo(jobhandle, stagename, linkname, infotype)

So your routine should look something like this:

$IFNDEF JOBCONTROL.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$ENDIF
RowCount = DSGetLinkInfo(DSJ.ME,DSJ.ME,InputArg,DSJ.LINKROWCOUNT)
If RowCount 0
Then
ErrorCode = RowCount
Message = RowCount : " row(s) rejected."
Call DSLogWarn(Message, "Rows Rejected")
End

What you supply as the Input Value is the name of the link that handles rejected rows, not DSJ.LINKROWCOUNT.

Apologies for the inconvenience. [:I]

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

rasi · Post by **rasi** » Thu Jan 30, 2003 2:10 am

Hi Ray,

The routine has no problem it works fine.

Even after changing the ErrorCode = 0 it still logs a reject record in the director. And after changing the ErrorCode to 1 it says Job aborted, in that all the valid records gets inserted into the target even if the reject record is in between valid ones.

What I am doing in my job is. 1st Transformer passes all the reject records into a table and in another stream all the records are passed including the rejected ones. 2nd Transformer does the real check and aborts the job even if one record gets rejected.

Thanks
rasi