Foeign Key Violation not caught by REJECTED scenario

muascdev · Post by **muascdev** » Sun Jan 07, 2007 11:59 am

When i try to insert a record, i want to capture the rejected records, surprisingly 'FOREIGN KEY VIOLATION' is not being rejected, but its rejecting records with invalid lengths. i tried using DBMSCODE ,SQLSTATE, none of them have any value in it.. anyhelp is appreciated

chulett · Post by **chulett** » Sun Jan 07, 2007 12:13 pm

How are you capturing rejects? There's all kinds of different methods, you'd need to explain exactly how yours is setup before anyone could comment on your issue.

muascdev · Post by **muascdev** » Sun Jan 07, 2007 12:15 pm

i am checking for VALID_LINK.REJECTED

chulett · Post by **chulett** » Sun Jan 07, 2007 12:23 pm

How exactly? Post your constraint derivation here directly from the job, please don't hand type it. Or is that it?

Specifically...

Is your database link called VALID_LINK and what you posted is the constraint from a separate 'reject' link you setup? Also let us know what database and stage you are using - the more details the better, ya know.

muascdev · Post by **muascdev** » Sun Jan 07, 2007 2:20 pm

thats exactly I am doing, I am checkig that condition on a reject link to see if VALID_LINK record got rejected. My database is oralce, and i am using DRS stage to insert into oracle.

chulett · Post by **chulett** » Sun Jan 07, 2007 6:20 pm

While it should work fine, I'm not a big fan of the DRS stage so I don't use it and can't say what kind of an influence it might have on your rejection handling. Unless you've got a wonderful reason for using it, I'd really suggest sticking the OCI stages for Oracle.

I prefer REJECTEDCODE, so if that was literally your reject link constraint you could try changing it to:

Code: Select all

VALID_LINK.REJECTEDCODE

And see if that changes the behaviour. For the better.

muascdev · Post by **muascdev** » Sun Jan 07, 2007 8:20 pm

well i was initially using oracle stage but its having issues when we use 2 sql's 1 upadte and 1 insert in user defined sql, the same sql's work fine as expected when we use DRS stage, but oracle stage giving an error all the time.

chulett · Post by **chulett** » Sun Jan 07, 2007 9:33 pm

The issues aren't with the OCI stage, it's with multiple actions in user-defined sql... IMHO that's a bad path to be walking down and the DRS stage just makes the slope a little more slippery by allowing stuff like that. Learn how to build robust jobs - native stages, separate links for inserts v. updates, generated sql, all that boring stuff. Plus side is they'll work fine and reject things in a predictable manner.

Throw in multiple actions in user-defined sql (something you failed to mention you were doing) and all bets are off. Obviously, it's not working 'fine as expected' or we wouldn't be here. Not sure how much help we can be in that situation, but perhaps if you posted your sql? Also, when do you have this 'issue' with rejects, only after the update fails and then the insert generates the FK violation or something else?

muascdev · Post by **muascdev** » Sun Jan 07, 2007 10:43 pm

i really dont know whether to insert or update unless i check the database, since datastage does allow 2 sqls we did that.

chulett · Post by **chulett** » Sun Jan 07, 2007 11:49 pm

Of course not, nobody does - so check the database. That's what hashed files are for and the concepts of preloading and using them to determine insert versus update are the heart and soul of the Server product. Learn it. Love it. The people who come after you will thank you.

However, that's a whole 'nuther lesson in and of itself. If you want to pursue the reject issue, post your sqls and answer the questions in the last post. And for grins try changing REJECTED to REJECTEDCODE, curious what difference (if any) it makes in your case.