|
View previous topic :: View next topic
|
| Author |
Message |
soumen2255
New User
Joined: 25 Jun 2008
Posts: 24
Location: Pune
|
|
|
|
HI,
I want to load some of the tables with a new set of Data but some of the interdependent constraints does not allow me to do so.
I want to disable all the constraints for the tables,then load the data and enable all the constraints back.
Can someone help me out with possible guidance on this. |
|
| Back to top |
|
 |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello Soumen ,
Suggest u to read manuals . You can use ENFORCE NO option but it will put table in Check Pending state. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I want to disable all the constraints for the tables,then load the data and enable all the constraints back. |
Why would you want to do this? Suggest the data be corrected or the constraints be changed before you load the data. What you want to do will cause more problems than solutions. . . |
|
| Back to top |
|
|
soumen2255
New User
Joined: 25 Jun 2008
Posts: 24
Location: Pune
|
|
|
|
| There is a cross referencing data in my DB.Foreign Key of table1 refers to primary Key of Table B and Foreigh key of Table B refers to Primary Key of Table A.Like this there are around set of 50 tables that have an inter cross referencing so I want to disable the constraints,Load all the tables and enables the constraints back. |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
soumen,
Have you checked my reply? |
|
| Back to top |
|
|
soumen2255
New User
Joined: 25 Jun 2008
Posts: 24
Location: Pune
|
|
|
|
Hi,
Enforce No will not help since I need to delete the existing records from the table and load it.CheckPending would not allow that.
Thanks,
Soumen |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| but some of the interdependent constraints does not allow me to do so |
| Quote: |
| so I want to disable the constraints,Load all the tables and enables the constraints back. |
It is still not at all clear why you want to load "corrupt" data. . .
Why do you want to load data that does not meet "the rules"? Either the constraints are valid and must be met or they should be modified/removed. You might use a few "catch all" values so the constraints would be met and change/corrent them later. |
|
| Back to top |
|
|
soumen2255
New User
Joined: 25 Jun 2008
Posts: 24
Location: Pune
|
|
|
|
| There is a situation where we have 100 tables and 5000 constraints.Not sure on how to handle this kind of situation while uploading the data.So thought the best way is to disable the constraints,upload the data and enable it back. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
What needs "handling"? It should be automatic. . . |
|
| Back to top |
|
|
soumen2255
New User
Joined: 25 Jun 2008
Posts: 24
Location: Pune
|
|
|
|
Hello,
By Handling I mean to say that how do I insert data in all the tables maintaining referential integrity.As soon as I start the process of deletion of data from these table it says integrity violation.
We can handle this for small number of tables by emptying the foreign table first and then emptying the primary table and inserting records in the primart table first and then the foreign tables.
Please suggest. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
load all table with enforce no;
while some tables are checkpending
check all tables that are checkpending
wend
you might have to do the loop several times. |
|
| Back to top |
|
|
soumen2255
New User
Joined: 25 Jun 2008
Posts: 24
Location: Pune
|
|
|
|
Hi GUYC,
Thanks for your advice.Do you mean to say we have to set Integrity OFF for that table first.Could you please elaborate on how to use Enforce No option?
If Enforce No would stop the table from the constraint chcking while loading then we can select all the 100 tables and start the deletion/loading process right?
Thanks Again. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
It has been a while since I've done this, so you'll have to try my solution if it still works.
So If I remember correctly :
LOAD REPLACE ENFORCE NO will put your table and its children in check pending. (it doesn't check the RI)
CHECK DATA can read other tables that are check pending, so you don't need to to force other tables first.
If you have circular RI (or cross RI) at least one of the RI will be SET NULL.
f.e. :
A-> restrict -> B
B-> set null -> A
The table B with the set null will come out of check pending.
if check table A doesn't find errors , table A will also come out of check pending.
if check table A finds errors (and removes rows) , table B will go back into checkpending.
If you repeat it, in the end all tables will come out of checkpending. |
|
| Back to top |
|
|
soumen2255
New User
Joined: 25 Jun 2008
Posts: 24
Location: Pune
|
|
|
|
Hi GUYC,
Thanks a lot for your suggestion.
I have checked with the ENFORCE NO Option and this works!.
The only problem I have is now how to set my Check Data.This utility need the tablespace names for the tables so how do I pull out thetablespace name each time.
Could you please guide me on the Check Data utility since we do not have options to create the Exception table.
Thanks Again,
Soumen |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello Soumen,
| Quote: |
The only problem I have is now how to set my Check Data.This utility need the tablespace names for the tables so how do I pull out thetablespace name each time. |
You must be having table apce name with you first run the util for parent followed by Child Tables. |
|
| Back to top |
|
|
soumen2255
New User
Joined: 25 Jun 2008
Posts: 24
Location: Pune
|
|
|
|
Hi,
Could you please let me know how to get the tablespace name for each and individual table so that I can use it.The Table schemas are already created and we are not aware of the tablespace.
Just thought if we can query the table space name for a particular table and use it.Please suggest |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello Soumen,
If you have access to SYSIBM.SYSTABLES. You can get it using below sql
| Code: |
Select TSNAME
from SYSIBM.SYSTABLES
where NAME='tablename'
And CREATOR ='creatorname'; |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
maybe it is easier to
| Code: |
select * from sysibm.systablespace where status in ('P','S')
--and dbname = ? |
|
|
| Back to top |
|
|
soumen2255
New User
Joined: 25 Jun 2008
Posts: 24
Location: Pune
|
|
|
|
Hi,
Thanks every one for their suggestion. Now I am able to fetch the Tablespace names.  |
|
| Back to top |
|
|
|
|
