Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    6

    Unanswered: HELP!!!..How to start stopped Bufferpools???

    Dears
    I am working on a production transactional database DB2 v(9.5.3)..suddenly when i run a normal select query the DB2 returns:
    SQL20169W The buffer pool is not started. SQLSTATE=01654
    when i search for this on IBM info center it indicates that to start the bufferpools i should drop and recreate the bufferpools!!!!!

    Is there any other way to start the bufferpools without dropping and recreating the Bufferpools ???

    please Help as this issue was repeated many times...

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Most likely the bufferpools you have defined are too large for the amont of memory available on the server. You probably need to alter some of them to reduce the size.

    It might help if you could provide the following information:
    • db2 "select * from syscat.bufferpools" (for every database on the server)
    • the total amount of memory on the server
    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
    Mar 2008
    Posts
    6
    Dear Marcus
    Thanks for your quick reply..
    The SYSCAT.BUFFERPOOLS Data is:
    BPNAME BUFFERPOOLID DBPGNAME NPAGES PAGESIZE
    IBMDEFAULTBP 1 -2 4096
    BP_CONFIG 2 300 8192
    BP_LOOKUP 3 700 8192
    BP_BILLING 4 99300 8192
    BP_AUDIT 5 22400 8192
    BP_AUDIT_LOB 6 2800 32768
    BP_PAYMENT 7 9740 8192
    BP_MSGLOG 8 46700 8192
    BP_MSGLOG_LOB 9 14600 32768
    BP_ERROR 10 7500 8192


    The installed memory at the machine is 4608 MB...

    Thanks in Advance

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure all the tables that are associated with IBMDEFAULTBP in your database, but I would change the -2 to some fixed number. If only the catalog tablespace is using IBMDEFAULTBP, then set it to 3000 pages. If you have temp tablespaces using it, then assign them to different tablespaces.

    If the only tablespaces using BP_MSGLOG_LOB and BP_AUDIT_LOB bufferpools are tablespaces that only have LOB columns, reduce them to 250 pages. LOB columns do not use bufferpools, even though you must assign a bufferpool to every tablespace.

    Slightly off topic, but IMO you have too many bufferpools. For most databases, "something like" this usually works better:

    • IBMDEFAULTBP - catalog tablespace only - 3000 pages
    • SMALL_8K_BP - small reference tables, and indexes on small and medium size tables (30% of bufferpool memory)
    • MEDIUM_8K_BP - medium size tables, and indexes for large tables (30% of bufferpool memory)
    • LARGE_8K_BP - Large tables, and system/user temp tables (30% of bufferpool memory)
    • 32K_BP - all 32K tablespaces - size depends on whether you have any non-LOB 32 Tablespaces.
    Last edited by Marcus_A; 04-03-11 at 12:25.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Mar 2008
    Posts
    6
    Dear Marcus
    No problem with all what u said..but the bufferpools were up and started yesterday and suddenly they are all down !!!!
    the size of the physical memory is enough for all these buffer pools to be allocated... i hope u have an explanation for this

    another question is Is there any other way to get these bufferpools up without dropping and recreating them??

    Thanks for your reply and patience http://1.1.1.5/bmi/www.dbforums.com/...s/rolleyes.gif

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The fact that the bufferpools were up yesterday, does not mean much. If a bufferpool is set to -2 the size various continously, which can be a problem. Also, you may other things on that server using memory.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Suddenly the bufferpool is down? Is the database activated? Is the instance stopped and started in the mean time? Sounds fuzzy to me.
    What might help in your case is the registry variabele "db2_override_bpf". This will make sure that the all the bufferpools are started with a fixed size (specified by that variabele). Once the database is up-and-running you can alter the bufferpools to set the required sizes or let STMM pick it up from there. Not ideal, but everything is better than a database start with hidden bufferpools.

Posting Permissions

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