Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2009
    Posts
    42

    Unanswered: npages is -1 /-2 in syscat.bufferpools

    Hello,

    What does it mean when npages is -1 or -2 in syscat.bufferpools?

    Thank you!

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    According to DB2 V9.5 Information center:

    -1 indicates that the buffer pool is to use the BUFFPAGE parameter as the number of buffer pool pages.

    When self tuning is enabled for a buffer pool, the NPAGES field in the SYSCAT.BUFFERPOOLS catalog view for that particular buffer pool will be set to -2. When self tuning is disabled, the NPAGES field will be set to the buffer pool's current size.

  3. #3
    Join Date
    Apr 2009
    Posts
    42
    Thank you. If I have one bufferpool per database, should I leave it automatic and let db2 manage it or set it to some specific value?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That depends on how much faith you have into STMM, which skills you have for tuning the system, and which workload you run. Given your questions, I would suggest that you just set everything to automatic.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2009
    Posts
    42
    We're running 9.1 on AIX with 16GB of RAM. One instance, 5 local databases. The server is dedicated to DB2, transactions are mostly OLTP.

    As you suggested, I can set everything to automatic and let DB2 do the "tuning". Once DB2 finds the "optimal" numbers based on the average workload, I want to hardcode the bufferpools (one per database) and leave everything automatic.

    Would you recommend this approach? What % of memory would you suggest I give to bufferpools assuming all 5 databases have the same workload? What about database_memory and instance_memory, should I hardcode them or leave automatic?

    Thank you!

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by db2zip
    leave automatic?
    That's what I should do. Take care that all the 5 databases stay acitvated to prevent the other 4 to consume the resources.

  7. #7
    Join Date
    Apr 2009
    Posts
    42
    Let's assume I have 4 databases active and almost all memory is consumed by them. Then I start the 5th one. Will db2 "borrow" memory from other databases and give it to my 5th database or return an error messages saying out of memory? I think DB2 should be aware of all databases.

    Thank you!

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Please read this: http://www.dbforums.com/db2/1641799-...configure.html

    Regardng your question: Yes, DB2 will borrow (as you call it) memory resources from the other 4. The problem is the start-up of the 5th DB. When there is no room for the bufferpools, db2 will fall back to the "hidden" ones. This is a situation you do not want. If you do not notice at first (db2diag.log) you'll notice very bad performance for that database very soon. The only way to correct this "mis-init" is to free enough space and STOP/START the database.
    Another option is to avoid this situation is the use of the DB2_OVERRIDE_BPF parameter. That's why I asked you to read the thread mentioned earlier.

Posting Permissions

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