Deletion from a large table (with two child tables) takes more than an hour

[karpalmera]

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?

[docdiesel]

Hi,

looks like a lot of I/O on your action which means DB2 seems to be doing table scans and is not using the indexes. A lot of indexes on your tables don't mean they're gonna used. Index on columns A, B and C are useless if you're using a condition like "WHERE D=123". Check constraints, joins and conditions. Or, in first step, use the DB2 Explain tool to check which indexes are used on your SQL statement (if any).

Regards

Doc Diesel