Reject records with referential integrity constraint
Moderators: chulett, rschirm, roy
Reject records with referential integrity constraint
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
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
Why don't you use the reject link and handling builtin to the Oracle Enterprise stage?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
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
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
It will never be a better solution than prevention.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.