Referentail integrity in DB2

MB · New User Joined: 25 Jun 2008 Posts: 20 Location: Hyderabad

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?

ashimer · Posted: Thu Jul 10, 2008 7:17 pm

handle what ?? to implement ref integrity ??

MB · New User Joined: 25 Jun 2008 Posts: 20 Location: Hyderabad

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.

ashimer · Posted: Thu Jul 10, 2008 7:55 pm

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 ....

MB · New User Joined: 25 Jun 2008 Posts: 20 Location: Hyderabad

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

Suresh Ponnusamy · Posted: Thu Jul 10, 2008 8:33 pm

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.

ashimer · Posted: Thu Jul 10, 2008 8:42 pm

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 ...

MB · New User Joined: 25 Jun 2008 Posts: 20 Location: Hyderabad

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?

MB · New User Joined: 25 Jun 2008 Posts: 20 Location: Hyderabad

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.

Suresh Ponnusamy · Posted: Thu Jul 10, 2008 10:48 pm

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.

MB · New User Joined: 25 Jun 2008 Posts: 20 Location: Hyderabad

'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

Suresh Ponnusamy · Posted: Fri Jul 11, 2008 11:06 pm

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.

MB · New User Joined: 25 Jun 2008 Posts: 20 Location: Hyderabad

Hi Suresh,

Thanks alot....

Suresh Ponnusamy · Posted: Sun Jul 13, 2008 10:32 pm

You are welcome Mairaj

MB · New User Joined: 25 Jun 2008 Posts: 20 Location: Hyderabad

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.

dick scherrer · Posted: Mon Jul 14, 2008 9:25 pm

Hello,

MB · New User Joined: 25 Jun 2008 Posts: 20 Location: Hyderabad

Thanks Dick,

Please let me know on this
Are there any mandatory options to be included in the LOAD card for Referntial Integrity?

murmohk1 · Posted: Tue Jul 15, 2008 12:39 pm

Raj,

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

If you override, run CHECK DATA job after LOAD util job.

MB · New User Joined: 25 Jun 2008 Posts: 20 Location: Hyderabad

Thanks Murali