Sizing of DB2 Bufferpools

[#1]

We are looking for the most effective/proactive way to keep track having optimally size Bufferpools. We periodically increase the DB2 Bufferpoolsizes based increases in Physical Reads and Hitratios; Are there any good rules of thumb to sizing of DB2 Bufferpools?

[Former Member]

You may need a z/OS buffer pool tuning tool. There are ones from IBM, ESAi, BMC, etc. If you are looking for 'proactive' alerting between tunings, then BPA4DB2 product will do it.

[KayKa]

Hi Elena,

your question is one the most interesting question on DB2 z/OS. But as you see here in the forum, there are only few answers.

I think we have 2 ways to go.

The first way is the SAP DB2 Admin Guide with a standard layout for around 1 GB of bufferpool, for a BW system there a some additional bufferpools.

The other way is following the transaction DB2B, as mentioned by Brian.

My way is the first one, but i have only some 30 systems for our own development. So i have to reduce the bufferpools in the most systems because of my limited amount of RAM.

On the other hand, i'm involved in some migrations on real production system on customer site. And here i have some more GBytes to play.

So i double or triple the amount of buffers for the "little" bufferpools and then put the rest of the RAM into BP2/BP3. The other parameters are setting according to the SAP DBA guide.

Here is an example of one of our production system:

-

---

BP name

VP size

-

---

BP0	130.000
BP1	90.000
BP2	750.000
BP3	750.000
BP30	150.000
BP31	150.000
BP40	9.000
BP8K0	15.000
BP16K0	6.000
BP32K	7.500
BP32K1	1.000

-

---

The bp30/bp31 are used for 4 ICL-tables and their indexes. In other systems we separated the DFKKOP and her indexes in some other bufferpools. But this depends on our own product we developed on top of SAP functionality.

Until SP10 one could monitor the bufferpools in the transaction st04 / Subsystem Activity / Bufferpool Activity. There a button called "List all" or something like that. In these list you could see all of your bufferpools and their values. You could easily recognized where were the hotspots.

Now we have only a table with the bufferpools and some values. If your are looking for hit ratios, getpages and so on, you have to go the details for every buffpools. I miss the old list.

With SP16 the button reset and since reset will work again as before. This helps a lot.

I prefer a design with few bufferpools, because it is better to monitor. I don't have a tool on the host to monitor and on customer systems i don't have an account to use it. So i'm looking only through the dbacockpit on the db2.

On one of our customer systems they used the db2b functionality and they used a lot of bufferpools. But i don't think their system runs much faster or better as the systems with standard layout. But for me it is harder to understand how their bufferpools will work ;-(

What i recognized during testing the db2b is that the generated "alter tablespace" commands will also handle the indexes. So you have to separat the indexes manually afterwards. And the other thing is that you can use the db2 every day. And every day you get new advices for your bufferpool layout. A neverending task.

hth

best regards

Kay

[brian_walker]

Kay obviously has a lot of experience here. I spoke to my DBA and she also suggested that if you happen to have a tool like OmegaMon, it can collect bufferpool usage information periodically so that you can determine if/when a particular bufferpool runs out of space and then try to correlate that event with what may have caused it in DB2. If it is a monthly job, then perhaps you just ignore it. If it happens multiple times per day, perhaps further tuning can/should be done.

Our DBA also said that some of the tuning techniques will depend on what version of DB2 you're on. We happen to be on DB2 v9 (the latest) and the advice we have been given there from IBM jives with what Kay said. For DB2 v9, it is better to have fewer but larger bufferpools, and thus we do not have all the bufferpools that the DB2B transaction suggests.

Perhaps if enough SAP on DB2 z/OS customers asked, we could get a better tool from SAP for bufferpool tuning? Or perhaps we could ask IBM to give DB2 z/OS the same auto-tuning abilities as DB2 LUW has? Either of those options make a lot of sense as long as zSeries memory continues to be more expensive compared to memory for a similar DB2 LUW system.

Brian

[Former Member]

Hi

[this link|http://www.ibm.com/developerworks/data/library/techarticle/0212wieser/0212wieser.html] may be helpful for you.

Thanks & Regards

Arun

[brian_walker]

From the above link:

"This article is written for IBM DB2 Universal DatabaseTM for Linux, UNIX®, and Windows ®"

If Elena was truly talking about DB2 z/OS, some things mentioned in the article won't apply to DB2 z/OS. Specifically, there is an auto-tuning feature in DB2 LUW that will adjust various parameters like bufferpools on the fly. SAP recommends turning this on for DB2 LUW, but this does not exist for DB2 z/OS.

The best thing I have found from the SAP perspective is to use the DBACOCKPIT under Configuration -> Bufferpool maintenance and clicking the button "Simulate BP settings". Sadly, this just asks for input the amount of storage to commit to bufferpools and then it suggest the sizes and parameters of the various bufferpools. It appears not to take into account actual usage patterns of the various tables and in fact provides the same recommendation for ECC as for BI when the same amount of storage is input. Perhaps the suggested bufferpools for tables does a bit better since it is based on table usage patterns?

I believe there are some DB2 z/OS specific tools to run in z/OS that might help in determining the optimal bufferpool sizes, but I am a Basis guy and not a DB2 z/OS DBA.

Brian