Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2009
    Posts
    272

    Unanswered: DB2 V9.7 - Bufferpool full

    Currently on DB2 V9.7 FP2.

    Seeing these warnings in db2diag.log although bufferpools are set to automatic and there is lot of free memory on the box.

    2010-07-15-10.00.32.467237-240 I104056A505 LEVEL: Warning
    PID : 2003078 TID : 7969 PROC : db2sysc 0
    INSTANCE: teieoryz NODE : 000
    EDUID : 7969 EDUNAME: db2pfchr (IEORTEST) 0
    FUNCTION: DB2 UDB, buffer pool services, sqlbFreeUpBlock, probe:20
    MESSAGE : ZRC=0x8502002C=-2063466452=SQLB_BPFULL
    "no available buffer pool pages"
    DATA #1 : <preformatted>
    Buffer pool 2 (IEORDATABP1) is full.
    This message will be suppressed for 60 minutes.


    $ db2 "select substr(BPNAME,1,18), BUFFERPOOLID, PAGESIZE, NPAGES from syscat.bufferpools"

    1 BUFFERPOOLID PAGESIZE NPAGES
    ------------------ ------------ ----------- -----------
    IBMDEFAULTBP 1 4096 -2
    IEORDATABP1 2 4096 -2
    IEORIDXBP1 3 4096 -2

    3 record(s) selected.


    Any help/info on this would be greatly appreciated
    Last edited by blazer789; 07-15-10 at 17:09.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would use hardcoded number of pages instead of -2 (STMM) fpr each bufferpool. How many DB2 databases do you have on the server and how much total physical memory is there? Is this development, production, etc. How large is the database?
    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
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    In addition to what Marcus has asked, what are the settings for the instance and database memory? What other parameters are set to automatic?

  4. #4
    Join Date
    Jun 2009
    Posts
    272
    There are two instances on the box each with one database. Total box has 8 GB of memory out of which 6.6 GB of memory is allocated to the instance_memory of this instance where I am noticing the errors while the other instance has 0.075 GB memory allocated.

    Both instance memory and database memory are set to automatic and STMM is turned on.

    Here are the other parameters set to automatic

    Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(298900)
    Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(6656)
    Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(98)
    Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(87210)
    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(1393)
    Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(278)
    Database heap (4KB) (DBHEAP) = AUTOMATIC(13666)
    Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(256)
    Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(40000)
    Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384)
    Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC
    Max number of active applications (MAXAPPLS) = AUTOMATIC(362)


    Here is the memory usage of the instance

    $ db2pd -dbptnmem

    Database Partition 0 -- Active -- Up 1 days 04:23:27 -- Date 07/15/2010 17:56:48

    Database Partition Memory Controller Statistics

    Controller Automatic: Y
    Memory Limit: 6977820 KB
    Current usage: 1488832 KB
    HWM usage: 1845440 KB
    Cached memory: 413568 KB

    Individual Memory Consumers:

    Name Mem Used (KB) HWM Used (KB) Cached (KB)
    ================================================== ======
    APPL-IEORTEST 160000 160000 156096
    DBMS-teieoryz 71872 71872 12352
    FMP_RESOURCES 22528 22528 0
    PRIVATE 29120 29120 6144
    DB-IEORTEST 1204032 1570304 238976
    LCL-p1065122 256 256 0
    LCL-p1966170 256 256 0
    LCL-p1065122 256 256 0
    LCL-p1212416 256 256 0
    LCL-p1142902 256 256 0


    Notice that the highwater mark is way below than the memory limit . DB2 should automatically increase the memory rather than thowing such errors.


    Here is the size of the database. This is our test environment

    $ db2 "call get_dbsize_info(?,?,?,0)"

    Value of output parameters
    --------------------------
    Parameter Name : SNAPSHOTTIMESTAMP
    Parameter Value : 2010-07-15-18.00.56.671086

    Parameter Name : DATABASESIZE
    Parameter Value : 5710028800

    Parameter Name : DATABASECAPACITY
    Parameter Value : 17851723776

    Return Status = 0

  5. #5
    Join Date
    Jun 2009
    Posts
    272
    Instance parameters which are set to automatic

    Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(1024)
    Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(1744455)
    Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(400)
    Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(400)
    Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)
    Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
    No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(4096)
    No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC(2048)
    (db2udb@palmetto)/db2udb:

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    blazer789, this looks very familiar

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You can try checking the STMM log to see if it makes any adjustments to the bufferpool(s). The warning in db2diag.log may just indicates the event that triggers BP size changes by STMM.

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    This message is for a block-based area of the bufferpool (not tuned by STMM)

  9. #9
    Join Date
    Jun 2009
    Posts
    272
    Even this answer looks very familiar Bella . Thanks anyways !!

Posting Permissions

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