Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Wink Unanswered: DB2 instance hangs on query

    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:
    Code:
    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:
    Code:
    SELECT * FROM "TestCase" WHERE "Custom2" = 'something'
    However it would take less than one second to finish this:
    Code:
    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_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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Fix Pack "0"? Are you kidding me?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2009
    Posts
    3
    Nope, I never kid. I do agree we should be applying the fix packs soon ....

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by $200 View Post
    Unfortunately IBM refused to help us with this issue because they no longer supported the product that we're using DB2 for.
    Why did they refuse? v9.1 is a supported product.

  6. #6
    Join Date
    Feb 2009
    Posts
    3
    Quote Originally Posted by ARWinner View Post
    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
    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


    Quote Originally Posted by db2girl View Post
    Why did they refuse? v9.1 is a supported product.
    Hi db2girl,

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

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by $200 View Post
    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?).
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •