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
 

 

Referencial Integrity to be enforced (Database vs logic)

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

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Tue Jul 17, 2012 8:15 pm    Post subject: Referencial Integrity to be enforced (Database vs logic)
Reply with quote

Hi,

I have to introduced new tables which are child tables of a parent table.I am a bit confussed whether to use referential integrity at the application level or define it in the database.

The pros and cons of enforcing the Referential integrity in the database is as below :

Advantages :

- If we handle it at the business level then possible error might happen as compared to defining it at the database.

Disadvantages :

- scalability and performance issues
- Only my application will insert the data into the new tables thereby there is NO risk of corrupt data being inserted by some other application if my logic is correctly written.

Also, I don't see any existing tables which are using referential integrity in the database in my apllication thereby a bit reserve to use it for the new development.

Can anyone please suggest whether should i define RI in the database and what might be the consequencesof doing so.

It's an online application and data would be inserted in the table 24 X 7.

Please advice.

Thanks in Advance!
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jul 17, 2012 8:26 pm    Post subject: Re: Referencial Integrity to be enforced (Database vs logic)
Reply with quote

Quote:
referential integrity at the application level


Mean what??

Quote:
Advantages :

- If we handle it at the business level then possible error might happen as compared to defining it at the database.



Mean what??

and Why do you need RI for your new application or whatever, when RI is not being used in your system???
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Jul 17, 2012 8:27 pm    Post subject:
Reply with quote

much depends on your version of db2,
the size of the tables,
amount of activity and
how much iron you have.

I would not rely on
Quote:
Only my application will insert the data into the new tables thereby there is NO risk of corrupt data being inserted by some other application if my logic is correctly written.


back in the days of db2 vsn 2 thru 6, all the disadvantages of RI are true. vsn7 was a great leap forward, 8 addressed the db2 environment problems, 9 and 10 are improvements.

I have worked on systems that had application RI written in,
(did not use db2 RI)
and my experience was,
everytime someone made a change, they screwed-it-up.
most shops do not have the talent to support application RI.
my advice is if you are not using a discontinued version of db2
and you have some iron,
use DB2 RI.
That way you only have to code to respond to sqlcodes,
whereas if you use application ri, your are at the mercy of the people who code and modify your system.

I would also ignore the post prior to mine.
Back to top
View user's profile Send private message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Tue Jul 17, 2012 10:48 pm    Post subject:
Reply with quote

Thanks Dick!

I would also like to check with you whether there would be any difference in the application code(Housekeeping of child table) if I have enforced RI(DELETE RESTRICT) in the DB2 child table or without using it. I believe there won't be any difference in the application code to delete the record from the child table.i.e. I would have to ensure that all the records from the child table are deleted first before the record from the parent table is deleted in both the cases. Defining RI at the database level would just work as an additional check.

Is there anything specific I need to take care of while defining RI at the database level.

Many Thanks !
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Jul 17, 2012 11:41 pm    Post subject:
Reply with quote

well, i was composing a response in my mind as i read your post,
but when you said
Quote:
while defining RI at the database level.

I quashed that quickly and suggest that you do some reading.
RI is defined at the table(s) level.

i have no idea what version of db2 you have, or what kind of box you are running on.
and, no, I don't need an answer.
I suggest that you wait for GuyC or one of the other db2 types to answer your questions.

Also, I would check with your dba's, as they are going to be the ones to maintain the system.
Back to top
View user's profile Send private message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Wed Jul 18, 2012 11:07 am    Post subject:
Reply with quote

Hi Dick,

Apologies I meant at the table level only.

" if I have enforced RI(DELETE RESTRICT) in the DB2 child table or without using it".

The records will be inserted in the child table online transaction and would be deleted by a batch module.

Please suggest if there's anything specific I need to consider while defining RI at the table level.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Jul 18, 2012 12:46 pm    Post subject:
Reply with quote

With database RI, your child can get in Check Pending (Load,...)
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jul 18, 2012 3:08 pm    Post subject:
Reply with quote

Rahul,

Quote:
" if I have enforced RI(DELETE RESTRICT) in the DB2 child table or without using it".
If you have specified DELETE RESTRICT, you can delete rows from the child table. When deleting from parent table, if child rows exist, error occurs and no rows are deleted.

Advantages
When RI is maintained by DB2, it will make sure integrity is maintained during INSERT, UPDATE, DELETE, LOAD or RECOVERY.
You code can more focus on business logics.

Disadvantages
DB2 has to do additional check when a DML activity or LOAD is performed. Tiny stress there.
Additonally CHECK DATA utility needs to be run to verify the integrity when DB2 suspects referential problem.

