"P Adhia" <pa****@spamnot.yahoo.comwrote in message
news:53***************************@ALLTEL.NET...
Odd Bjørn Andersen wrote:
>c1 is unique in TAB1 (primary key), but not in TAB2. I expect a little
less
Besides what Serge suggested, other things to check would be to see if the
table has any large child tables with cascade delete relationship
(specially without supporting index), and/or delete triggers. These don't
show up in explain, but do have tangible overhead.
Regards
P Adhia
Thanks for all the response, but none have helped so far. There is something
strange going on here and I don't think it's the SQL in itself that causes
the problems.
It seems that the job stops after deleting some of the rows, and then
nothing seems to happen. The job does not continue or finish.
In TAB1 there are 1680554 rows, and I tried with 9465 rows (9295 unique
values in col1) in TAB2. When I tried the delete command like this:
delete from TAB1
where col1 in (select distinct col1 from TAB2)
the jobs deleted 308 rows and then STOPPED!
When I tried the merge command:
MERGE INTO TAB1 USING (SELECT DISTINCT col1 FROM TAB2) AS TAB2
ON TAB1.col1= TAB2.col1
WHEN MATCHED THEN DELETE
Then the job deleted 9244 rows and then stopped.
In both circumstances it took less than a second before the jobs stopped.
There are RI in question here, but I have deleted all the records in all the
underlying tables to TAB1 (just checked !!). So there should be no overhead
with delete cascade.
I set diaglevel to 4 to see if there are any messages concerning this, but
the only thing I got was:
2006-08-21-10.43.03.440000+120 E18248H389 LEVEL: Info (OS)
PID : 3960 TID : 1052 PROC : db2cmnclp.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, oper system services, sqlodelete, probe:100
CALLED : OS, -, unspecified_system_function
OSERR : 2 "The system cannot find the file specified."
DATA #1 : File name, 6 bytes
\s3ro.
but I cannot see that this has any relevance to my problem.
So now I am even more confused, if possbile, than when I started out.....
Odd B