Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to skip foriegn Key constraint in Spufi

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Arunkumar.chinnappa

New User


Joined: 05 Feb 2014
Posts: 2
Location: India

PostPosted: Fri Feb 07, 2014 2:17 pm    Post subject: How to skip foriegn Key constraint in Spufi
Reply with quote

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.
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Feb 07, 2014 3:35 pm    Post subject:
Reply with quote

Quote:
skip the SQL which has foriegn key constraint in it and proceed with the next SQL statment


Very bad idea..There is a purpose for having Referencial integrity

First try to understand the constraints and have the data filled for Parent-child related tables
Back to top
View user's profile Send private message
Arunkumar.chinnappa

New User


Joined: 05 Feb 2014
Posts: 2
Location: India

PostPosted: Fri Feb 07, 2014 3:48 pm    Post subject:
Reply with quote

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).
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Feb 10, 2014 1:17 pm    Post subject:
Reply with quote

if you are doing it with DSNTEP2 / DSNTEP4 :

Code:
--#SET MAXERRORS -1
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Tue Feb 11, 2014 11:27 am    Post subject:
Reply with quote

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:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000998.htm
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Feb 11, 2014 2:33 pm    Post subject:
Reply with quote

"SET Integrity" is a LUW feature, not available on z/OS.
at least, last time I checked (with every new version, this might change)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts why i need hit enter after EXECIO * D... jackzhang75 CLIST & REXX 10 Fri May 20, 2016 9:16 pm
No new posts Problems in File-Aid / DB2 Spufi whil... amitc23 CLIST & REXX 6 Fri Feb 12, 2016 1:59 pm
No new posts Not able to access the DB2 via SPUFI sakthikumarT DB2 6 Sat Nov 28, 2015 1:52 am
No new posts DB2 program returned data is dif than... pradeepkit DB2 1 Tue Mar 03, 2015 5:39 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us