DB2 referential integrity

madhavan

DB2 referential integrity
Hi,

One of our customer's shop standard is that DB2 referential integrity will
not be used in the normal cases. Referential integrity will be maintained
through program logic. I was also told that 90% of the DB2 shops do not use
DB2 referential integrity due to performance reasons.

I thought referential integrity through DB2 will be performance wise better
than checking it through the program. Also unless we do a fetch for update,
there is always a chance of loosing the integrity of data when we do
referential check in the programs.

Can you please give me your experiences on this?

regards
Madhavan.



Sibimon Philip

Re: DB2 referential integrity
(in response to madhavan)
Madhavan,

I think you are misinformed. We used DB2 referential integrity in mainframe
always. It is very unreliable to maintain the referential integrity through
program. What happens if somebody update data through QMF or SPFUI or
through any C/S window?

Thanks
Sibimon Philip
972-702-2515 - Office
972-417-3597 - Residence
E-mail - [login to unmask email]


-----Original Message-----
From: madhavan [mailto:[login to unmask email]
Sent: Monday, January 24, 2000 6:04 AM
To: [login to unmask email]
Subject: DB2 referential integrity


Hi,

One of our customer's shop standard is that DB2 referential integrity will
not be used in the normal cases. Referential integrity will be maintained
through program logic. I was also told that 90% of the DB2 shops do not use
DB2 referential integrity due to performance reasons.

I thought referential integrity through DB2 will be performance wise better
than checking it through the program. Also unless we do a fetch for update,
there is always a chance of loosing the integrity of data when we do
referential check in the programs.

Can you please give me your experiences on this?

regards
Madhavan.








Paul A Redhead

Re: DB2 referential integrity
(in response to Sibimon Philip)
Madhavan,
The Business Rules should be built into the database. A lot of the
'performance problem' comes from people using RI for things it shouldn't be used
for. Things like checking for 'Y' or 'N' or day of the week etc...should be
table check constraints...performing I/O's for these sorts of things do cause
problems. Referential Integrity SHOULD be used to enforce parent/child
relationships, and in these cases will be faster (I believe) than the same check
being performed by SQL in your code.

Paul.

---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on
25/01/2000 09:30 ---------------------------


madhavan <[login to unmask email]> on 24/01/2000 22:03:48

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
Subject: DB2 referential integrity




Hi,

One of our customer's shop standard is that DB2 referential integrity will
not be used in the normal cases. Referential integrity will be maintained
through program logic. I was also told that 90% of the DB2 shops do not use
DB2 referential integrity due to performance reasons.

I thought referential integrity through DB2 will be performance wise better
than checking it through the program. Also unless we do a fetch for update,
there is always a chance of loosing the integrity of data when we do
referential check in the programs.

Can you please give me your experiences on this?

regards
Madhavan.











*************************************************************
Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads, or
of Queensland Transport. If you have received this electronic
mail message in error, please immediately notify the sender
and delete the message from your computer.



John O'Conner

Re: DB2 referential integrity
(in response to Paul A Redhead)
In our shop (DB2 5.1, OS/390 2.7), we update Production data with static SQL
only. We do not have RI defined in Production, but we do have it defined in
the Development database so that our testing can easily catch programs that
are not correctly maintaining RI. If (or maybe I should say "when") we
allow updates to Production data from sources that do not get developed (and
tested) in our development environment, we probably want to rethink RI. But
for now it's much less expensive for us to test user input with an "if"
statement than with a call to DB2.

John O'Conner
[login to unmask email]

-----Original Message-----
From: Paul A Redhead [mailto:[login to unmask email]
Sent: Monday, January 24, 2000 3:32 PM
To: [login to unmask email]
Subject: Re: DB2 referential integrity


Madhavan,
The Business Rules should be built into the database. A lot of the
'performance problem' comes from people using RI for things it shouldn't be
used
for. Things like checking for 'Y' or 'N' or day of the week etc...should be
table check constraints...performing I/O's for these sorts of things do
cause
problems. Referential Integrity SHOULD be used to enforce parent/child
relationships, and in these cases will be faster (I believe) than the same
check
being performed by SQL in your code.

Paul.

---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on
25/01/2000 09:30 ---------------------------


madhavan <[login to unmask email]> on 24/01/2000 22:03:48

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
Subject: DB2 referential integrity




Hi,

One of our customer's shop standard is that DB2 referential integrity will
not be used in the normal cases. Referential integrity will be maintained
through program logic. I was also told that 90% of the DB2 shops do not use
DB2 referential integrity due to performance reasons.

I thought referential integrity through DB2 will be performance wise better
than checking it through the program. Also unless we do a fetch for update,
there is always a chance of loosing the integrity of data when we do
referential check in the programs.

Can you please give me your experiences on this?

regards
Madhavan.











*************************************************************
Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads, or
of Queensland Transport. If you have received this electronic
mail message in error, please immediately notify the sender
and delete the message from your computer.








Richard A Yevich

Re: DB2 referential integrity
(in response to John O'Conner)
Actually it is quite the reverse. DB2 declarative RI should always be the
first choice and there are only a couple of exceptions when you need to
revert to application program RI.

Richard Yevich
+=====+======+
Information Technology Consulting, Data Modeling, Advanced Education
RYC® Inc. USA: 1-800-664-2421 Int'l: 1-305-361-8585 Fax: 1-512-476-3930
Web: < http://www.ryci.com > Email: [login to unmask email] Offices: USA and Europe
DB2® Family and Oracle® Specialists - Parallel Technologies
VLDB and Data Sharing Technologies (specialties SAP®, Peoplesoft®)
Authors of "DB2 Answers" by Osborne-McGraw Hill, May, 1999


> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
> madhavan
> Sent: Monday, January 24, 2000 7:04 AM
> To: [login to unmask email]
> Subject: DB2 referential integrity
>
>
> Hi,
>
> One of our customer's shop standard is that DB2 referential
> integrity will
> not be used in the normal cases. Referential integrity will be maintained
> through program logic. I was also told that 90% of the DB2 shops
> do not use
> DB2 referential integrity due to performance reasons.
>
> I thought referential integrity through DB2 will be performance
> wise better
> than checking it through the program. Also unless we do a fetch
> for update,
> there is always a chance of loosing the integrity of data when we do
> referential check in the programs.
>
> Can you please give me your experiences on this?
>
> regards
> Madhavan.
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]



Richard A Yevich

Re: DB2 referential integrity
(in response to Richard A Yevich)
Could you please explain and justify the following statement you made since
it is a little confusing:
> for now it's much less expensive for us to test user input with an "if"
> statement than with a call to DB2.

Reason is I would like to answer that but I need to be sure I did not
misunderstand you.

Regards,
Richard Yevich
+======+======+======+
The opinions expressed here are just opinions.
[login to unmask email]


> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
> O'Conner, John
> Sent: Monday, January 24, 2000 7:00 PM
> To: [login to unmask email]
> Subject: Re: DB2 referential integrity
>
>
> In our shop (DB2 5.1, OS/390 2.7), we update Production data with
> static SQL
> only. We do not have RI defined in Production, but we do have it
> defined in
> the Development database so that our testing can easily catch
> programs that
> are not correctly maintaining RI. If (or maybe I should say "when") we
> allow updates to Production data from sources that do not get
> developed (and
> tested) in our development environment, we probably want to
> rethink RI. But
> for now it's much less expensive for us to test user input with an "if"
> statement than with a call to DB2.
>
> John O'Conner
> [login to unmask email]
>
> -----Original Message-----
> From: Paul A Redhead [mailto:[login to unmask email]
> Sent: Monday, January 24, 2000 3:32 PM
> To: [login to unmask email]
> Subject: Re: DB2 referential integrity
>
>
> Madhavan,
> The Business Rules should be built into the database. A lot of the
> 'performance problem' comes from people using RI for things it
> shouldn't be
> used
> for. Things like checking for 'Y' or 'N' or day of the week
> etc...should be
> table check constraints...performing I/O's for these sorts of things do
> cause
> problems. Referential Integrity SHOULD be used to enforce parent/child
> relationships, and in these cases will be faster (I believe) than the same
> check
> being performed by SQL in your code.
>
> Paul.
>
> ---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on
> 25/01/2000 09:30 ---------------------------
>
>
> madhavan <[login to unmask email]> on 24/01/2000 22:03:48
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> To: [login to unmask email]
> cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
> Subject: DB2 referential integrity
>
>
>
>
> Hi,
>
> One of our customer's shop standard is that DB2 referential
> integrity will
> not be used in the normal cases. Referential integrity will be maintained
> through program logic. I was also told that 90% of the DB2 shops
> do not use
> DB2 referential integrity due to performance reasons.
>
> I thought referential integrity through DB2 will be performance
> wise better
> than checking it through the program. Also unless we do a fetch
> for update,
> there is always a chance of loosing the integrity of data when we do
> referential check in the programs.
>
> Can you please give me your experiences on this?
>
> regards
> Madhavan.
>
>
> To change your subscription options or to cancel your
> subscription visit the
>
>
>
>
>
>
>
>
> *************************************************************
> Opinions contained in this e-mail do not necessarily reflect
> the opinions of the Queensland Department of Main Roads, or
> of Queensland Transport. If you have received this electronic
> mail message in error, please immediately notify the sender
> and delete the message from your computer.
>
>
> To change your subscription options or to cancel your
> subscription visit the
>
>
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]



RICK (SWBT) DAVIS

Re: DB2 referential integrity
(in response to Richard A Yevich)
Madhaven,
Simply said: "If your RI isn't enforced by the DBM, you D O N O T
have RI." Every other method is simply something close to RI that will
eventually fail.
HTH,
Rick Davis
"This e-mail and any files transmitted with it are the property of SBC,
are confidential, and are intended solely for the use of the individual
or entity to whom this e-mail is addressed. If you are not one of the
named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender at 314-235-6854
and delete this message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."



-----Original Message-----
From: madhavan [mailto:[login to unmask email]
Sent: Monday, January 24, 2000 6:04 AM
To: [login to unmask email]
Subject: DB2 referential integrity


Hi,

One of our customer's shop standard is that DB2 referential integrity will
not be used in the normal cases. Referential integrity will be maintained
through program logic. I was also told that 90% of the DB2 shops do not use
DB2 referential integrity due to performance reasons.

I thought referential integrity through DB2 will be performance wise better
than checking it through the program. Also unless we do a fetch for update,
there is always a chance of loosing the integrity of data when we do
referential check in the programs.

Can you please give me your experiences on this?

regards
Madhavan.








craig patton

Re: DB2 referential integrity
(in response to RICK (SWBT) DAVIS)
I have to agree with Rick. There was concern about DB2 RI performance in
the earlier releases, but since V3, I 've felt that if you want REAL RI,
then it MUST be defined in the RDBMS. This saves from 2 different
programmers coding slightly different rules (not that this EVER happens!).


>From: "DAVIS, RICK (SBCSI)" <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: DB2 referential integrity
>Date: Tue, 25 Jan 2000 06:57:01 -0600
>
>Madhaven,
> Simply said: "If your RI isn't enforced by the DBM, you D O N O T
>have RI." Every other method is simply something close to RI that will
>eventually fail.
>HTH,
>Rick Davis
>"This e-mail and any files transmitted with it are the property of SBC,
>are confidential, and are intended solely for the use of the individual
>or entity to whom this e-mail is addressed. If you are not one of the
>named recipient(s) or otherwise have reason to believe that you have
>received this message in error, please notify the sender at 314-235-6854
>and delete this message immediately from your computer. Any other use,
>retention, dissemination, forwarding, printing, or copying of this
>e-mail is strictly prohibited."
>
>
>
>-----Original Message-----
>From: madhavan [mailto:[login to unmask email]
>Sent: Monday, January 24, 2000 6:04 AM
>To: [login to unmask email]
>Subject: DB2 referential integrity
>
>
>Hi,
>
>One of our customer's shop standard is that DB2 referential integrity will
>not be used in the normal cases. Referential integrity will be maintained
>through program logic. I was also told that 90% of the DB2 shops do not use
>DB2 referential integrity due to performance reasons.
>
>I thought referential integrity through DB2 will be performance wise better
>than checking it through the program. Also unless we do a fetch for update,
>there is always a chance of loosing the integrity of data when we do
>referential check in the programs.
>
>Can you please give me your experiences on this?
>
>regards
>Madhavan.
>
>
>
>the
>
>
>
>
>
>
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Craig Mullins

Re: DB2 referential integrity
(in response to craig patton)
Although I agree with the general sentiment expressed by others (if the RI
is not expressed as declarative constraints in DB2 then the RI is not 100
per cent guaranteed), I have to express a few exceptions to the general
rule.

DB2 does a referential integrity check for every row insertion. You can
increase efficiency if your application does a single check of a row from
the parent table and then makes multiple inserts to the child table. This
should not be the sole determining factor for implementing RI or not,
though, because program logic can fail (bugs, errors, etc.) or be different
from program to program; and ad hoc modifications will not be checked.
If the application processing needs are such that the parent table is read
before even one child is inserted, consider not implementing DB2 RI. In this
case, DB2 would repeat the read process that the application must do anyway
to satisfy its processing needs. Same caveats as above though.
Do not use DB2 RI on tables built from another system that already is
referentially intact. If the tables are updated after being built or loaded
from the external data source, consider building the RI into the application
code where appropriate and ignoring the RI when building or updating the
tables from the referentially intact source.
Do not use DB2 RI if tables are read only. If you need to scrub the data
when loading, you still may want to use DB2 RI.
Define a primary (or unique) key to prohibit duplicate table rows. This
should be done to ensure entity integrity regardless of whether dependent
tables are related to the table being defined. Entity integrity ensures that
each row in a table represents a single, real-world entity.
Avoid large referential sets. Try not to tie together all tables in a large
system; otherwise, recovery, quiesce, and other utility processing can be
difficult to develop and administer. For this reason, consider not using
referential constraints to tie lookup, code, and domain tables to the main
tables in your database. These tables are typically "low update" and not
done via ad hoc SQL.
Another issue to deal with is that not every RI need is covered by DB2
declarative constraints. For example, there is no ON UPDATE CASCADE
capability. And you can not define a constraint such that when the last
child row is deleted for a parent row, the parent row should be deleted, too
(this is called pendant delete). As of DB2 V6, you can code triggers to
enforce these type of RI rules, though. Like declarative RI, triggers are
non-bypassable, which is good. But the down side is that you must code the
logic in the triggers yourself (which can be prone to bugs).
Good Luck,
Craig S. Mullins
Director, DB2 Technology Planning
BMC Software
http://www.bmc.com
[login to unmask email]
http://www.craigsmullins.com
[login to unmask email]

>-----Original Message-----
>From: madhavan [mailto:[login to unmask email]
>Sent: Monday, January 24, 2000 6:04 AM
>To: [login to unmask email]
>Subject: DB2 referential integrity
>
>Hi,
>
>One of our customer's shop standard is that DB2 referential integrity will
>not be used in the normal cases. Referential integrity will be maintained
>through program logic. I was also told that 90% of the DB2 shops do not use
>DB2 referential integrity due to performance reasons.
>
>I thought referential integrity through DB2 will be performance wise better
>than checking it through the program. Also unless we do a fetch for update,
>there is always a chance of loosing the integrity of data when we do
>referential check in the programs.
>
>Can you please give me your experiences on this?
>
>regards
>Madhavan.
>



Jim Drewe

DB2 referential integrity
(in response to Craig Mullins)
Madhavan

DB2 RI discussions can become quasi-religious
debates. I will share my experiences, but you
will have to make up your own mind as to what is
best for your physical design and your installation ,s
standards. Again, like Richard Yevich pointed out,
there is declarative RI, as well as application RI.
It is never a question of whether there will be RI,
but where is it better suited?

For the most part, our shop does not use RI. Like
another Listserver commented, we too, use only static
SQL on production OLTP. If a shop can enforce that
standard, then I would consider not using RI. But
that is a big &if 8 because I know the pressure of the
business coming down on technologies to open up
production data for ad hoc access. So far we have
not budged.

Assuming this is your installation ,s situation, then
you will need to assess the pro ,s and con ,s of RI.
For instance, will you application be inserting a
large number of child table rows with the same
parent key? If so, DB2 will have to perform a
needless check to see validate the existence of the
parent value. Will your application be deleting
parent table rows in which there are no corresponding
child table rows? Again, DB2 will have to check
needlessly if you have a child table row. Also, you
typically have indexes on the foreign keys. This
is an additional overhead for inserts, deletes, and
updates if they don ,t pertain to RI constraints.

On the other hand, routine inserts, updates, and
deletes that involve RI integrity can be done by
DB2 somewhat more efficiently than by an application
program.

Jim Drewe
DBA

====
To: [login to unmask email]
cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
Subject: DB2 referential integrity

Hi,

One of our customer's shop standard is that DB2 referential integrity will
not be used in the normal cases. Referential integrity will be maintained
through program logic. I was also told that 90% of the DB2 shops do not use
DB2 referential integrity due to performance reasons.

I thought referential integrity through DB2 will be performance wise better
than checking it through the program. Also unless we do a fetch for update,
there is always a chance of loosing the integrity of data when we do
referential check in the programs.

Can you please give me your experiences on this?

regards
Madhavan.



RICK (SWBT) DAVIS

Re: DB2 referential integrity
(in response to Jim Drewe)
Jim,
Appreciate your comments and could probably safely say that every
DBA has struggled with this issue. I agree that this subject can seem to be
quasi-religious. However, you'll need a God of some type to protect your job
if you rely on any form of RI that isn't enforced at the DB level. You had
better spell out those RI forms that can't be enforced at the DB level, or
application enforced RI that was chosen for whatever "quasi-religious"
reason and the exposure to loss of data integrity it implies. In this
manner, you can tell them there were known risks and fall back on them when
things go bad and they start hunting down the perpetrators of this
"quasi-religious" blasphemy.

;~)
Regards,
Rick Davis
"This e-mail and any files transmitted with it are the property of SBC,
are confidential, and are intended solely for the use of the individual
or entity to whom this e-mail is addressed. If you are not one of the
named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender at 314-235-6854
and delete this message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."



-----Original Message-----
From: James Drewe [mailto:[login to unmask email]
Sent: Tuesday, January 25, 2000 1:58 PM
To: [login to unmask email]
Subject: DB2 referential integrity


Madhavan

DB2 RI discussions can become quasi-religious
debates. I will share my experiences, but you
will have to make up your own mind as to what is
best for your physical design and your installation ,s
standards. Again, like Richard Yevich pointed out,
there is declarative RI, as well as application RI.
It is never a question of whether there will be RI,
but where is it better suited?

For the most part, our shop does not use RI. Like
another Listserver commented, we too, use only static
SQL on production OLTP. If a shop can enforce that
standard, then I would consider not using RI. But
that is a big &if 8 because I know the pressure of the
business coming down on technologies to open up
production data for ad hoc access. So far we have
not budged.

Assuming this is your installation ,s situation, then
you will need to assess the pro ,s and con ,s of RI.
For instance, will you application be inserting a
large number of child table rows with the same
parent key? If so, DB2 will have to perform a
needless check to see validate the existence of the
parent value. Will your application be deleting
parent table rows in which there are no corresponding
child table rows? Again, DB2 will have to check
needlessly if you have a child table row. Also, you
typically have indexes on the foreign keys. This
is an additional overhead for inserts, deletes, and
updates if they don ,t pertain to RI constraints.

On the other hand, routine inserts, updates, and
deletes that involve RI integrity can be done by
DB2 somewhat more efficiently than by an application
program.

Jim Drewe
DBA

====
To: [login to unmask email]
cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
Subject: DB2 referential integrity

Hi,

One of our customer's shop standard is that DB2 referential integrity will
not be used in the normal cases. Referential integrity will be maintained
through program logic. I was also told that 90% of the DB2 shops do not use
DB2 referential integrity due to performance reasons.

I thought referential integrity through DB2 will be performance wise better
than checking it through the program. Also unless we do a fetch for update,
there is always a chance of loosing the integrity of data when we do
referential check in the programs.

Can you please give me your experiences on this?

regards
Madhavan.








Jim Drewe

Re: DB2 referential integrity
(in response to RICK (SWBT) DAVIS)
Rick

As far betting my career when using application RI, I will have to turn to
Dilbert, my chief advisor: "if you had my career, you would bet it as well".
(My apologies to those of you in other countries that don ,t receive Dilbert
cartoons.)

There really is no 100% guarantee against data integrity risk -- whether you
use DBMS or application RI. Recovery or load operations with DBMS RI also can
go awry. As Craig Mullins pointed out as well, declarative RI does not cover
RI requirements for cascading updates; this has to be done via triggers or the
application. Risk avoidance, therefore, is not an absolute term.

The caveat I mentioned previously regarding an RI decision is that there can
be no ad hoc update to production data. Assuming application code is truly
tested prior to going into production, then a DBA has an opportunity to
consider not using declarative RI. On one application I am working on we are
doing many inserts into a child table (eventually over 500 rows per second)
where the parent key is the same. I would be doubling the I/O activity in DB2
for a needless check to the parent if I used declarative RI. I would like to
think performance has to play a role in a DBA's physical design when
considering RI.

Like I said, the subject of RI can take on a religious fervor. For those of
you that are still weighing application versus declarative RI, I hope my
thoughts can help you make a reasonable decision.

Jim Drewe
DBA

====

Date: Wed, 26 Jan 2000 06:51:49 -0600
From: "DAVIS, RICK (SBCSI)" <[login to unmask email]>
Subject: Re: DB2 referential integrity

Jim,
Appreciate your comments and could probably safely say that every
DBA has struggled with this issue. I agree that this subject can seem to be
quasi-religious. However, you'll need a God of some type to protect your job
if you rely on any form of RI that isn't enforced at the DB level. You had
better spell out those RI forms that can't be enforced at the DB level, or
application enforced RI that was chosen for whatever "quasi-religious"
reason and the exposure to loss of data integrity it implies. In this
manner, you can tell them there were known risks and fall back on them when
things go bad and they start hunting down the perpetrators of this
"quasi-religious" blasphemy.

;~)
Regards,
Rick Davis
"This e-mail and any files transmitted with it are the property of SBC,
are confidential, and are intended solely for the use of the individual
or entity to whom this e-mail is addressed. If you are not one of the
named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender at 314-235-6854
and delete this message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."

====

-----Original Message-----
From: James Drewe [mailto:[login to unmask email]
Sent: Tuesday, January 25, 2000 1:58 PM
To: [login to unmask email]
Subject: DB2 referential integrity

Madhavan

DB2 RI discussions can become quasi-religious debates. I will share my
experiences, but you will have to make up your own mind as to what is best for
your physical design and your installation ,s standards. Again, like Richard
Yevich pointed out, there is declarative RI, as well as application RI. It
is never a question of whether there will be RI, but where is it better
suited?

For the most part, our shop does not use declarative RI. Like another
Listserver commented, we too, use only static SQL on production OLTP. If a
shop can enforce that standard, then I would consider not using RI. But that
is a big &if 8 because I know the pressure of the business coming down on
technologies to open up production data for ad hoc access. So far we have not
budged.

Assuming this is your installation ,s situation, then you will need to assess
the pros and cons of RI.
For instance, will your application be inserting a large number of child table
rows with the same
parent key? If so, DB2 will have to perform a needless check to see validate
the existence of the
parent value. Will your application be deleting parent table rows in which
there are no corresponding
child table rows? Again, DB2 will have to check needlessly if you have a
child table row. Also, you
typically have indexes on the foreign keys. This is an additional overhead
for inserts, deletes, and
updates if they don ,t pertain to RI constraints.

On the other hand, routine inserts, updates, and deletes that involve RI
integrity can be done by
DB2 somewhat more efficiently than by an application program.

Jim Drewe
DBA

====
To: [login to unmask email]
cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
Subject: DB2 referential integrity

Hi,

One of our customer's shop standard is that DB2 referential integrity will
not be used in the normal cases. Referential integrity will be maintained
through program logic. I was also told that 90% of the DB2 shops do not use
DB2 referential integrity due to performance reasons.

I thought referential integrity through DB2 will be performance wise better
than checking it through the program. Also unless we do a fetch for update,
there is always a chance of loosing the integrity of data when we do
referential check in the programs.

Can you please give me your experiences on this?

regards
Madhavan.





RICK (SWBT) DAVIS

Re: DB2 referential integrity
(in response to Jim Drewe)
Jim,
You've given me a gift beyond all price, inspired me, if you will.

I'm getting out of this dumb business and getting into writing Dilbert
cartoons.

You ARE the Man,
Rick Davis
"This e-mail and any files transmitted with it are the property of SBC,
are confidential, and are intended solely for the use of the individual
or entity to whom this e-mail is addressed. If you are not one of the
named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender at 314-235-6854
and delete this message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."



-----Original Message-----
From: James Drewe [mailto:[login to unmask email]
Sent: Thursday, January 27, 2000 11:00 AM
To: [login to unmask email]
Subject: Re: DB2 referential integrity


Rick

As far betting my career when using application RI, I will have to turn to
Dilbert, my chief advisor: "if you had my career, you would bet it as well".
(My apologies to those of you in other countries that don ,t receive Dilbert
cartoons.)

There really is no 100% guarantee against data integrity risk -- whether you
use DBMS or application RI. Recovery or load operations with DBMS RI also
can
go awry. As Craig Mullins pointed out as well, declarative RI does not
cover
RI requirements for cascading updates; this has to be done via triggers or
the
application. Risk avoidance, therefore, is not an absolute term.

The caveat I mentioned previously regarding an RI decision is that there can
be no ad hoc update to production data. Assuming application code is truly
tested prior to going into production, then a DBA has an opportunity to
consider not using declarative RI. On one application I am working on we
are
doing many inserts into a child table (eventually over 500 rows per second)
where the parent key is the same. I would be doubling the I/O activity in
DB2
for a needless check to the parent if I used declarative RI. I would like
to
think performance has to play a role in a DBA's physical design when
considering RI.

Like I said, the subject of RI can take on a religious fervor. For those of
you that are still weighing application versus declarative RI, I hope my
thoughts can help you make a reasonable decision.

Jim Drewe
DBA

====

Date: Wed, 26 Jan 2000 06:51:49 -0600
From: "DAVIS, RICK (SBCSI)" <[login to unmask email]>
Subject: Re: DB2 referential integrity

Jim,
Appreciate your comments and could probably safely say that every
DBA has struggled with this issue. I agree that this subject can seem to be
quasi-religious. However, you'll need a God of some type to protect your job
if you rely on any form of RI that isn't enforced at the DB level. You had
better spell out those RI forms that can't be enforced at the DB level, or
application enforced RI that was chosen for whatever "quasi-religious"
reason and the exposure to loss of data integrity it implies. In this
manner, you can tell them there were known risks and fall back on them when
things go bad and they start hunting down the perpetrators of this
"quasi-religious" blasphemy.

;~)
Regards,
Rick Davis
"This e-mail and any files transmitted with it are the property of SBC,
are confidential, and are intended solely for the use of the individual
or entity to whom this e-mail is addressed. If you are not one of the
named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender at 314-235-6854
and delete this message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."

====

-----Original Message-----
From: James Drewe [mailto:[login to unmask email]
Sent: Tuesday, January 25, 2000 1:58 PM
To: [login to unmask email]
Subject: DB2 referential integrity

Madhavan

DB2 RI discussions can become quasi-religious debates. I will share my
experiences, but you will have to make up your own mind as to what is best
for
your physical design and your installation ,s standards. Again, like
Richard
Yevich pointed out, there is declarative RI, as well as application RI. It
is never a question of whether there will be RI, but where is it better
suited?

For the most part, our shop does not use declarative RI. Like another
Listserver commented, we too, use only static SQL on production OLTP. If a
shop can enforce that standard, then I would consider not using RI. But
that
is a big &if 8 because I know the pressure of the business coming down on
technologies to open up production data for ad hoc access. So far we have
not
budged.

Assuming this is your installation ,s situation, then you will need to
assess
the pros and cons of RI.
For instance, will your application be inserting a large number of child
table
rows with the same
parent key? If so, DB2 will have to perform a needless check to see
validate
the existence of the
parent value. Will your application be deleting parent table rows in which
there are no corresponding
child table rows? Again, DB2 will have to check needlessly if you have a
child table row. Also, you
typically have indexes on the foreign keys. This is an additional overhead
for inserts, deletes, and
updates if they don ,t pertain to RI constraints.

On the other hand, routine inserts, updates, and deletes that involve RI
integrity can be done by
DB2 somewhat more efficiently than by an application program.

Jim Drewe
DBA

====
To: [login to unmask email]
cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
Subject: DB2 referential integrity

Hi,

One of our customer's shop standard is that DB2 referential integrity will
not be used in the normal cases. Referential integrity will be maintained
through program logic. I was also told that 90% of the DB2 shops do not use
DB2 referential integrity due to performance reasons.

I thought referential integrity through DB2 will be performance wise better
than checking it through the program. Also unless we do a fetch for update,
there is always a chance of loosing the integrity of data when we do
referential check in the programs.

Can you please give me your experiences on this?

regards
Madhavan.










David A. Zelmer

DB2 Referential Integrity
(in response to RICK (SWBT) DAVIS)
Listers,

In looking at RI, I have a question regarding the removal and then
subsequent reactivation of RI. Do the application packages need to be
rebound if RI is removed and later added back?

The reason for this is due to third party products at a "hot-site"
backup. In order to replicate DB2 data we can not have RI defined at
the shadow site. During DR, we would have to activate RI. After the
DR, we would need to remove RI. I am wondering the implications to the
application packages when this occurs.

Thanks for any assistance you can provide,

Dave Zelmer
Citibank



Rob Crane

Re: DB2 Referential Integrity
(in response to David A. Zelmer)
No, the database manager handles RI not the packages.

"David A. Zelmer" wrote:
>
> Listers,
>
> In looking at RI, I have a question regarding the removal and then
> subsequent reactivation of RI. Do the application packages need to be
> rebound if RI is removed and later added back?
>
> The reason for this is due to third party products at a "hot-site"
> backup. In order to replicate DB2 data we can not have RI defined at
> the shadow site. During DR, we would have to activate RI. After the
> DR, we would need to remove RI. I am wondering the implications to the
> application packages when this occurs.
>
> Thanks for any assistance you can provide,
>
> Dave Zelmer
> Citibank
>
>
>



Joseph Link

Re: DB2 Referential Integrity
(in response to Rob Crane)
Actually, there is one RI condition that will invalidate plans and
packages, which bit me once before. Quoting the SQL Reference:

"When a referential constraint is defined with a delete rule of CASCADE or
SET NULL, all plans and packages that refer to the parent table of the
constraint are invalidated. Furthermore, all plans and packages that
refer to tables from which deletes cascade are also invalidated."

So, if you add the RI back on at your DR site, and you have delete rules of
CASCADE or SET NULL, there may be some plans and packages that need to be
rebound.

Joe Link
DBA - FDR





Rob Crane
<[login to unmask email] To: [login to unmask email]
ntric.net> cc:
Sent by: DB2 Subject: Re: DB2 Referential Integrity
Data Base
Discussion
List
<[login to unmask email]
M>


12/14/2000
05:46 PM
Please respond
to DB2 Data
Base
Discussion
List





No, the database manager handles RI not the packages.

"David A. Zelmer" wrote:
>
> Listers,
>
> In looking at RI, I have a question regarding the removal and then
> subsequent reactivation of RI. Do the application packages need to be
> rebound if RI is removed and later added back?
>
> The reason for this is due to third party products at a "hot-site"
> backup. In order to replicate DB2 data we can not have RI defined at
> the shadow site. During DR, we would have to activate RI. After the
> DR, we would need to remove RI. I am wondering the implications to the
> application packages when this occurs.
>
> Thanks for any assistance you can provide,
>
> Dave Zelmer
> Citibank
>
>
>










Scott Trometer

Re: DB2 Referential Integrity
(in response to Joseph Link)
Depending on the DB2 version, shouldn't these plans be rebound at runtime
via the default DSNZPARM (ABIND)?



-----Original Message-----
From: Joseph Link [mailto:[login to unmask email]
Sent: Friday, December 15, 2000 10:35 AM
To: [login to unmask email]
Subject: Re: DB2 Referential Integrity


Actually, there is one RI condition that will invalidate plans and
packages, which bit me once before. Quoting the SQL Reference:

"When a referential constraint is defined with a delete rule of CASCADE or
SET NULL, all plans and packages that refer to the parent table of the
constraint are invalidated. Furthermore, all plans and packages that
refer to tables from which deletes cascade are also invalidated."

So, if you add the RI back on at your DR site, and you have delete rules of
CASCADE or SET NULL, there may be some plans and packages that need to be
rebound.

Joe Link
DBA - FDR





Rob Crane
<[login to unmask email] To: [login to unmask email]
ntric.net> cc:
Sent by: DB2 Subject: Re: DB2 Referential
Integrity
Data Base
Discussion
List
<[login to unmask email]
M>


12/14/2000
05:46 PM
Please respond
to DB2 Data
Base
Discussion
List





No, the database manager handles RI not the packages.

"David A. Zelmer" wrote:
>
> Listers,
>
> In looking at RI, I have a question regarding the removal and then
> subsequent reactivation of RI. Do the application packages need to be
> rebound if RI is removed and later added back?
>
> The reason for this is due to third party products at a "hot-site"
> backup. In order to replicate DB2 data we can not have RI defined at
> the shadow site. During DR, we would have to activate RI. After the
> DR, we would need to remove RI. I am wondering the implications to the
> application packages when this occurs.
>
> Thanks for any assistance you can provide,
>
> Dave Zelmer
> Citibank
>
>
>















Joseph Link

Re: DB2 Referential Integrity
(in response to Scott Trometer)
Yes, the plans and packages will auto-rebind the first time they are
executed. The mistake I made was to alter the table in the middle of the
day, which caused several hundred online packages to attempt to auto-rebind
all at once. Aside from the peformance hit, many of the rebinds failed
with contention on the catalog causing the transactions to fail.




Scott Trometer
<scott.tromete To: [login to unmask email]
[login to unmask email]> cc:
Sent by: DB2 Subject: Re: DB2 Referential Integrity
Data Base
Discussion
List
<[login to unmask email]
M>


12/15/2000
10:05 AM
Please respond
to DB2 Data
Base
Discussion
List





Depending on the DB2 version, shouldn't these plans be rebound at runtime
via the default DSNZPARM (ABIND)?



-----Original Message-----
From: Joseph Link [mailto:[login to unmask email]
Sent: Friday, December 15, 2000 10:35 AM
To: [login to unmask email]
Subject: Re: DB2 Referential Integrity


Actually, there is one RI condition that will invalidate plans and
packages, which bit me once before. Quoting the SQL Reference:

"When a referential constraint is defined with a delete rule of CASCADE or
SET NULL, all plans and packages that refer to the parent table of the
constraint are invalidated. Furthermore, all plans and packages that
refer to tables from which deletes cascade are also invalidated."

So, if you add the RI back on at your DR site, and you have delete rules of
CASCADE or SET NULL, there may be some plans and packages that need to be
rebound.

Joe Link
DBA - FDR





Rob Crane
<[login to unmask email] To: [login to unmask email]
ntric.net> cc:
Sent by: DB2 Subject: Re: DB2 Referential
Integrity
Data Base
Discussion
List
<[login to unmask email]
M>


12/14/2000
05:46 PM
Please respond
to DB2 Data
Base
Discussion
List





No, the database manager handles RI not the packages.

"David A. Zelmer" wrote:
>
> Listers,
>
> In looking at RI, I have a question regarding the removal and then
> subsequent reactivation of RI. Do the application packages need to be
> rebound if RI is removed and later added back?
>
> The reason for this is due to third party products at a "hot-site"
> backup. In order to replicate DB2 data we can not have RI defined at
> the shadow site. During DR, we would have to activate RI. After the
> DR, we would need to remove RI. I am wondering the implications to the
> application packages when this occurs.
>
> Thanks for any assistance you can provide,
>
> Dave Zelmer
> Citibank
>
>
>










the