Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: DB2 Memory math

  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: DB2 Memory math

    Hi all,

    I have not much understanding of memeory used by DB2.

    We have 4 production DB2 using 16 GB of physical RAM.

    Which parameters and/or settings should I review for application heap size and utility heap sioze.

    Actually on one DB we are having memory issues. I remember, few months ago IBM support had advised to free some memory from bufferpools for backup utility which worked like a charm. The senior DBA ( remote support ) had freed some buffrpool allocations.

    I am not sure how to caculate these things. Can some one help me to start checking where the memory comes and where it goes.

    One of our replication program is being set up and it is complaining of having not enough memory available

    FUNCTION: DB2 UDB, SQO Memory Management, SqloMemController::requestMemory, probe:50
    MESSAGE : ZRC=0x8B0F0000=-1961951232=SQLO_NOMEM "No Memory Available"
    DIA8300C A memory heap error has occurred.
    DATA #1 : String, 28 bytes
    Attempt to get memory failed
    DB2 V 9.5.5 on WIN2K3

    Regards and Thanks

    DBFinder

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How many servers are you using? How many DB2 instances? And how many databases? Is it 32 or 64 bit? I need a clearer picture of your environment.

    Andy

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    On each server there is only one DB. One Partition and DB2 is 64 bit. One Instance on each server.

    2 are POS dbs around 110 GB size at 100 transaction per second.

    other 2 are gaming dbs 280 GB size with 240 transactions per second.

    Regards

    DBFinder

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    One thing you can try that should give you some understanding is use db2top. On each server run "db2top -d <database>". Then press "m" to see how your memory is being used.

    Andy

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    How do we run db2top ?

    C:\Documents and Settings\db2admin>db2top
    'db2top' is not recognized as an internal or external command,
    operable program or batch file.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Apparently db2top cannot be run on Windows. I guess you are stuck with db2pd or use the snapshot routines:

    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    Andy

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    But, my original ques was ,

    Which parameters and/or settings should I review for application heap size and utility heap sioze.
    regards

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by DBFinder View Post
    But, my original ques was ,

    Which parameters and/or settings should I review for application heap size and utility heap sioze.
    application heap size - applheapsz (appl_memory/instance_memory if applheapsz is automatic)

    utility heap size - util_heap_sz


    I think the memory message you pasted should also contain the heap name.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think your best bet would be to enable DB2 memory self-tuning and let it do the work. In particular, it will increase the utility heap when it's needed and reallocate that memory to other pools when utilities are not running.

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Bella,

    Here is complete entry. Can you find for me which memory pool was out of memory.

    Code:
    2010-08-16-12.03.32.143000-240 I14671F988         LEVEL: Warning
    PID     : 1216                 TID  : 8852        PROC : db2syscs.exe
    INSTANCE: DB2                  NODE : 000         DB   : GC_PROD
    APPHDL  : 0-31494              APPID: 10.90.1.21.41477.100816154811
    AUTHID  : DB2ADMIN
    EDUID   : 8852                 EDUNAME: db2agent (GC_PROD)
    FUNCTION: DB2 UDB, SQO Memory Management, SqloMemController::requestMemory, probe:50
    MESSAGE : ZRC=0x8B0F0000=-1961951232=SQLO_NOMEM "No Memory Available"
              DIA8300C A memory heap error has occurred.
    DATA #1 : String, 28 bytes
    Attempt to get memory failed
    DATA #2 : unsigned integer, 8 bytes
    4259840
    DATA #3 : unsigned integer, 8 bytes
    0
    DATA #4 : String, 7 bytes
    PRIVATE
    DATA #5 : unsigned integer, 8 bytes
    3166568448
    DATA #6 : unsigned integer, 8 bytes
    132841472
    DATA #7 : unsigned integer, 8 bytes
    14749073408
    DATA #8 : unsigned integer, 8 bytes
    14749331456
    DATA #9 : unsigned integer, 8 bytes
    135331840
    regards
    Last edited by DBFinder; 08-16-10 at 23:58.

  11. #11
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by n_i View Post
    I think your best bet would be to enable DB2 memory self-tuning and let it do the work. In particular, it will increase the utility heap when it's needed and reallocate that memory to other pools when utilities are not running.
    I cannot do it. Very sensetive database. High activity gaming DB.

    Even tried to tune some online backup command, always went wrong. That's why I need to understand more about memory.

    regards

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Unfortunately, utility heap is not tuned by STMM, but it's a soft limit and can grow into overflow if there is still room

  13. #13
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by DBFinder View Post
    Bella,

    Here is complete entry. Can you find for me which memory pool was out of memory.

    Code:
    2010-08-16-12.03.32.143000-240 I14671F988         LEVEL: Warning
    PID     : 1216                 TID  : 8852        PROC : db2syscs.exe
    INSTANCE: DB2                  NODE : 000         DB   : GC_PROD
    APPHDL  : 0-31494              APPID: 10.90.1.21.41477.100816154811
    AUTHID  : DB2ADMIN
    EDUID   : 8852                 EDUNAME: db2agent (GC_PROD)
    FUNCTION: DB2 UDB, SQO Memory Management, SqloMemController::requestMemory, probe:50
    MESSAGE : ZRC=0x8B0F0000=-1961951232=SQLO_NOMEM "No Memory Available"
              DIA8300C A memory heap error has occurred.
    DATA #1 : String, 28 bytes
    Attempt to get memory failed
    DATA #2 : unsigned integer, 8 bytes
    4259840
    DATA #3 : unsigned integer, 8 bytes
    0
    DATA #4 : String, 7 bytes
    PRIVATE
    DATA #5 : unsigned integer, 8 bytes
    3166568448
    DATA #6 : unsigned integer, 8 bytes
    132841472
    DATA #7 : unsigned integer, 8 bytes
    14749073408
    DATA #8 : unsigned integer, 8 bytes
    14749331456
    DATA #9 : unsigned integer, 8 bytes
    135331840
    regards


    I think what this message is saying is that:

    "PRIVATE" heap requested 4259840 bytes. It's currently using 14749073408 and the maximum allowed is 14749331456

    14749331456 - 14749073408 = 258048 which is less than what it's trying to allocate.


    As far as I know, this "PRIVATE" heap is not something you can configure (except for private sorts). I'll have to look into this further...


    I think it's not a bad idea to open a pmr (not sure if it has to go to the replication group or db2, can start with db2)

  14. #14
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Surprisingly 'PRIVATE' is not known via sysibmadm views

    Code:
    POOL_ID        MEMORY_SIZE         
    
    -------------- --------------------
    APPLICATION                65667072
    APPL_SHARED                33816576
    BP                       9389080576
    CAT_CACHE                   4325376
    DATABASE                  100597760
    LOCK_MGR                  282001408
    OTHER                        196608
    PACKAGE_CACHE             111083520
    UTILITY                      851968
    -----------------------------------
     TOTAL                   9987031040

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by db2girl View Post
    I think what this message is saying is that:

    "PRIVATE" heap requested 4259840 bytes. It's currently using 14749073408 and the maximum allowed is 14749331456

    14749331456 - 14749073408 = 258048 which is less than what it's trying to allocate.

    It can't be using this much, I may not be reading the numbers correctly... Perhaps, the large number is your instance_memory setting


    Try:
    db2pd -dbptnmem

Posting Permissions

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