Upsert Error while trying to Update and Insert

[kfb_developer]

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.

[vinothkumar]

Check in your database whether there are any triggers for this child table pointing to parent table.

[kfb_developer]

Check in your database whether there are any triggers for this child table pointing to parent table.

Can you be specific about which triggers.

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.

[ArndW]

Please have your DBA check the foreign key FQA551.

[kfb_developer]

Please have your DBA check the foreign key FQA551.

I just checked with my DBA and there are no triggers on any of the table with foreign key FQA551.

[devesh_ssingh]

you cannot insert record in child table without having its corresponding parent records..its violation of foreign key constraint.
try inserting such record in child who have corresponding record...
foreign key of child table linked to primary key of parent table...

[kwwilliams]

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).

[kfb_developer]

Your question is whether an insertion failure caused by referential integrity should fail the job or flow down the reject link.

Yes Thats Exactly My Question

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.

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.

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).

we are building a operational System

[kwwilliams]

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.

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.