Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2013
    Posts
    34

    Unanswered: Need to increase size of IBMDEFAULTBP bufferpool

    Hi,

    db2 version db2 9.7 on linux version 5.

    while running the queries i got below error:

    SQL1218N There are no pages currently available in bufferpool "4096" SQLSTATE=57011.

    in db2diag.nfy got below error:

    ADM6019E All pages in buffer pool "IBMSYSTEMBP4K" (ID "4096") are in use.
    Refer to the documentation for SQLCODE -1218.

    So to avoid the use of system bufferpool i need to increase IBMDEFAULTBP bufferpool size whose page size is 4096 and NPAGES are 32768.

    So my question is do i need to increase PAGE SIZE of this bufferpool and how to increase the PAGE SIZE? Please suggest command.

    Thanks in advance.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    You need to increase the *size* (= number of pages, ) of the bufferpool (not the pagesize).
    Refer to the Knowledge centre for the ALTER BUFFERPOOL statement details, and read it carefully.
    http://www-01.ibm.com/support/knowle...7.0%2F2-10-6-8

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The fact that DB2 attempts to use the system bufferpool suggests that non-system bufferpools may have been too large and could not be allocated.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    Yep, check the db2diag.log file around the startup time and you will most likely see a message saying that there was insufficient memory to allocate the bufferpools and that it has consequently used the IBMSYSTEMBP4K bufferpool

  5. #5
    Join Date
    Sep 2013
    Posts
    34
    Then how to decide the proper size of the bufferpool?

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    If you lack the skills or experience to judge bufferpool sizes, consider allowing DB2 to manage automatically the sizes of the bufferpools by enabling STMM and configuring your bufferpools to be automatically sized (instead of fixed size).
    For many simple databases this is by the easiest option, but in some situations might not be the best option.
    Refer to the online DB2 documentation for all the details.

    For a dedicated database server, it is common for 80% of the available RAM in the (virtual) machine to be allocated for DB2. However, if on the machine there are multiple instances of db2, or multiple logical-nodes, or multiple databases - they each need to use a portion of that according to need. So tuning can be either time-consuming (if manual) or easier (though possibly with a lag between supply and demand) with automatic tuning.

    If you are failing to connect/activate a database due to bufferpools sized too large for the hostname, read the docs about DB2_OVERRIDE_BPF.
    Last edited by db2mor; 02-20-15 at 09:04.

  7. #7
    Join Date
    Sep 2013
    Posts
    34
    Hi,

    I have increased no of pages of IBMDEFAULTBP bufferpool but still got the same error for sql query.

    i.e SQL1218N There are no pages currently available in bufferpool "4096" SQLSTATE=57011.

    So is it possible to increase PAGESIZE of IBMDEFAULTBP bufferpool or should i drop this bufferpool and recreate it with larger pagesize?

    Please suggest.

  8. #8
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I see that your error message is about "IBMSYSTEMBP4K" bufferpool.
    In that case, increasing IBMDEFAULTBP would not help. You should read about DB2 hidden bufferpools.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  9. #9
    Join Date
    Sep 2013
    Posts
    34
    But to avoid the use of IBMSYSTEMBP4K hidden bufferpool i need to increase the size of IBMDEFAULTBP . So Self tuning (STMM) is the only option for this?

  10. #10
    Join Date
    Sep 2013
    Posts
    34
    I have enabled STMM but still getting the same error.

    Please suggest the solution.

  11. #11
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Give more information.

    How much RAM is on this linux box/lpar ?
    How many instances of db2 are running ?
    How many databases are active in each db2-instances ?
    Is this a DPF environment ?

    What is the output of this query for each active database :
    db2 "select bpname,bufferpoolid,npages,pagesize,blocksize,numb lockpages from syscat.bufferpools"

  12. #12
    Join Date
    Sep 2013
    Posts
    34
    1.RAM memory status:

    total used free shared buffers cached
    Mem: 3035 2886 148 0 41 2396
    -/+ buffers/cache: 447 2587
    Swap: 34295 18 34276

    2. Only 1 instance of db2 is running on system
    3. There is only 1 database on the system
    4. It is nit DPF env.
    db2 "select bpname,bufferpoolid,npages,pagesize,blocksize,numb lockpages from syscat.bufferpools"

    BPNAME BUFFERPOOLID NPAGES PAGESIZE BLOCKSIZE NUMBLOCKPAGES
    -------------------------------------------------------------------------------------------------------------------------------- ------------ ----------- ----------- ----------- -------------
    IBMDEFAULTBP 1 8192 4096 0 0
    FDX8K 2 500 8192 0 0
    FDX32K 3 550 32768 0 0
    BPDATA_4 4 32768 4096 0 0
    FDX4K_DS 5 500 4096 0 0
    TEMPBP 6 500 32768 0 0

  13. #13
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    You have overcommitted the memory, by allocating bufferpools whose total size exceeds the system RAM, which you must not do on a db2 server.

    Your bufferpools are still fixed size, the recommendation is to have them auto sized, which you can do like this (which assumes you already correctly enabled STMM):

    alter bufferpool ibmdefaultbp size automatic ;

    ...repeat the above command for every bufferpool that you have.

    then deactivate the database, and activate the database, and check the symptom again.
    Study the docs carefully if you don't understand the advice.

Posting Permissions

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