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

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

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

  • can you point out some more information (snippets of your corrumpting select and the accessible select)? – Peter Miehle Dec 13 '11 at 8:44

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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