Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2008
    Posts
    9

    Unanswered: Deletion of 1500 records from a parent table takes more than an hour

    Hello!

    We are trying to delete 1,500 records from a parent table with two child tables
    and it is taking hours to execute.

    Here is the set-up.
    - The parent table has 800,000 records. Child table A has 2.3 M records. Child table B has 200,000 records.
    - The parent table has 5 indexes. Child table A has 6 indexes and Child table B has one index.
    - Both child tables has contraints with 'on delete cascade' parameter.

    Do the indexes affect the long execution of delete?

    In our monitoring tool, there is a large percentage in the following events:
    - WAIT: OTHER READ I/O
    - WAIT: SYNC I/O

    What is the significance of these two events? How can I reduce the percentage of these events?


  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    If it's an option to do this offline, I would (1) remove the two FKs; (2) do the 1500 row delete; (3) put back the two FKs; (4) run CHECK DATA (with DELETE option).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Apr 2008
    Posts
    9
    Actually, that was what we were doing in the past. We delete per batch..However, we actually would like to do it online now...

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    karpalmera,
    can you provide table definitions, primary keys, foreign keys, indexes etc.

    Please also provide db2 version and fixpack level and operating system name, version and fixpack level.

    Also check if there are so triggers on tables. Also check if there is some kind of monitoring turned on or even traces.

    Hope this helps,
    Grofaty

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    do you have indexes on the child tables that support the foreign keys? For instance if col12 is defined as the foreign key you have to have an index that starts with col12.

    Dave

  6. #6
    Join Date
    Apr 2008
    Posts
    9
    Hi! We are using DB2 UDB 8.10 in Z/OS. We don't have triggers and the monitoring tool we use is TMON for DB2.

    Here are the table definitions:
    Parent table
    OWNER.PARENT
    BANK_CD SMALLINT NOT NULL,
    GL_PROC_CURR CHAR(3) NOT NULL,
    UNIT_CD SMALLINT NOT NULL,
    GL_SYSTEM_ID CHAR(3) NOT NULL,
    GL_DOC_TYPE CHAR(3) NOT NULL,
    GL_DOC_NO INTEGER NOT NULL,
    GL_SRT_STAT CHAR(1) NOT NULL,
    GL_ADJUST_TAG CHAR(2) NOT NULL,
    GL_COMP_SRT SMALLINT NOT NULL,
    GL_CONTRA_UNIT SMALLINT NOT NULL,
    GL_DOC_STAT CHAR(1) NOT NULL,
    GL_POST_STAT CHAR(1) NOT NULL,
    GL_DOC_DATE INTEGER NOT NULL,
    GL_EDIT_DATE INTEGER NOT NULL,
    GL_POST_DATE INTEGER NOT NULL,
    GL_POST_TIME TIME NOT NULL,
    GL_DOC_MODE CHAR(1) NOT NULL,
    GL_SRT_NORM CHAR(1) NOT NULL,
    USER_ID CHAR(8) NOT NULL,
    SRT_NO INTEGER NOT NULL,
    SRT_TAG CHAR(1) NOT NULL,
    GL_PARTS VARCHAR(252) NOT NULL
    ,PRIMARY KEY ( BANK_CD
    ,GL_PROC_CURR
    ,UNIT_CD
    ,GL_SYSTEM_ID
    ,GL_DOC_TYPE
    ,GL_DOC_NO
    ,GL_SRT_STAT
    ,GL_ADJUST_TAG
    ,GL_COMP_SRT )

    Child table A
    OWNER.CHILDA
    BANK_CD SMALLINT NOT NULL,
    GL_PROC_CURR CHAR(3) NOT NULL,
    UNIT_CD SMALLINT NOT NULL,
    GL_SYSTEM_ID CHAR(3) NOT NULL,
    GL_DOC_TYPE CHAR(3) NOT NULL,
    GL_DOC_NO INTEGER NOT NULL,
    GL_SRT_STAT CHAR(1) NOT NULL,
    GL_ADJUST_TAG CHAR(2) NOT NULL,
    GL_COMP_SRT SMALLINT NOT NULL,
    GL_SEQ_NO SMALLINT NOT NULL,
    GL_ACCT_CD INTEGER NOT NULL,
    GL_DR_AMT DECIMAL(15, 2) NOT NULL,
    GL_CR_AMT DECIMAL(15, 2) NOT NULL,
    GL_OTHFX_CURR CHAR(3) NOT NULL,
    GL_OTHFX_DR_AMT DECIMAL(15, 2) NOT NULL,
    GL_OTHFX_CR_AMT DECIMAL(15, 2) NOT NULL,
    GL_SL_TYPE CHAR(1) NOT NULL,
    GL_SL_CODE CHAR(10) NOT NULL,
    GL_RATE_BASE CHAR(1) NOT NULL,
    GL_RATE_TYPE CHAR(1) NOT NULL,
    GL_FX_RATE DECIMAL(14, 9) NOT NULL,
    GL_REF_NO CHAR(20) NOT NULL,
    GL_FROM_DATE INTEGER NOT NULL,
    GL_TO_DATE INTEGER NOT NULL,
    GL_NO_OF_DAYS SMALLINT NOT NULL,
    GL_ACC_INT_RATE DECIMAL(11, 8) NOT NULL,
    GL_PRIN_AMT DECIMAL(15, 2) NOT NULL

    Child table B
    OWNER.CHILDB
    BANK_CD SMALLINT NOT NULL,
    GL_PROC_CURR CHAR(3) NOT NULL,
    UNIT_CD SMALLINT NOT NULL,
    GL_SYSTEM_ID CHAR(3) NOT NULL,
    GL_DOC_TYPE CHAR(3) NOT NULL,
    GL_DOC_NO INTEGER NOT NULL,
    GL_SRT_STAT CHAR(1) NOT NULL,
    GL_ADJUST_TAG CHAR(2) NOT NULL,
    GL_COMP_SRT SMALLINT NOT NULL,
    GL_ALLOC_STATUS CHAR(1) NOT NULL,
    GL_SEQ_NO SMALLINT NOT NULL,
    GL_ACCT_CD INTEGER NOT NULL,
    GL_CHARGE_UNIT SMALLINT NOT NULL,
    GL_DR_AMT DECIMAL(15, 2) NOT NULL,
    GL_CR_AMT DECIMAL(15, 2) NOT NULL,
    GL_OTHFX_CURR CHAR(3) NOT NULL,
    GL_OTHFX_DR_AMT DECIMAL(15, 2) NOT NULL,
    GL_OTHFX_CR_AMT DECIMAL(15, 2) NOT NULL,
    GL_FX_RATE DECIMAL(14, 9) NOT NULL


    Here are the foreign keys:
    Child table A
    ALTER TABLE OWNER.CHILDA
    ADD FOREIGN KEY CHILDA1 (BANK_CD,GL_PROC_CURR,UNIT_CD,
    GL_SYSTEM_ID,GL_DOC_TYPE,GL_DOC_NO,GL_SRT_STAT,GL_ ADJUST_TAG,
    GL_COMP_SRT) REFERENCES OWNER.PARENT ON DELETE CASCADE;
    Child table B
    ALTER TABLE OWNER.CHILDB
    ADD FOREIGN KEY CHILDB1 (BANK_CD,GL_PROC_CURR,UNIT_CD,
    GL_SYSTEM_ID,GL_DOC_TYPE,GL_DOC_NO,GL_SRT_STAT,GL_ ADJUST_TAG,
    GL_COMP_SRT) REFERENCES OWNER.PARENT ON DELETE CASCADE;
    ;




    Here are the indexes:
    Parent table
    Index 1
    BANK_CD,
    GL_PROC_CURR
    UNIT_CD
    GL_SYSTEM_ID
    GL_DOC_TYPE
    GL_DOC_NO
    GL_SRT_STAT
    GL_ADJUST_TAG
    GL_COMP_SRT

    Index 2
    BANK_CD,
    GL_DOC_TYPE
    UNIT_CD
    GL_SRT_STAT
    GL_ADJUST_TAG
    GL_SYSTEM_ID

    Index 3
    GL_DOC_NO

    Index 4
    GL_DOC_STAT
    GL_POST_DATE

    Index 5
    UNIT_CD
    GL_POST_DATE


    Child A
    Index 1
    BANK_CD
    GL_PROC_CURR
    UNIT_CD
    GL_SYSTEM_ID
    GL_DOC_TYPE
    GL_DOC_NO
    GL_SRT_STAT
    GL_ADJUST_TAG
    GL_SEQ_NO
    GL_ACCT_CD

    Index 2
    BANK_CD,
    GL_DOC_TYPE
    UNIT_CD
    GL_SRT_STAT
    GL_ADJUST_TAG
    GL_SYSTEM_ID

    Index 3
    GL_SYSTEM_ID
    GL_DOC_TYPE
    GL_ACCT_CD
    GL_DR_AMT
    GL_CR_AMT
    GL_OTHFX_CURR

    Index 4
    BANK_CD,
    GL_PROC_CURR
    UNIT_CD
    GL_REF_NO

    Index 5
    UNIT_CD
    GL_ACCT_CD

    Index 6
    BANK_CD
    GL_PROC_CURR
    UNIT_CD
    GL_SYSTEM_ID
    GL_DOC_TYPE
    GL_DOC_NO
    GL_COMP_SRT
    GL_SRT_STAT
    GL_ADJUST_TAG


    Child B
    Index
    BANK_CD,
    GL_PROC_CURR
    UNIT_CD
    GL_SYSTEM_ID
    GL_DOC_TYPE
    GL_DOC_NO
    GL_SRT_STAT
    GL_ADJUST_TAG
    GL_SEQ_NO
    GL_ACCT_CD

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    on table b you should really have an index that supports your entire key. You almost have it, but missing at the end
    Dave

  8. #8
    Join Date
    Apr 2008
    Posts
    9
    okay, Dave. i'll try adding a new index for table B.

  9. #9
    Join Date
    Apr 2008
    Posts
    9
    Dave, when I added a new index, it still runs slow...I had to terminate it after an hour for it was only able to delete 6 records only.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I guess now is the time to finally look at the execution plan of your delete statement.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by n_i
    I guess now is the time to finally look at the execution plan of your delete statement.
    The problem is it won't tell the whole story: especially the CASCADE effects on the child tables (and certainly their indexes) is not visible in the execution plan.

    Either all those objects have to be monitored in detail during the delete, or we have to make an educated guess based, on the available info, where DB2 could be spending 1 hour deleting just 6 rows...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Peter.Vanroose
    The problem is it won't tell the whole story: especially the CASCADE effects on the child tables (and certainly their indexes) is not visible in the execution plan.
    Don't know about z/os, but it certainly shows cascading actions on LUW. I should know - I was once trying to deal with a cascading delete that involved 300+ tables...
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by n_i
    Don't know about z/os, but it certainly shows cascading actions on LUW.
    Interesting.
    Chances are, then, that the new Optimizaton Service Center (OSC) which comes with DB2 9 on z/OS also will show this kind of information. Not yet used it, unfortunately: still using Visual Explain.
    The problem will still be, I'm afraid, that it won't tell much more than what we already see from the table design, that is, that there indeed will be cascading actions, and that they indeed will cause all indexes of the child tables to be accessed and updated. In a more or less random way if those indexes and/or the primary access path index are not the cluster index.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  14. #14
    Join Date
    Apr 2008
    Posts
    9
    Thank you, guys for the info. Our boss wants to go on with the batch clean up wherein we will drop the constraints, delete the records and run check utility. But I will still research on this more and do some experiments...thanks!

  15. #15
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Karpalmera,
    The cascade is really going to be generating deletes along the lines of:
    DELETE FROM TABLEX
    WHERE KEY = ?;

    Try explaining the same type delete statements on your child tables and see where you are getting the tablespace scan. It has to be that you are missing something in an index on one or more of the child tables. Do those child tables have children and do they have indexes that support the cascading effect?
    Dave
    Last edited by dav1mo; 09-30-08 at 12:41.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •