How to Disable Referential Integrity when loading DB2
Moderators: chulett, rschirm, roy
How to Disable Referential Integrity when loading DB2
I am in the process of taking a DB2 source, and loading it back to another DB2 table that has RI on another table. The client is ok with breaking the RI in this test environment. What would the syntax be within the DB2 connector to disable RI within the table before loading?
Re: How to Disable Referential Integrity when loading DB2
Well it looks like I was able to work through this one... Just in case anyone is interested, I simply executed a before sql that dropped the primary key which released the RI, then ran my job which did a Delete and Loaded my recs. I then did an after job sql to re-establish my Primary Key.
Before Sql:
ALTER TABLE XXXX.XXXX DROP PRIMARY KEY
After Sql:
ALTER TABLE XXXX.XXXX ADD PRIMARY KEY (XXXXX)
Before Sql:
ALTER TABLE XXXX.XXXX DROP PRIMARY KEY
After Sql:
ALTER TABLE XXXX.XXXX ADD PRIMARY KEY (XXXXX)
-Chris
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: How to Disable Referential Integrity when loading DB2
HiTHEDSKID wrote:I am in the process of taking a DB2 source, and loading it back to another DB2 table that has RI on another table. The client is ok with breaking the RI in this test environment. What would the syntax be within the DB2 connector to disable RI within the table before loading?
well if you talk about DB2 load - it does not care about the defined RI during load but sets a so called check pending state afterwards.
There are option in load to handle this and there is a command (set integrity) to do it explizitly but I strongy recommend to talk to your DBA ...
General speaking I would recommend that you follow Rays advise if you do not use DB2 Load because dropping the primary key and re-creating it is not sufficient to re-establish RI.
regards
Michael
Michael