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
 

 

Referentail integrity in DB2

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

New User


Joined: 25 Jun 2008
Posts: 20
Location: Hyderabad

PostPosted: Thu Jul 10, 2008 7:14 pm    Post subject: Referentail integrity in DB2
Reply with quote

Referential Integrity is implemented in the database, we have auto generated ids in some tables which should be referenced in the child tables.
Can you suggest me any solution to handle this?
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jul 10, 2008 7:17 pm    Post subject:
Reply with quote

handle what ?? to implement ref integrity ??
Back to top
View user's profile Send private message
MB
Warnings : 1

New User


Joined: 25 Jun 2008
Posts: 20
Location: Hyderabad

PostPosted: Thu Jul 10, 2008 7:23 pm    Post subject:
Reply with quote

Yes. Actually I have mainframe files and I need to load data to DB2 tables using LOAD utility. There are some tables which are auto generating some data, and this data should be refernced in child tables. So how can I handle the referernce to data in child tables.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jul 10, 2008 7:55 pm    Post subject:
Reply with quote

I Suppose you are referring to LOADing the child tables and with it maintaining ref intergrity ...in this case use LOAD utility with ENFORCE CONSTRAINTS option ....
Back to top
View user's profile Send private message
MB
Warnings : 1

New User


Joined: 25 Jun 2008
Posts: 20
Location: Hyderabad

PostPosted: Thu Jul 10, 2008 8:16 pm    Post subject:
Reply with quote

Id is generated autimatically in a column whenever a record is inserted in the table. So please let me know the order in which i should load the data i.e, parent first or child forst? And please explain me How the ENFORCE CONSTRAINTS option will work
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Thu Jul 10, 2008 8:33 pm    Post subject:
Reply with quote

Hi

When you have tables with Referential Integrity, you need to load the Parent table first and then Child Table.

First Auto generated IDs will be stored in the parent table and use those ids in child table to maintain the referential integrity.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jul 10, 2008 8:42 pm    Post subject:
Reply with quote

Mairaj,

If you are loading the parent tables you need not worry about any ref integrity bcos parent tables do not refer to child tables ...only whn you insert data in child tables then the foreign key declared in child table should already be present in the parent table ...

first load your parent table and then your child table ...ENFORCE CONSTRAINTS is default for load utility .. so it will chk for ref constraints while loading the child table ...

but you be careful with the identity cols .... it may lead to violation in DATA integrity ...
Back to top
View user's profile Send private message
MB
Warnings : 1

New User


Joined: 25 Jun 2008
Posts: 20
Location: Hyderabad

PostPosted: Thu Jul 10, 2008 9:14 pm    Post subject:
Reply with quote

Thanks for the replies..

What is the query to know the corresponding parent table for respective child table and the the column which has key constraints on child table?
Back to top
View user's profile Send private message
MB
Warnings : 1

New User


Joined: 25 Jun 2008
Posts: 20
Location: Hyderabad

PostPosted: Thu Jul 10, 2008 9:59 pm    Post subject:
Reply with quote

Hi Suresh,

Can you please explain me how can I use the ids stored in parent table to load into child table? Suppose think that i loaded 10 records in parent table, then id is autogenerated for these 10 records and has value from 1 to 10 respectively, then how can I use these autogenerated ids to load into child table? Please clarify on this. this is very important for me.
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Thu Jul 10, 2008 10:48 pm    Post subject:
Reply with quote

We need to have manual intervention in preparing the load file. The input load file should be populated with the values being generated in the parent table manually and can use this to load child table.

Alternate way is to have SQL Insert statement for the child table with generated values.

If you are inserting a large number of rows, you can use the LOAD utility. Alternatively, use multiple INSERT statements with predicates that isolate the data that is to be loaded, and then commit after each insert operation.

Suggestions are always welcome.
Back to top
View user's profile Send private message
MB
Warnings : 1

New User


Joined: 25 Jun 2008
Posts: 20
Location: Hyderabad

PostPosted: Fri Jul 11, 2008 3:46 pm    Post subject:
Reply with quote

'Use multiple INSERT statements with predicates that isolate the data that is to be loaded, and then commit after each insert operation.' --- Please explain in detail
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Fri Jul 11, 2008 11:06 pm    Post subject:
Reply with quote

Here you can use either
1) insert statement inside a COBOL program to populate the data to the table or
2) can have Insert statement in SPUFI and select the newly generated value from the parent and insert the same in to the Child.

The second one is manual process.
Back to top
View user's profile Send private message
MB
Warnings : 1

New User


Joined: 25 Jun 2008
Posts: 20
Location: Hyderabad

PostPosted: Sat Jul 12, 2008 7:04 pm    Post subject: Reply to: Referentail integrity in DB2
Reply with quote

Hi Suresh,

Thanks alot....
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Sun Jul 13, 2008 10:32 pm    Post subject:
Reply with quote

You are welcome Mairaj icon_biggrin.gif
Back to top
View user's profile Send private message
MB
Warnings : 1

New User


Joined: 25 Jun 2008
Posts: 20
Location: Hyderabad

PostPosted: Mon Jul 14, 2008 6:47 pm    Post subject:
Reply with quote

As the database is implemented in Referntial Integrity ,What are the LOAD utility options to be included in LOAD CARD. The other way how the Load card be designed for parent tables as the tables are in referential Integrity.
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 Jul 14, 2008 9:25 pm    Post subject:
Reply with quote

Hello,

Quote:
What are the LOAD utility options to be included in LOAD CARD
If you simply run the load, referential integrity should be automatic. . .
Back to top
View user's profile Send private message
MB
Warnings : 1

New User


Joined: 25 Jun 2008
Posts: 20
Location: Hyderabad

PostPosted: Tue Jul 15, 2008 12:19 pm    Post subject:
Reply with quote

Thanks Dick,

Please let me know on this
Are there any mandatory options to be included in the LOAD card for Referntial Integrity?
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Tue Jul 15, 2008 12:39 pm    Post subject:
Reply with quote

Raj,

ENFORCE CONSTRAINTS is default value for LOAD utility. Dont override it.

If you override, run CHECK DATA job after LOAD util job.
Back to top
View user's profile Send private message
MB
Warnings : 1

New User


Joined: 25 Jun 2008
Posts: 20
Location: Hyderabad

PostPosted: Tue Jul 15, 2008 3:33 pm    Post subject:
Reply with quote

Thanks Murali
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 Referencial Integrity to be enforced ... Rahul_kumar DB2 12 Tue Jul 17, 2012 8:15 pm
No new posts Suggestion to avoid Refrential Integr... GuyC DB2 3 Mon Mar 15, 2010 11:24 am
No new posts Rule be enforced by referential integ... ntmartins DB2 2 Thu Dec 04, 2008 5:47 pm
No new posts Fileaid db2 referential integrity sanjida Compuware & Other Tools 6 Sun Jan 20, 2008 6:10 pm
No new posts Referential integrity b/w Parent and ... vikas kumar jain DB2 7 Fri Jun 22, 2007 10:39 am


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