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:
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:
However it would take less than one second to finish this:
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_SZ)
3. Turning off STMM (SELF_TUNING_MEM = 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

Fix Pack "0"? Are you kidding me?

Nope, I never kid. I do agree we should be applying the fix packs soon ....

You have a severe memory issue. You have 32 bit Windows which will only give DB2 about 2 GB of memory to play with. Then you have 25-60 databases crammed into that little space. It is a wonder anything works at all. Your "hangs" are probably windows doing nothing but memory swapping.

Andy

Why did they refuse? v9.1 is a supported product.

Hi ARWinner,

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 here. 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 maybe know the tasks DB2 does after finishing a query and whether they can be disabled?

Many thanks,
Billy


Hi db2girl,

We are using the databases for an IBM Rational Software that hasn't been supported in the past few years.

This makes no sense from a DB2 perspective, You shut down the instance AND THEN the result set is returned? Once the instance is shut down, nothing should be happening as far as DB2 is concerned.

I strongly think that the server is way over taxed. Things are slow because there has to be memory swapping going on. The query was returned after DB2 shutdown probably came from the swap space. DB2 probably served up the results, but they got swapped to disk because of your memory problem. Then when you shut down DB2, Windows cleaned up and then sent the results.

Andy