DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE

Mark Fleming

DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE
I haven't used DB2-enforced RI much. I'm hoping you can give me some
insights.

Here's the scenario. I have a parent table and 4 child tables with keys
to the parent table. Each FK is set up similar to this:

ALTER TABLE DB2PROD.child_tab_1 FOREIGN KEY fkey1
(IN4_col1 ,
IN4_col2 ,
IN4_col3 )
REFERENCES DB2PROD.parent_tab
(IN5_col1 ,
IN5_col2 ,
IN5_col3 )
ON DELETE CASCADE

Is there any benefit to deleting the child rows before deleting the
corresponding parent row? Or is it better to delete the parent row and
let the deletes cascade to the child rows by DB2? The application is
doing the first option, deleting the child rows first followed by the
parent. They're getting really *really* slow response when they delete
the parent row, though. All the deletes are in the same commit scope.
Your thoughts?

Mark Fleming
Database Administration
847-402-3889

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Shery hepp

Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE
(in response to Mark Fleming)
Hi Mark- do you have indexes defined on the FK's in the child tables to
support index access for the db2 delete?

Our standard has always been to defined delete restrict or delete set
null. Mostly I think to give exposure to what exactly on the child is
being deleted. I have never used delete with cascade so can't really
comment on it one way or the other. I would make sure you have indexes
defined. Especially if the child tables are large.

Hope this helps. Regards, Shery

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Fleming, Mark
Sent: Wednesday, January 16, 2008 1:39 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS v8 -- Referential Integrity question when
using ON DELETE CASCADE

I haven't used DB2-enforced RI much. I'm hoping you can give me some
insights.

Here's the scenario. I have a parent table and 4 child tables with keys
to the parent table. Each FK is set up similar to this:

ALTER TABLE DB2PROD.child_tab_1 FOREIGN KEY fkey1
(IN4_col1 ,
IN4_col2 ,
IN4_col3 )
REFERENCES DB2PROD.parent_tab
(IN5_col1 ,
IN5_col2 ,
IN5_col3 )
ON DELETE CASCADE

Is there any benefit to deleting the child rows before deleting the
corresponding parent row? Or is it better to delete the parent row and
let the deletes cascade to the child rows by DB2? The application is
doing the first option, deleting the child rows first followed by the
parent. They're getting really *really* slow response when they delete
the parent row, though. All the deletes are in the same commit scope.
Your thoughts?

Mark Fleming
Database Administration
847-402-3889

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Philip Sevetson

Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE
(in response to Shery hepp)
From a strictly performance standpoint, you save a DB2 call by DELETEing
the parent table, as the whole thing is accomplished with one DB2 call.


However, I don't know anyone who's doing ON DELETE CASCADE in
production; most people are very concerned with the consequences of
deleting a parent and accidentally getting the child data as well.

I'm not sure why you'd let anyone who didn't understand the data's
parent-child relationships, excuse me, "foreign keys", anywhere near
your data in the first place, so this has always seemed to me like
excessive worrying.

--Phil Sevetson, NYCAPS DBA Support
Financial Information Services Agency of The City of New York
450 West 33rd Street, 4th Floor
New York, NY 10001
phone: (212) 857-1688
mailto: [login to unmask email]
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Fleming, Mark
Sent: Wednesday, January 16, 2008 3:39 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS v8 -- Referential Integrity question when
using ON DELETE CASCADE

I haven't used DB2-enforced RI much. I'm hoping you can give me some
insights.

Here's the scenario. I have a parent table and 4 child tables with keys
to the parent table. Each FK is set up similar to this:

ALTER TABLE DB2PROD.child_tab_1 FOREIGN KEY fkey1
(IN4_col1 ,
IN4_col2 ,
IN4_col3 )
REFERENCES DB2PROD.parent_tab
(IN5_col1 ,
IN5_col2 ,
IN5_col3 )
ON DELETE CASCADE

Is there any benefit to deleting the child rows before deleting the
corresponding parent row? Or is it better to delete the parent row and
let the deletes cascade to the child rows by DB2? The application is
doing the first option, deleting the child rows first followed by the
parent. They're getting really *really* slow response when they delete
the parent row, though. All the deletes are in the same commit scope.
Your thoughts?

