I have a table with 400 records. When I make the exclusion of these records a trigger is triggered to delete records from another table. However this process is taking 10 seconds and raising the processor utilization at 100%. Has anyone experienced this problem? There is some suggestion?
I'm using DB2 9.7 Express-C
Thanks, but now I have observed that slowness is caused by referential integrity. When you delete a record from the parent table records from four tables are also excluded. What should I do? Remove referential integrity?
Originally Posted by db2mor
You probably have a bad access-plan for the DELETE on the other table, probably you have in the trigger a "delete from other-table WHERE...." ( a searched delete) that is underperforming.
Verify that runstats are up to date on both tables, and all indexes on both tables.
Verify that the searched-delete has an appropriate index (use the index advisor db2advis or use the (free) Data-Studio 3.1.1 to recommend indexes).
If indexes are already present, verify that you don't have any row-locking issues when you perform the delete (i.e. not waiting for locks).
But most likely it is just a poor access-plan that you can fix with indexes and/or runstats.