Update statement using IN clause issue

Question

I have an update statement that goes thus:

update tableA 
    set val1='X', val2='Y' 

where id in (
    select id from tableA A 

    LEFT JOIN tableB B ON A.col1=B.col1 and A.col2=B.col2 

    where A.col3='xx' and B.col3= 'YY')

Now, the inner SELECT statement runs in 10 minutes returning 1000 records (both tableA and tableB have about 10mil records each)

The whole update statement runs for 3 hours ( have not waited yet for completion)

Any ideas?

Solution 1

I know this thread is a bit old, but Id like to share what happened to me (specifically today hehe).

We were facing the same problem. Update was taking forever to run! So we found out that the table we were trying to update was locked. However no error message was thrown by DB2.

hope that Ive had helped somehow! cheers. ~chamb~

Solution 2

There is probably no index on the column tableA.id or the type of tableA.id doesn't match the type returned by the select.

[EDIT] Alternatively, you can try this weird syntax:

update (
    select val1, val2 from tableA A 

    LEFT JOIN tableB B ON A.col1=B.col1 and A.col2=B.col2 

    where A.col3='xx' and B.col3= 'YY'
) tmp
set val1='X', val2='Y' 

This creates a temporary table which is still linked to the original table, so you can update the values which the select returns and they will show up in the original table.

[EDIT2] I missed the fact that you're selecting and updating the same table (i.e. id is the same column). In this case, the type obviously doesn't matter and you shouldn't even need an index (since the select already returns the correct rows).

Try EXPLAIN PLAN to see whether something else is going on.

Also, you might get in conflict with another process which also updates the same table (i.e. you have a lock somewhere). AQT has a Monitor which can show these things. If you can, get AQT and use that. It has excellent support for DB2 and is better than anything I've seen out there so far.