Just googled there seems to be a REDbook about it. Check it out

Usually i have seen for online tables having DELETE CASCADE(caution) / DELETE RESTRICT and same warehousing tables with no RI. It differs shop-to-shop based on their experience and trial-and-incident method best practices are made.

Thanks,
Sushanth
Back to top
View user's profile Send private message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Wed Jul 18, 2012 10:19 pm    Post subject:
Reply with quote

Many Thanks for the valuable information!

In my application ,the tables will be inserted,Updated through online transactions (parent and child both). The tables will be deleted through batch module.

RI is defined in the child table DELETE RESTRICT. I would like to know whether there would be any perfomance issues while inserting or updating the parent/child table. As this would be done online thereby I am a bit worried about the performance.

The tables won't be uploaded through LOAD utility. Only in case of any recovery, the tables would be loaded and the necessary action such as to bring the table in Read/Write status from load pending can be taken care of.

Thanks!
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Jul 19, 2012 9:57 am    Post subject:
Reply with quote

Hi Rahul,

How many rows will be inserted into parent and child table via online application in a hour or day ?
For one parent row, how many child rows will be available ?
Is it just one-parent & one-child hierarchy or is there a whole big level of hierarchy ?

Quote:
RI is defined in the child table DELETE RESTRICT. I would like to know whether there would be any perfomance issues while inserting or updating the parent/child table. As this would be done online thereby I am a bit worried about the performance.
Make sure indexes are available for the foreign key and columns in the indexes are in right order and organized, this should take care of most of the things.

Catch with DELETE RESTRICT is, for every deletes in the child table, it has to access the parent table for checking purposes whereas cascade is one-shot, delete the parent row and child-rows are gone, the number of calls between parent and child tables are less. Cascade can have negative effects when there is a bigger heirarchy or millions of rows in child table. Also, setting the RI as cascade will invalidate the packages.

To evaluate and choose the best RI policy, basis on integrity and performance, you have to Know Your data(KYD). When RI is involved there is always room for maintainence.

Check out this article also

Thanks,
Sushanth
Back to top
View user's profile Send private message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Thu Jul 19, 2012 2:57 pm    Post subject:
Reply with quote

Hi Sushant,

Plaese find my replies for your queries :

How many rows will be inserted into parent and child table via online application in a hour or day ?

Parent = 100 records per day
Child = 5000 records per day

For one parent row, how many child rows will be available ?
one parent row can have around 20 child rows

Is it just one-parent & one-child hierarchy or is there a whole big level of hierarchy ?

its a 3 level hierarchy parent -->child1 --> child2 ---> child 3.1
--->child 3.2

Child1 is the parent of child2 and child 2 is the parent of child 3.1 and child 3.2

In case of a insert to the child table online , it will check the parent table for the corresponding key. Will it degrade the performance as compared to if the tables were independent without table level RI.

In case if a record is to be inserted online in child 3.1 then will it check for the complete hierarchy and will it de-grade the performance of the online system.

I think deletion is not a contraint as it will be done through batch and not online but still I think the performance in case of deletion will also degrade. pls suggest.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Jul 19, 2012 3:18 pm    Post subject:
Reply with quote

Rahul,

Quote:
In case of a insert to the child table online , it will check the parent table for the corresponding key. Will it degrade the performance as compared to if the tables were independent without table level RI.
With the row counts you have given, i don't think there gonna be any problem.

Quote:
In case if a record is to be inserted online in child 3.1 then will it check for the complete hierarchy and will it de-grade the performance of the online system.
I don't think so, it checks the parent table(2) when you insert a row to the child table(3.1), since there are no updates done in table(2), it shouldn't check its parent.

Just
Quote:
Make sure indexes are available for the foreign key and columns in the indexes are in right order and organized, this should take care of most of the things.

Thanks,
Sushanth
Back to top
View user's profile Send private message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Thu Jul 19, 2012 3:33 pm    Post subject:
Reply with quote

Thanks Sushanth,

I think it will be fine to enforce RI in the tables then but will just have to ensure that proper sequence is maintained while inserting/deleting the records.

Thanks all for your help !!
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 IMS Database backup info ashek15 IMS DB/DC 14 Wed Nov 16, 2016 5:29 am
No new posts Wildcard logic in COBOL dudenithy COBOL Programming 13 Thu Sep 01, 2016 7:36 pm
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts Sort Logic to extract data scorp_rahul23 DFSORT/ICETOOL 13 Tue Mar 01, 2016 1:22 am
No new posts Need a logic to validate the data in ... sandeep kumar302 COBOL Programming 17 Mon Feb 01, 2016 3:30 pm


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