we are migrating to DB2 v9.5.1 and AIX 6.1 at the same time.

- do I leave dbm and db cfg parameters same as in prev. v.8.2 or should I reconfigure (we noticed some performance degradation in v9.5.1).
- is new self tuning feature in v9 good to use for database memory tuning?
- what works better - self tuning feature or autoconfigure?

Thanks in advance

I would not recommend that you use automatic bufferpools in 9.5. Everything else automatic is OK.

I would concur with Marcus. My experience with automatic bufferpools was not good. It seems that DB2 kept resizing them over and over again without any let up. Everything else being automatic works really well.

Andy

So, it works as designed. What's wrong with that?
The bufferpools are constantly matched and optimized for the current workload.
So STMM and set to "automatic" as much as possible.

I have no experiences with automatic buffer pool management. The question is what kind of impact the resizing has. For example, if it causes additional disk writes because dirty pages have to be flushed to disk, it may indeed not be the best choice to use this feature.

So it comes down to some explanation from Marcus and Andy on what the issues were. At least I would be interested...

It was a while ago that I experimented with automatic bufferpools so the details are a little fuzzy. What I understand about STMM is that it will do a little tweaking here then do a little tweaking there and it eventually should reach a somewhat steady state. This seems to be what is happening with everything else, but when I turned it on for the bufferpools, it kept changing the sizes back and forth, it never really settled down. And it kept doing it several times each hour, over and over. And I think every time it did a change, I saw a slight performance hit.

Andy

Basically it gets down to a couple of issues.

First, If you have more than one bufferpool, DB2 STMM will treat them equally, but usually the reason for having more than one bufferpool is to keep certain tables from flushing other tables from bufferpool memory. For example, a DBA may have decided that certain tables/indexes will always be in memory and therefore be in its own bufferpool, while other tables/indexes may have to share the remaining bufferpool(s). STMM will take away these decisions made by the DBA and if one bufferpool needs more memory, it could take it away from another bufferpool (even if the DBA has decided that other bufferpool is supposed to always have a 100% hit ratio).

The other reason for not using STMM on bufferpools, is that many problems (including crashes) have been reported, including many I have witnessed myself. At some point I expect these to be fixed, but reason number 1 above takes precedence, and anyone who actually understands how to optimize bufferpools manually (admittedly not all DBA's do) would not want STMM taking that over in a manner that is less than optimal.

how do you assign a bufferpool to a particular table? create table in TS which uses a particular bufferpool? from your experience is it a good way to improve perfomance? thanks

My report (v9.5.2a on win2003 64bit): converted production database (100 GB) september 2008, use as many "automatic" parameters as I could find and it all works as designed. As the workload constantly varies, so do the sizes of the various memory objects.
The same database is (redirect) restored on other test servers and the memory consumption is automaticly altered to fit in the smaller server. A helpfull feature is the DB2_OVERRIDE_BPF. I use this parameter to give all the bufferpools an initial value after each database start. So the large values included in the backup-image are immediatly overrulled. STMM picks it up after that. No problems.

Look up creating tablespaces