How to skip foriegn Key constraint in Spufi

Arunkumar.chinnappa · New User Joined: 05 Feb 2014 Posts: 2 Location: India

Hi,

I need to run a bulk of insert SQL around 100K Insert statement in a Spufi. But there is a foriegn key constraint violating in the table as the reference record is deleted from the other table due to which the SQL fails and stops at that point.

Is there any way to skip the SQL which has foriegn key constraint in it and proceed with the next SQL statment?

I don't have option to write a cobol program or manually remove the SQL's which will fail due to Foriegn key constraint.

Thanks in Advance for your valueable inputs on this.

Pandora-Box · Posted: Fri Feb 07, 2014 3:35 pm

Arunkumar.chinnappa · New User Joined: 05 Feb 2014 Posts: 2 Location: India

I know it is a Very Very Bad idea but the requirements are like that. It will take around 1 week for me to implement my SQL in the production enviroment.

Inbetween time frame there is a possbility of deleting the record from the Parent table is possbile through online.

So after a week It is not possbile for me to recheck for all 100K SQL for reference record available in Table 1 while my insert in Table 2 which is a Audit data for Table 1.

Anyother possible way to skip that constraint(Here skipping is not skipping the constraint, it is to skip the Insert SQL if referencial constraint found and continue with the next Insert SQL).

GuyC · Posted: Mon Feb 10, 2014 1:17 pm

if you are doing it with DSNTEP2 / DSNTEP4 :

Rohit Umarjikar · Posted: Tue Feb 11, 2014 11:27 am

Try ENFORCE NO option:

If you use the ENFORCE NO option, you tell LOAD not to enforce referential constraints. Sometimes you have good reasons for doing that, but the result is that the loaded table space might violate the constraints. Hence, LOAD places the loaded table space in CHECK-pending status. If you use REPLACE, all table spaces that contain any dependent tables of the tables that were loaded are also placed in CHECK-pending status. You must reset the status of each table before you can use any of the table spaces

Also you might wants to check below link,

SET INTEGRITY:

publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000998.htm

GuyC · Posted: Tue Feb 11, 2014 2:33 pm

"SET Integrity" is a LUW feature, not available on z/OS.
at least, last time I checked (with every new version, this might change)