Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

No errors or warnings, database just hangs while trying to execute simple select query on specific table. Other tables are accessible until I trying to query specific (faulty) table.

This should not be related to large volume of data, table contains only few thousands of rows. I've tried db2 restart db2 dbName, but it didn't help.

Something wrong with data? Any suggestions how to recover?

DB2 9.5.3 on Solaris

share|improve this question
    
can you point out some more information (snippets of your corrumpting select and the accessible select)? –  Peter Miehle Dec 13 '11 at 8:44
add comment

migrated from stackoverflow.com Dec 15 '11 at 17:01

This question came from our site for professional and enthusiast programmers.

1 Answer

I am not sure if I understand your problem. You say that the database hangs on a query, that means that all tables, views, etc, are blocked. However, you say that other tables are accessible, that means that the database is not hung, it is just an access to one table.

You used "db2 restart db", however it is for other things, for example when you need to restart the database once it has crashed, but I think your db has not crashed, just it is blocked.

I think your problem is a concurrency problem, the database has to be processing a big query on the table, putting an exclusive lock (or something similar) and the other queries cannot read the data with the current isolation level.

In order to see if this is the problem, try to get snapshots to see which table are accessed and check the lock list.

You can change the isolation level in the query, for example adding "WITH UR" at the end, if this isolation level is appropriated.

Other problems "could" be related to transaction logs (flush issue), busy or not enough agents, prefetchers issue, small bufferpool, expensive access plan. Well, you have to see the db2diag to have more clues about your problem.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.