DB2 instance hangs on query

[200dogz]

Hi all,

We have a DB2 instance running on a virtual machine with Windows Server 2003 and 3.75GB of RAM.

Some time since 2010, we started to experience problems with DB2, which the entire instance would "hang" when certain SQL queries are run and the only way we could get it to recover is by restarting the machine. I'm not sure if any update was deployed at that time. The version we currently have is:

DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with level identifier "02010107".
Informational tokens are "DB2 v9.1.0.356", "s060629", "NT32", and Fix Pack "0".

At first I suspected it was a result of poorly written queries but further look into the problems revealed that some of these queries that would cause the system to hang/query were rather simple and often a query would only be problematic for one database but fine for all others.

For example, our DB2 instance would stop responding if I run this query on one of our databases:

SELECT * FROM "TestCase" WHERE "Custom2" = 'something'

However it would take less than one second to finish this:

SELECT * FROM "TestCase"

I also don't think it's the problem with our databases. This is because I have tried copying some of the databases to our development environment and was able to execute the queries with no problem there.

This is why I think the problem lies between the server we use or the DB2 instance - maybe something to do with memory; rather than queries or databases. Unfortunately IBM refused to help us with this issue because they no longer supported the product that we're using DB2 for.

I've tried a few things to fix the problems, none of these worked so far:

1. Increasing instance heap size (MON_HEAP_SZ)
2. Update database application heap size (APP_CTL_HEAP_S Z)
3. Turning off STMM (SELF_TUNING_ME M = OFF)
4. Reducing the number of databases on the instance from 60 to 25 (maybe still too many?)
5. ....and many more small attempts at turing

I've also tried looking into "db2 list applications show detail" or db2diag.log but couldn't see any obvious problem.

I will continue to look into the problem and if all else fail we might have to use a fresh new production server. If there's suggestions you have to what the cause of the problems might be or what I can try please let me know. Any help is appreciated!!! :)

Cheers,
Billy

[ggonsalves]

Hi Billy,

The problem look to me a like a memory issue. You are using 32bit version of DB2 which can address only approx 2GB of total memory at max. It would be advisable to upgrade the DB2 instance to 64-bit version. Tune the memory parameter and then execute your query.

[200dogz]

Hi ggonsalves,

Thanks for your suggestions and sorry for the late response (I am only able to access the system on Tuesdays).

I have tried to tune the memory parameters using the configuration advisor in DB2 Control Center before going about upgrading the system or DB2 instance but so far that hasn't worked out...

However, I have discovered something very interesting that I think I should mention. The query results of whatever query that hangs DB2 is actually correctly returned after I shut down the instance. To me this seems to suggest the execution of queries is not actually what's causing the problem but rather it's something DB2 does after a query has finished (stuffs related to logs maybe?).

Do you know the tasks DB2 does after finishing a query and whether they can be disabled?

Many thanks,
Billy