Tutorial :DB2 hang when query or opening large table



Question:

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.


Solution: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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »