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
 

 

Enable/Disable Constraints on DB2

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

New User


Joined: 25 Jun 2008
Posts: 24
Location: Pune

PostPosted: Mon Feb 22, 2010 8:54 pm    Post subject: Enable/Disable Constraints on DB2
Reply with quote

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

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Mon Feb 22, 2010 10:00 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Feb 22, 2010 10:39 pm    Post subject:
Reply with quote

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
View user's profile Send private message
soumen2255

New User


Joined: 25 Jun 2008
Posts: 24
Location: Pune

PostPosted: Tue Feb 23, 2010 1:43 pm    Post subject: Reply to: Enable/Disable Constraints on DB2
Reply with quote

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
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Tue Feb 23, 2010 1:45 pm    Post subject:
Reply with quote

soumen,

Have you checked my reply?
Back to top
View user's profile Send private message
soumen2255

New User


Joined: 25 Jun 2008
Posts: 24
Location: Pune

PostPosted: Wed Feb 24, 2010 7:13 pm    Post subject: Reply to: Enable/Disable Constraints on DB2
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Feb 24, 2010 8:10 pm    Post subject:
Reply with quote

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
View user's profile Send private message
soumen2255

New User


Joined: 25 Jun 2008
Posts: 24
Location: Pune

PostPosted: Wed Feb 24, 2010 8:16 pm    Post subject: Reply to: Enable/Disable Constraints on DB2
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Feb 24, 2010 10:16 pm    Post subject:
Reply with quote

Hello,

What needs "handling"? It should be automatic. . .
Back to top
View user's profile Send private message
soumen2255

New User


Joined: 25 Jun 2008
Posts: 24
Location: Pune

PostPosted: Thu Feb 25, 2010 12:04 pm    Post subject: Reply to: Enable/Disable Constraints on DB2
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Feb 25, 2010 1:19 pm    Post subject:
Reply with quote

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
View user's profile Send private message
soumen2255

New User


Joined: 25 Jun 2008
Posts: 24
Location: Pune

PostPosted: Thu Feb 25, 2010 1:23 pm    Post subject: Reply to: Enable/Disable Constraints on DB2
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Feb 25, 2010 1:44 pm    Post subject: A
Reply with quote

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
View user's profile Send private message
soumen2255

New User


Joined: 25 Jun 2008
Posts: 24
Location: Pune

PostPosted: Tue Mar 02, 2010 5:50 pm    Post subject: Reply to: Enable/Disable Constraints on DB2
Reply with quote

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
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Tue Mar 02, 2010 6:09 pm    Post subject:
Reply with quote

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
View user's profile Send private message
soumen2255

New User


Joined: 25 Jun 2008
Posts: 24
Location: Pune

PostPosted: Tue Mar 02, 2010 6:11 pm    Post subject: Reply to: Enable/Disable Constraints on DB2
Reply with quote

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
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Tue Mar 02, 2010 6:21 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Mar 02, 2010 11:12 pm    Post subject:
Reply with quote

maybe it is easier to
Code:
select * from sysibm.systablespace where status in ('P','S')
--and dbname = ?
Back to top
View user's profile Send private message
soumen2255

New User


Joined: 25 Jun 2008
Posts: 24
Location: Pune

PostPosted: Wed Mar 03, 2010 7:56 pm    Post subject: Reply to: Enable/Disable Constraints on DB2
Reply with quote

Hi,

Thanks every one for their suggestion. Now I am able to fetch the Tablespace names. icon_smile.gif
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 What is the code in CLIST to enable t... jackzhang75 CLIST & REXX 1 Fri Dec 02, 2016 3:02 am
No new posts JCL to Check whether a VSAM file is ... raj4neo CICS 3 Wed Aug 06, 2014 2:58 pm
No new posts How to enable qury optimization for M... srilata83 DB2 5 Tue Sep 25, 2012 4:34 pm
No new posts Please suggest me how to disable a ke... pradeep dodda CICS 6 Wed Sep 05, 2012 10:54 am
No new posts Which Utility will use for DISable an... Pandora-Box CICS 2 Mon Aug 13, 2012 6:26 pm


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