If one is interested in "trapping" rejected rows. (e.g. dup key rejections from the database or other database constraints). Design the job as follows;
SourceStage----readlink---->TransformerStage-----insertlink----->TargetR
SourceStage----readlink---->TransformerStage-----insertlink----->DBMS
rejectlink
SeqFileStage
In the constraint screen (for the transformer stage) check "rejects" =Yes on the rejectlink. The result will be that if either the constraint on the insert link fails or if the database rejects the insert/update the row will pass out the reject path (based upon what youve mapped). You can also create a reason column in the reject link. In the derivation for the reason column right click (or hit the ...) to drop down the menu options. Select "Link Variables", "outputs" then the insertlink. This will provide you a list of error conditions/msgs that you can add to this row when the reject condition occurs. If you select "LastErr" you will get the entire log message that gets logged when a database constraint reject happens (dup key, RI, null etc) If you want the specific database error number select "DBMSCODE" which will load the value of the respective database error code. This is a powerful feature of DataStage especially when you consider that you can react to the specific error condition when it occurs. e.g. If DBMSCODE = dup key then generate a key followed by another attempt to insert, if null constraint test columns for null then fill with default value..
-Allen ________________________________________________________________________________
Allen Spayth daspayth@wwc.com
Handling DataBase Rejects/Error conditions
Archive of postings to DataStageUsers@Oliver.com. This forum intended only as a reference and cannot be posted to.
Return to “Archive of DataStage Users@Oliver.com”
Jump to
- Moderators' Choice
- ↳ Editor's BLOG Corner
- ↳ Ask the Experts! - Dads and Grads
- ↳ DSXchange Testimonials
- ↳ Cognos (IBM BI)
- FAQs
- ↳ FAQs
- ↳ FAQ Discussion
- DataStage
- ↳ General
- ↳ IBM<sup>®</sup> Infosphere DataStage Server Edition
- ↳ IBM<sup>®</sup> DataStage Enterprise Edition (Formerly Parallel Extender/PX)
- ↳ Archive of DataStage Users@Oliver.com
- IBM<sup>®</sup>Infosphere Products<sup></sup>
- ↳ Business Glossary
- Suggestions
- ↳ Site/Forum
- ↳ Enhancement Wish List
- Consulting
- ↳ Talent
- ↳ Looking for Talent
- Support
- ↳ Parameter Manager
- ↳ Compile All Plus
- Usergroup Forums
- ↳ Usergroup Central Forum
- ↳ Heartland Usergroup Forum
- The Written Word
- ↳ Articles, White Papers and Tips and Tricks
- ↳ Product Documentation
- Third Party Applications
- ↳ Third Party Applications
- Product Derivatives
- ↳ Functions
- ↳ Routines
- ↳ Jobs
- ↳ Logs
- Tools
- ↳ Tools Forum
- Category
- ↳ Infosphere Master Data Management
- ↳ Data Quality Best Practices
- ↳ IBM QualityStage
- ↳ Information Analyzer (formerly ProfileStage)
- ↳ IBM<sup>®</sup> SOA Editions (Formerly RTI Services)
- ↳ IBM<sup>®</sup> DataStage TX
- ↳ BI
- ↳ Data Integration