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?

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).

Actually, that was what we were doing in the past. We delete per batch..However, we actually would like to do it online now...

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

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

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

on table b you should really have an index that supports your entire key. You almost have it, but missing at the end
Dave

okay, Dave. i'll try adding a new index for table B.

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.

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...

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...

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.

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!

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