Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    Jan 16
    2008

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

    Mark Fleming
    [Allstate]
    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
    [SRP]
    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
    [FISA/City of New York]
    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
    [FISA/City of New York]
    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
    [Themis Inc.]
    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
    [Lincoln Financial Group]
    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
    [Allstate]
    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
    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
    [CA Technologies]
    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
    [DST Systems,Inc.]
    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
    [Bear Stearns]
    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
    [Allstate]
    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

    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact