DB2 hang when query or opening large table

Question

Hi There

I have a problem here. I have a DB2 v8.1 where I have this particular table which I cannot query or open. Whenever I tried to query or sample data it will tell me its a timeout/deadlock issue after for long time. No one is using the table so it cant be deadlock. Is there anyway i can recover this table? thanks.

Answer 1

Can you just do a select with an UNCOMMITED READ isolation level in order to bypass all the eventual locks :

SELECT * FROM YOUR_TABLE 
FETCH FIRST 10 ROWS ONLY
WITH UR

This way it will retrieve 10 rows from the table. If it works, just do it again without the FETCH FIRST clause : it was effectively locked by some other connection.

You can also try with changing the optimisation level by issuing

SET CURRENT QUERY OPTIMIZATION 0

in order to just activate INDEX SCAN, FULL SCAN & NESTED LOOPS without all those efficient, but often expensive HASH JOIN & friends that could be the culprit here if the stats are miscalculated.

Edit:

With the comments, I think that your best bet would be with db2dart to check your database.