Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Location
    Columbus, OH
    Posts
    55

    Unanswered: Increasing bufferpools with STMM

    Hello DB2 friends!

    I have a DB2 ESE v9.1 database running on virtual Linux Suse 10. Self tuning memory is enabled for locking, sorting and package cache, but not for database memory. My db cfg parameters look as follows:

    Self tuning memory (SELF_TUNING_MEM) = ON
    Size of database shared memory (4KB) (DATABASE_MEMORY) = 61440
    Database memory threshold (DB_MEM_THRESH) = 10
    Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC
    Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC
    Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC
    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC
    Sort list heap (4KB) (SORTHEAP) = AUTOMATIC

    My bufferpools are NOT set for self tuning.

    Over the weekend I altered 2 of the bufferpools increasing the number of pages for an increase of 16MB.

    My question is this: When I gave more memory to the bufferpools was it taken from DATABASE_MEMORY? If so, would it reduce the amount of memory available for other memory pools configured for automatic tuning?

    The reason I ask is because I've been seeing these warning messages in my diag.log

    2013-04-09-20.11.59.835817-240 I2278304E827 LEVEL: Warning
    PID : 14332 TID : 47244204438480PROC : db2agent (ECDWMADP) 0
    INSTANCE: dbiecs1p NODE : 000 DB : ECDWF01P
    APPHDL : 0-547 APPID: GA651936.HEC7.130410113124
    AUTHID : ECZWMADP
    FUNCTION: DB2 UDB, SQO Memory Management, sqloCreateMemorySubPool, probe:130
    MESSAGE : ZRC=0x8B0F0012=-1961951214=SQLO_NOMEM_SHARED_SORT
    "No memory available in 'Shared Sort Heap'"
    DIA8300C A memory heap error has occurred.
    DATA #1 : String, 52 bytes
    Unable to allocate memory for the initial pool size.
    DATA #2 : Pool type, PD_TYPE_POOL_TYPE, 4 bytes
    18
    DATA #3 : String, 16 bytes
    Shared Sort Heap
    DATA #4 : String, 14 bytes
    SHEAPTHRES_SHR
    DATA #5 : Initial pool size, PD_TYPE_POOL_INITIAL_SIZE, 8 bytes
    90112


    I'm wondering if I need to increase my DATABASE_MEMORY.

    Here's my OS memory:
    Mem: 8176744k total, 6079968k used, 2096776k free, 180828k buffers

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The bufferpools reside in database_memory, so increasing them would have reduced the amount for other thing that also reside in database_memory. You probably should increase database_memory by the same amount you increased the bufferpools.

    Andy

  3. #3
    Join Date
    Aug 2011
    Location
    Columbus, OH
    Posts
    55
    Andy - thank you, thank you, thank you. DB2's memory model has been a challenge for me and I greatly appreciate your help in making sure I'm understanding it.

  4. #4
    Join Date
    Dec 2012
    Posts
    53
    If you increase bufferpool size when database is active it will take from database memory, if you increase bufferpool size when database is deactive it will take from O.S level. I read this point somewhere i dont have the link to share

  5. #5
    Join Date
    Aug 2011
    Location
    Columbus, OH
    Posts
    55
    Oh! That's interesting. Thank you for sharing, Chaitanya.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by chaitanya_db2adm View Post
    if you increase bufferpool size when database is deactive it will take from O.S level.
    That doesn't make sense. To alter the bufferpool size you need to connect to the database, and you cannot connect to an inactive database, can you?
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Tracygirl View Post
    Hello DB2 friends!

    I have a DB2 ESE v9.1 database running on virtual Linux Suse 10.
    Are you sure it is V9.1? That version is no longer supported by IBM, and was not a particularly good "vintage" to begin with. If you can't get an upgraded version (at least V9.7) due to license issues, I would strongly consider getting DB2 Express-C 10.1 (which is free). Since you don't have support for DB2 V9.1, it wouldn't make much difference. About the only thing you might be missing with DB2 Express-C (compared to 9.1 ESE) is SQL Replication.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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