Upsert Error while trying to Update and Insert
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 13
- Joined: Wed Feb 24, 2010 9:41 am
Upsert Error while trying to Update and Insert
Hi,
I created One Dataset with one record with PolicyNumber as Key Column and I am trying to insert it into a DB2 table using ODBC Enterprise Stage with a reject link going to a sequential File Stage. Well the Table to which I am inserting is a Child Table to a Parent table having same PolicyNumber as Key Column.
In the Dataset I created have a PolicyNumber which have no records in Parent Table. Now my expectation would be if I try to insert the record in the child table the record should get rejected and go into a sequential file stage returning some SQLCODE saying that the Foreign Key RelationShip got failed. But The job is getting Failed saying in the log
APT_CombinedOperatorController,0: Fatal Error: [IBM][CLI Driver][DB2] SQL0530N The insert or update value of the FOREIGN KEY "FQA551" is not equal to any value of the parent key of the parent table. SQLSTATE=23503
My question is, if we are trying to insert or update if some foreign key relation ship fails, is it going to reject the records or is it going to fail the whole job?
If you think the post is not elobarative enough Please let me know. I can explain you clearly.
I created One Dataset with one record with PolicyNumber as Key Column and I am trying to insert it into a DB2 table using ODBC Enterprise Stage with a reject link going to a sequential File Stage. Well the Table to which I am inserting is a Child Table to a Parent table having same PolicyNumber as Key Column.
In the Dataset I created have a PolicyNumber which have no records in Parent Table. Now my expectation would be if I try to insert the record in the child table the record should get rejected and go into a sequential file stage returning some SQLCODE saying that the Foreign Key RelationShip got failed. But The job is getting Failed saying in the log
APT_CombinedOperatorController,0: Fatal Error: [IBM][CLI Driver][DB2] SQL0530N The insert or update value of the FOREIGN KEY "FQA551" is not equal to any value of the parent key of the parent table. SQLSTATE=23503
My question is, if we are trying to insert or update if some foreign key relation ship fails, is it going to reject the records or is it going to fail the whole job?
If you think the post is not elobarative enough Please let me know. I can explain you clearly.
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
-
- Premium Member
- Posts: 13
- Joined: Wed Feb 24, 2010 9:41 am
Can you be specific about which triggers.vinothkumar wrote:Check in your database whether there are any triggers for this child table pointing to parent table.
I just asked my DBA what she told me is they just employed Foreign Key and Primary key relationships between parent and child tables, other than that they did not create any Triggers on the tables.
-
- Premium Member
- Posts: 13
- Joined: Wed Feb 24, 2010 9:41 am
-
- Participant
- Posts: 148
- Joined: Thu Apr 10, 2008 12:47 am
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Your question is whether an insertion failure caused by referential integrity should fail the job or flow down the reject link.
Set your array size and commit size to 1, I have seen behavior where having a large array/commit size resulted in a job failing instead of rejecting. If the behavior remains, then you have answered your own question.
FYI, if you are building a warehouse and not an operational system it is generally a bad practice to have referential integrity enabled. It should be defined and disabled in your database (causes many different types of issues in a batch loading environment to have RI enabled).
Set your array size and commit size to 1, I have seen behavior where having a large array/commit size resulted in a job failing instead of rejecting. If the behavior remains, then you have answered your own question.
FYI, if you are building a warehouse and not an operational system it is generally a bad practice to have referential integrity enabled. It should be defined and disabled in your database (causes many different types of issues in a batch loading environment to have RI enabled).
-
- Premium Member
- Posts: 13
- Joined: Wed Feb 24, 2010 9:41 am
Yes Thats Exactly My Questionkwwilliams wrote:Your question is whether an insertion failure caused by referential integrity should fail the job or flow down the reject link.
I set Insert Array size as '1'. It was previously '2000', and RowCommitInterval as '1' in the ODBC Enterprise Stage. But the problem is still existing. It eventually mean that the job gets failed if the foreign key relationship fails when trying inserting into a table even though it have a reject link right.kwwilliams wrote: Set your array size and commit size to 1, I have seen behavior where having a large array/commit size resulted in a job failing instead of rejecting. If the behavior remains, then you have answered your own question.
we are building a operational Systemkwwilliams wrote: FYI, if you are building a warehouse and not an operational system it is generally a bad practice to have referential integrity enabled. It should be defined and disabled in your database (causes many different types of issues in a batch loading environment to have RI enabled).
![Sad :(](./images/smilies/icon_sad.gif)
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
You can enter a ticket with your support provider if you don't think this behavior is correct. As far as your question goes, this is how it functions ... currently.I set Insert Array size as '1'. It was previously '2000', and RowCommitInterval as '1' in the ODBC Enterprise Stage. But the problem is still existing. It eventually mean that the job gets failed if the foreign key relationship fails when trying inserting into a table even though it have a reject link right.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com