Mark Fleming
Database Administration
847-402-3889

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms


=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Philip Sevetson

Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE
(in response to David Simpson)
Oh, as Sherry has noted, slow response time from deleting the parent
_may_ be a signal of missing or disorganized Foreign Key indexes.

--Phil Sevetson, NYCAPS DBA Support
Financial Information Services Agency of The City of New York
450 West 33rd Street, 4th Floor
New York, NY 10001
phone: (212) 857-1688
mailto: [login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Fleming, Mark
Sent: Wednesday, January 16, 2008 3:39 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS v8 -- Referential Integrity question when
using ON DELETE CASCADE

I haven't used DB2-enforced RI much. I'm hoping you can give me some
insights.

Here's the scenario. I have a parent table and 4 child tables with keys
to the parent table. Each FK is set up similar to this:

ALTER TABLE DB2PROD.child_tab_1 FOREIGN KEY fkey1
(IN4_col1 ,
IN4_col2 ,
IN4_col3 )
REFERENCES DB2PROD.parent_tab
(IN5_col1 ,
IN5_col2 ,
IN5_col3 )
ON DELETE CASCADE

Is there any benefit to deleting the child rows before deleting the
corresponding parent row? Or is it better to delete the parent row and
let the deletes cascade to the child rows by DB2? The application is
doing the first option, deleting the child rows first followed by the
parent. They're getting really *really* slow response when they delete
the parent row, though. All the deletes are in the same commit scope.
Your thoughts?

Mark Fleming
Database Administration
847-402-3889

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms


=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

David Simpson

Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE
(in response to Philip Sevetson)
If you REALLY want this behavior and everyone REALLY understands the nature of the cascade, and you have appropriate indexes to support the RI, then I prefer to do the cascade. Fewer API calls usually = better performance.

David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com

________________________________

From: DB2 Data Base Discussion List on behalf of Hepp Shery C
Sent: Wed 1/16/2008 2:49 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE



Hi Mark- do you have indexes defined on the FK's in the child tables to
support index access for the db2 delete?

Our standard has always been to defined delete restrict or delete set
null. Mostly I think to give exposure to what exactly on the child is
being deleted. I have never used delete with cascade so can't really
comment on it one way or the other. I would make sure you have indexes
defined. Especially if the child tables are large.

Hope this helps. Regards, Shery

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Fleming, Mark
Sent: Wednesday, January 16, 2008 1:39 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS v8 -- Referential Integrity question when
using ON DELETE CASCADE

I haven't used DB2-enforced RI much. I'm hoping you can give me some
insights.

Here's the scenario. I have a parent table and 4 child tables with keys
to the parent table. Each FK is set up similar to this:

ALTER TABLE DB2PROD.child_tab_1 FOREIGN KEY fkey1
(IN4_col1 ,
IN4_col2 ,
IN4_col3 )
REFERENCES DB2PROD.parent_tab
(IN5_col1 ,
IN5_col2 ,
IN5_col3 )
ON DELETE CASCADE

Is there any benefit to deleting the child rows before deleting the
corresponding parent row? Or is it better to delete the parent row and
let the deletes cascade to the child rows by DB2? The application is
doing the first option, deleting the child rows first followed by the
parent. They're getting really *really* slow response when they delete
the parent row, though. All the deletes are in the same commit scope.
Your thoughts?

Mark Fleming
Database Administration
847-402-3889

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms



The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

John Amsden

Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE
(in response to Philip Sevetson)
We have used delete cascade regularly for many years. We have had no
issues *** as long as we created indexes to support the delete ***.

The scenario you described (deleting the children and then still
getting slow response on the parent delete) would be expected if
you do not have the indexes to support the delete as the delete
process will still look (tablespace scan) for children whether
there are children rows present or not.

John Amsden - Lincoln Financial Group

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Hepp Shery C
Sent: Wednesday, January 16, 2008 3:49 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS v8 -- Referential Integrity question
when using ON DELETE CASCADE


Hi Mark- do you have indexes defined on the FK's in the child tables to
support index access for the db2 delete?

Our standard has always been to defined delete restrict or delete set
null. Mostly I think to give exposure to what exactly on the child is
being deleted. I have never used delete with cascade so can't really
comment on it one way or the other. I would make sure you have indexes
defined. Especially if the child tables are large.

Hope this helps. Regards, Shery

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Fleming, Mark
Sent: Wednesday, January 16, 2008 1:39 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS v8 -- Referential Integrity question when
using ON DELETE CASCADE

I haven't used DB2-enforced RI much. I'm hoping you can give me some
insights.

Here's the scenario. I have a parent table and 4 child tables with keys
to the parent table. Each FK is set up similar to this:

ALTER TABLE DB2PROD.child_tab_1 FOREIGN KEY fkey1
(IN4_col1 ,
IN4_col2 ,
IN4_col3 )
REFERENCES DB2PROD.parent_tab
(IN5_col1 ,
IN5_col2 ,
IN5_col3 )
ON DELETE CASCADE

Is there any benefit to deleting the child rows before deleting the
corresponding parent row? Or is it better to delete the parent row and
let the deletes cascade to the child rows by DB2? The application is
doing the first option, deleting the child rows first followed by the
parent. They're getting really *really* slow response when they delete
the parent row, though. All the deletes are in the same commit scope.
Your thoughts?

Mark Fleming
Database Administration
847-402-3889

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms




Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged, confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or entity to
which it is addressed. If you are not the intended recipient of this E-mail, you are
hereby notified that any dissemination, distribution, copying, or action taken in
relation to the contents of and attachments to this E-mail is strictly prohibited
and may be unlawful. If you have received this E-mail in error, please notify the
sender immediately and permanently delete the original and any copy of this E-mail
and any printout. Thank You.**

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mark Fleming

Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE
(in response to John Amsden)
Great responses everyone. Thanks.

Yes, there are indexes on all the foreign keys on each of the dependant
tables, however, in some cases there are additional columns after the
first two columns. In other words, the FK consists of (col1, col2, col2)
but the IX on a dependent table is (col1, col2, col4, col5, col3). So
there is an IX that can be used... not exactly optimal, but it is there.
I didn't set this up, I just inherited it.

The app says it takes minutes to complete a delete; so my next step is
to run a trace and verify what comes into play. I do not think this is
contention on the dependent tables, either. I won't rule that out, but I
think concurrent activity is low to nil.

Thanks again for everyone's input so far.

Mark Fleming
Database Administration
847-402-3889


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Hepp Shery C
Sent: Wednesday, January 16, 2008 2:49 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS v8 -- Referential Integrity question
when using ON DELETE CASCADE

Hi Mark- do you have indexes defined on the FK's in the child tables to
support index access for the db2 delete?

Our standard has always been to defined delete restrict or delete set
null. Mostly I think to give exposure to what exactly on the child is
being deleted. I have never used delete with cascade so can't really
comment on it one way or the other. I would make sure you have indexes
defined. Especially if the child tables are large.

Hope this helps. Regards, Shery

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mark Labby

Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE
(in response to Mark Fleming)
If I follow your response, I think the indices are the issue.

the FK consists of (col1, col2, col2) but the IX on a dependent table is
(col1, col2, col4, col5, col3)

Assuming you mean col3 in the FK, then DB2 will not use this index on the
dependant table for RI checking. You must match exactly the full key of
the parent as the high order columns on the child. The index on the child
can have the extra col4 and col5, but they cannot come until AFTER the
full parent columns match.

For everything else, you are right, the index is not optimal but it would
have a 2 column match and do an index scan. For RI checking, it either
matches all columns on the parent or it is ignored. This has been one of
my pet peeves for years and we have had to create extra indices for just
this situation where I have a resolution table between to large tables.
Col1 and Col2 can have fantastic cardinality and the 2 column match gets
great access performance, but we ended up creating an NPI just to have the
col1, col2, col3 match for RI checking.

I think there have been requests submitted to have DB2 use an index scan
on a partial key match for RI checking as it does with everything else,
but I do not know the number of the request. You may want to contact your
IBM rep and add your input to getting this requirement moved up the
priority list...





"Fleming, Mark" <[login to unmask email]>
Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>
01/16/2008 05:35 PM
Please respond to
"DB2 Database Discussion list at IDUG" <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE
CASCADE






Great responses everyone. Thanks.

Yes, there are indexes on all the foreign keys on each of the dependant
tables, however, in some cases there are additional columns after the
first two columns. In other words, the FK consists of (col1, col2, col2)
but the IX on a dependent table is (col1, col2, col4, col5, col3). So
there is an IX that can be used... not exactly optimal, but it is there.
I didn't set this up, I just inherited it.

The app says it takes minutes to complete a delete; so my next step is
to run a trace and verify what comes into play. I do not think this is
contention on the dependent tables, either. I won't rule that out, but I
think concurrent activity is low to nil.

Thanks again for everyone's input so far.

Mark Fleming
Database Administration
847-402-3889


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Hepp Shery C
Sent: Wednesday, January 16, 2008 2:49 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS v8 -- Referential Integrity question
when using ON DELETE CASCADE

Hi Mark- do you have indexes defined on the FK's in the child tables to
support index access for the db2 delete?

Our standard has always been to defined delete restrict or delete set
null. Mostly I think to give exposure to what exactly on the child is
being deleted. I have never used delete with cascade so can't really
comment on it one way or the other. I would make sure you have indexes
defined. Especially if the child tables are large.

Hope this helps. Regards, Shery





=====
This message contains privileged and confidential information intended for the above addressees only. If you
receive this message in error please delete or destroy this message and/or attachments.

The sender of this message will fully cooperate in the civil and criminal prosecution of any individual engaging
in the unauthorized use of this message.
=====

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Troy Coleman

Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE
(in response to Mark Labby)
Hi Mark,
You will want to make sure you have indexes that have the same columns
as the primary key of the parent table. If you only have a few of the
first columns and not all the column then you are going to have to do
data access. So you have more I/O for scanning the index and then I/O
to scan data.

As for rules on when to use DELETE CASCADE versus DELETE RESTRICT.
I would use DELETE CASCADE only if the result of the cascade is a small
enough unit of work that would not cause lock escalation and can
complete within a minute or two. The general reason for using DELETE
RESTRICT is to support control of the COMMIT/ROLLBACK. With RESTRICT
the programmer should open a cursor on the child table and perform
delete where current of the cursor. You would then do a commit after
every "n" number of rows or "t" amount of time. Once all the rows are
deleted from all the child tables you would process the parent table
and delete all the parent rows.

Of course the easy way out of this is to use DELETE CASCADE and hope for
the best. To keep from having a potential program most DBA's will
enforce the DELETE RESTRICT so you have to handle the child rows first.

I hope this helps.

Troy Coleman, Support Engineer
IBM Certified Solutions Expert

SoftBase Systems, Inc.
847-776-0618
828-670-9900 ext. 334
[login to unmask email]

Compliance Challenged with Test Data Privacy? White Papers and More at http://www.softbase.com/

The information contained in this message may be CONFIDENTIAL and is for the intended addressee only. Any unauthorized use, dissemination of the information, or copying of this message is prohibited. If you are not the intended addressee, please notify the sender immediately and delete this message.



Fleming, Mark wrote:
> I haven't used DB2-enforced RI much. I'm hoping you can give me some
> insights.
>
> Here's the scenario. I have a parent table and 4 child tables with keys
> to the parent table. Each FK is set up similar to this:
>
> ALTER TABLE DB2PROD.child_tab_1 FOREIGN KEY fkey1
> (IN4_col1 ,
> IN4_col2 ,
> IN4_col3 )
> REFERENCES DB2PROD.parent_tab
> (IN5_col1 ,
> IN5_col2 ,
> IN5_col3 )
> ON DELETE CASCADE
>
> Is there any benefit to deleting the child rows before deleting the
> corresponding parent row? Or is it better to delete the parent row and
> let the deletes cascade to the child rows by DB2? The application is
> doing the first option, deleting the child rows first followed by the
> parent. They're getting really *really* slow response when they delete
> the parent row, though. All the deletes are in the same commit scope.
> Your thoughts?
>
> Mark Fleming
> Database Administration
> 847-402-3889
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
>
>
>

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

David Churn

Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE
(in response to Troy Coleman)
Mark,

What kind of better are you looking for? Faster performance? A single
parent delete that cascades into child deletes performs faster than deleting
the children then the parent. Fewer calls to DB2 means less time the
processor has to spend transfering control from the program to DB2 and back.

However, using DELETE CASCADE can give you a large unit of work. If there
are several levels of RI using CASCADE, then a delete to the top level
parent would cause deletes throughout the structure. It is important to
have a rough estimate on how many deletes will be generated. 20 rows
deleted should not be an issue. 20 million rows might cause issues in other
areas (locking leaps to mind).

--
David Churn
Database Architect, DST Systems, Inc.
President, Heart of America DB2 Users Group

On 1/16/08, Fleming, Mark <[login to unmask email]> wrote:

> I haven't used DB2-enforced RI much. I'm hoping you can give me some
> insights.
>
> Here's the scenario. I have a parent table and 4 child tables with keys
> to the parent table. Each FK is set up similar to this:
>
> ALTER TABLE DB2PROD.child_tab_1 FOREIGN KEY fkey1
> (IN4_col1 ,
> IN4_col2 ,
> IN4_col3 )
> REFERENCES DB2PROD.parent_tab
> (IN5_col1 ,
> IN5_col2 ,
> IN5_col3 )
> ON DELETE CASCADE
>
> Is there any benefit to deleting the child rows before deleting the
> corresponding parent row? Or is it better to delete the parent row and
> let the deletes cascade to the child rows by DB2? The application is
> doing the first option, deleting the child rows first followed by the
> parent. They're getting really *really* slow response when they delete
> the parent row, though. All the deletes are in the same commit scope.
> Your thoughts?
>
> Mark Fleming
> Database Administration
> 847-402-3889
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
> list archives, the FAQ, and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab. While at the site, you
> can also access the IDUG Online Learning Center, Tech Library and Code
> Place, see the latest IDUG conference information, and much more. If you
> have not yet signed up for Basic Membership in IDUG, available at no cost,
> click on Member Services at http://www.idug.org/lsms
>

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Edward Krisiewicz

Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE
(in response to David Churn)
I wrote the following SQL. It lists the RI that has missing matching
indexes. It was once questioned, but I was unable to recreate the
case. Ed



SELECT DISTINCT
F.CREATOR, F.TBNAME AS CHILD, T1.CARD, F.RELNAME,
R.REFTBNAME AS PARENT, T2.CARD, R.IXNAME
FROM SYSIBM.SYSFOREIGNKEYS F, SYSIBM.SYSRELS R,
SYSIBM.SYSTABLES T1, SYSIBM.SYSTABLES T2
WHERE R.RELNAME = F.RELNAME
AND R.TBNAME = F.TBNAME
AND R.CREATOR = F.CREATOR
AND T1.CREATOR = F.CREATOR AND T1.NAME = F.TBNAME
AND T2.CREATOR = R.REFTBCREATOR AND T2.NAME = R.REFTBNAME
AND NOT EXISTS
( SELECT * FROM SYSIBM.SYSINDEXES I, SYSIBM.SYSKEYS K
WHERE F.CREATOR = I.TBCREATOR
AND F.TBNAME = I.TBNAME
AND F.COLNAME = K.COLNAME
AND F.COLSEQ = K.COLSEQ
AND I.NAME = K.IXNAME
AND I.CREATOR = K.IXCREATOR)

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mark Fleming

Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE
(in response to Edward Krisiewicz)
Thanks everyone for helping out on this issue. The problem was indeed
that the dependant tables did not have an exact index on the keys
corresponding to the parent foreign keys. Once I updated those indexes
to have all the columns in the exact order, DB2 used those indexes
instead of the table space scan it was getting before.

Also, thanks to all who provided SQL to identify missing indexes for
foreign keys. Because the app is already deleting the dependant rows
before the parent, I am going to look into changing the DELETE CASCADE
to DELETE RESTRICT.

Now if I can just get the app to manage their commit scope (they're
deleting millions of rows). Sigh. Our job is never done, is it?

Sorry it took so long to respond. And thanks again for all the
knowledgeable responses.

Mark Fleming
Database Administration
847-402-3889

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms