Reject records with referential integrity constraint

dinthat · Post by **dinthat** » Mon Mar 10, 2008 5:58 am

Hi,

In my job I am inserting a set of records into Oracle table using Oracle Enterprise stage. But whenever a referential integrity constarint violation is ocuuring, the job gets aborting and showing FATAL ORA error in the Director. These records are not going into rejection link.

Is there any solution for handling referential integrity constrain in Oracle stage. I need to reject all such records.

Thanks in advance.

dinthat

hamzaqk · Post by **hamzaqk** » Mon Mar 10, 2008 6:06 am

Ther is an option in oracle stage ( dont remember which it was) which allows to soften such constraints before loading the data. Not sure why do you have these in the actual DWH in the first place!

dinthat · Post by **dinthat** » Mon Mar 10, 2008 6:48 am

Hi All,

Any idea about such an option in oracle stage? I am using upsert method to load data.

Thanks in advance,

dithat

ArndW · Post by **ArndW** » Mon Mar 10, 2008 6:56 am

Why don't you use the reject link and handling builtin to the Oracle Enterprise stage?

dinthat · Post by **dinthat** » Mon Mar 10, 2008 9:01 am

Hi ArndW,

I am using rejectlink and the output reject record option in Oracle stage is set true also.

When a primary key violation is occuring, I am getting such records in reject link. But whenever a referential integrity constrain violation is occuring, I am not getting those records in reject link, but my job is aborting and showing FATAL ORA error in the Director log.

Thanks And Regards,

dinthat

ArndW · Post by **ArndW** » Mon Mar 10, 2008 10:30 am

I now see what you mean. I'm surprised at that behaviour but am not at a site where I can check to see if it works the same way in my implementation. I would expect constraint violations to be caught in the reject link instead of causing a job abort.

dinthat · Post by **dinthat** » Tue Mar 11, 2008 11:48 am

Hi All,

Any workaround or solution for this strange problem?

Thanks And Regards,
dinthat

ray.wurlod · Post by **ray.wurlod** » Tue Mar 11, 2008 6:03 pm

Prevention. Verify within your job stream that no referential integrity issues occur.

dinthat · Post by **dinthat** » Thu Mar 13, 2008 6:27 am

Hi Ray,

I am doing the same in my jobs. Before inserting records into Oracle I am performing a lookup with the Reference table. So I can handle the referential integrity problem. But I am searching for a simpler solution. If we can reject all the referential integrity constrain violated records from the oracle Enterprise stage itself, then it could be a better solution.

Thanks And Regards,

dinthat.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 13, 2008 6:31 am

It will never be a better solution.

dinthat · Post by **dinthat** » Thu Mar 13, 2008 7:57 am

Hi Ray,

Thank u for ur reply.

But why u r saying rejecting records in the target will never be a better solution? Will it slowdown the job. Since I am inserting 33 million records into target, in that records only 60 records are violating parent key constrain. So to identify this 60 record I am performing a lookup with two reference table. Can u pls guide me which will be the better method in this situation? Rejecting the 60 records in target side or performing a lookup with 33 million records? Which will increase the performance of my job?

Thanks And Regards,

dinthat

kcbland · Post by **kcbland** » Thu Mar 13, 2008 8:33 am

Something has to verify the constraints. Leaving it to happen at load time means the database will do it. Doing it during transformation allows you to act on it. Either way, something is doing the verification. You're not saving anything, just doing it in a way that is more difficult to manage and slower because of the messaging and mechanisms involved.

By verifying constraints during transformation, you can supply default values. You can take an alternative derivation path. Sometimes really good warehouse architects allow for different default values to indicate why a column is populated with a default:

- source foreign key data was null
- source foreign key data was invalid
- source foreign key data exists but warehouse foreign key does not
- source foreign key data exists but warehouse foreign key row is not appropriate

The place to put these types of rules is the place where business rules are enforced - the transformation steps. Otherwise, you're embedding these rules in the loading SQL or writing stored procedures to load or you're building secondary DataStage jobs to process your reject files.

As Ray stated: It will never be a better solution.

dinthat · Post by **dinthat** » Thu Mar 13, 2008 8:42 am

Thank u kcbland for a detailed explanation.

Anyway I handled the parent key violation using lookup. I am marking this topic as a workaround. But still I am searching for a solution to reject those records in target side...

ray.wurlod · Post by **ray.wurlod** » Thu Mar 13, 2008 5:02 pm

That "solution" is to do nothing, and let the database handle it. You will, as a result, end up with warnings in the DataStage job log, or in a bulk loader's log file.

It will never be a better solution than prevention.

dinthat · Post by **dinthat** » Sat Mar 15, 2008 11:36 am

So the conclusion of this post is "Prevention is better than cure..."

Thank you all for ur valuable replies...