Got 5 Minutes to get some expert tuning advice?

I thought I'd start looking at the various configuration parameters that exist in DB2 and try to provide a one stop shop for the key config parms that you need to know about. But before I get into the details, how would you like the some tuning advice from the people that build DB2 and you can have it in about 5 minutes. Take a minute to try out the Configuration Advisor. What's the Configuration Advisor? It's a tool that lets the people who developed DB2 tune your config parms for you. Ok maybe that's a bit of an exaggeration but it's not far from the truth. This advisor has the intelligence built into it by the folks that created the configuration parameters in the first place. The people that tune the DB2 benchmarks for a living helped to build the algorithms into the advisor. In tests I've seen it get pretty quickly within 80% of a professionally hand tuned system. Not a bad start for a tool that takes less than 10 minutes to go through. Now I hear people reading this saying "but I have already professionally hand tuned my system so why bother". Well with almost 200 database and database manager configuration parameters, do you think you got them all optimal or even near optimal? I know of one situation where the configuration advisor, run on a production transactional system, improved the performance by 50%. You may think that DBA didn't do their job but we all know we have 50 million things to worry about each day and one obscure configuration parameter that may or may not apply to me is not going to get my attention if no one is screaming at me that performance is slow. So my first recommendation to you is to go through the configuration advisor and save its recommendations, compare them to your existing settings and do a bit of research on the changes it is suggesting. You may be pleasantly surprised.

Before I go into the details on the parameters themselves, let's take a closer look at the details of the configuration advisor. As with pretty much everything in DB2, you can use the graphical interface or the command line to run the advisor. First the graphical interface…

Start the control center (which by the way you can start from a command line by typing db2cc - see I told you that you can do everything from the command line, hahaha)

Right click on a database and select "Configuration Advisor…" from the pop up menu.

The first screen is just an introduction so like most people you won't read it you will just click next. But if you take the time to read it you will notice that the advisor assumes that you have one database in the instance you are about to get advice on. If that's not the case then make sure you take a look at the memory recommendations more carefully as you may have to consider that two databases will be sharing the instance (and the server for that matter)

On the next page you can specify how much memory you want to give to this database. Note that it shows you how much memory is on the server. If you have other applications running on the same server and/or other database instances, make sure you move the slider down to allow for enough memory for the other stuff on the same box.

The next question is pretty straight forward. It asks if this is a data warehouse or a transaction system (or if it's mixed).

The next question is asking you about a typical transaction for this database. Does it have more or fewer than 10 statements in a unit of work? Also how many transactions do you expect (or want) to have running per minute.

The next question is asking you to make a trade-off between faster recovery and faster transactional throughput (or split the difference). Changing the logging parameters can impact your performance but can give you faster recovery. So choose to have your cake or choose to eat it (or just eat the icing like my kids do).

The next question should be the easiest of all. Is the database populated with data? If you are not sure, perhaps you should exit out of the advisor now and ask your doctor to check if you have amnesia and can't remember how long it took you to load your database. Seriously though, this question is asked so that DB2 can look at the size of the objects to make some more specific recommendations. If the database changes rapidly, you may want to rerun the advisor and see if any recommendations change.

The next question asks about the number of local and remote application you expect will be running at any given time.

And the final question is the type of isolation level you expect your applications to be running in.

That's it. Now you can run the advisor by clicking next to see what recommendations it gives you (and you can save the job as a task so you can run it again later with the same input values). The next page that pops up (rather quickly I might add), shows you the current value for all the configuration parameters it makes recommendations on and what the recommended values are. Any recommended values that differ from the current values are highlighted in bold. You can choose to apply the recommendations or save them to a file so you can mull them over and make your own decisions later. If you don't want to apply the changes, make sure you click Cancel at this point.

To do the same thing from the command line you can run the following:

db2 AUTOCONFIGURE USING keyword value APPLY NONE

The APPLY keyword of NONE tells DB2 to just show the recommendations and not to change anything. If you actually want to apply the changes you can use APPLY DB ONLY which will only make database level changes or you can use APPLY DB AND DBM which will make all the recommended changes to the database and the database manager config parms. Keywords and values are as follows

Keyword

Valid values

Default value

mem_percent

1-100

80

workload_type

simple, mixed, complex

mixed

num_stmts

1-1,000,000

10

tpm

1-200,000

60

admin_priority

performance, recovery, both

both

is_populated

yes, no

yes

num_local_apps

0-5000

0

num_remote_apps

0-5000

10

isolation

RR, RS, CS, UR

RR

bp_resizeable

yes, no

yes

So for example you could run

db2 autoconfigure using mem_percent 50 workload_type complex isolation cs apply none
and on the screen will be the list of config parms and recommendations.

In the next posting I'll go through more details on the dbm and db config parms.

22 Comments

Thanks, Chris - this is exactly what I was looking for=2E I used=
the configuration-advisor GUI in db2cc which built a very nice=
script for me to save and keep handy for later reference - or so=
I thought=2E But when I attempted to cut and paste out of the=
db2cc GUI - it was no go on the paste side (it seems like text=
is making it to a clipboard - but you can't put it anywhere)=2E =
Can you or IBM recommend some editor or method for handling=
this=2E From doing a couple of google-searches on the topic -=
there are apparently others out here looking for a solution on=
this=2E And - as a followup to the above - as one is unable to=
paste the config-advisor script produced by the db2cc GUI, it=
would be great to direct a script to a file in a directory from=
the command line with a parm or some series of options=2E But=
this also appears to be unavailable=2E Would appreciate your take=
on this - Thanks, Jim

Hi Jim, thanks fo rthe comment=2E There are a few ways I know of=
to do what I think you want=2E
Firstly when you run the configuration advisor on page 9 where it=
says "Schedule" you should click on the "Create this as a task=
in the task center" instead of run now without saving=2E You can=
schedule the task or save it AND run it now=2E What this does is=
it puts the recommendations into a script in the task center=2E =
On the results page you now have the name of the task and in the=
task details you can see all the recommendations=2E On the top=
right of the results page is a button to Export scripts=2E Click=
there and you can save the script to a file and edit with=
whatever editor you want=2E
At any point after this, you can also open the task center=2E =
There you can find this task and if you right click on the task=
you can "Edit task properties" which has a bunch of tabs to edit=
the task=2E The second tab is the command script giving you the=
recommendations=2E You can cut and past from here=2E The other=
option is to click on "Edit with Command Center"=2E This will=
bring up the recommendations in the command center interface=
where you can actually make modifications if you want or add=
your own comments and then click on "Selected -> Save" to save=
the command script to a file=2E
Hope that is helpful=2E

Thanks again - that worked great=2E I liked the fact that I was=
able to build the tools database (which I had neglected to do=
during install) while scheduling an initial task=2E I now have a=
saved script which I'll be using for reference so I'm good=
there=2E The one persistent problem however is with the editors=
in control center and their seeming inability to do=
cut-and-paste=2E I popped open the task on page 10 under "Task=
creation summary" where the script shows up under the "task=
details" text-area-box=2E I can highlight and cut - but can not=
paste to notepad or any other editor=2E Can you paste? I'm=
wondering what I need to do to make paste happen=2E There are=
similar problems when I attempt to paste in SQL into the EXPLAIN=
editor=2E Hope IBM can shed some light on this=2E
Meanwhile - thanks again, Chris=2E I have my script so mission=
accomplished=2E

Hi,
What version of DB2 are you using?
I cannot recreate your problem with V8.2 FP9... I remember seeing some text-areas where the mouse was not enabled but usually, you can use the keyboard:
shift + arrow keys to hightlight
ctrl-c to copy
ctrl-x to cut
ctrl-v to paste
Regards,
Richard Page
DBA Tools Development

Hello,
I'm running V8.2 FP9 on zLinux - but I've had the same experience with control center editors on pSeries instances as well. I just now opened the Explain SQL editor in db2cc (V8.2 FP9, pSeries) and did the following tests:
1. copy text from notepad and paste into Explain SQL editor
2. copy text from Explain SQL editor and paste into notepad.
Both tests failed. I repeated the tests with a different text editor (Ultra-Edit). These also failed. I use the CNTL-C and CNTL-V commands to copy and paste. I just now did a test using CNTL-X and "cut" also fails.
I am encouraged that you are able to cut and paste from and to db2cc editors. I had almost written them off entirely for this reason. What do you think/recommend?
Thanks,
Jim

Sorry Jim, when I read 'notepad', I automatically assumed you were working on Windows. I'll redo the test on Linux shortly.
Given that we use java, the cut and paste problem might be caused by the jdk we use on Linux but there might be some ways to work around the problem as well. I'll let you know what I find.
By copy to Chris, I went to your blog looking for Jim's response but did not see it... can you tell me where I can find this thread?
Regards,
Richard Page
DBA Tools Development
IBM Toronto Lab

Hello,
How do you set "isolation level" to UR in an dynamic sql environment at the db2 server level?
The default setting is CS. The users query the database using Cognos reporting tool. We did try changing the client setting , but the queries are still being submitted as CS.
We have AIX DB2 udb 8.2.
Thank you very much in advance

When you say you tried "changing the client setting" does this mean that you updated the db2cli.ini file on the client and added in the parameter
TxnIsolation = 1
If that doesn't help then there may be a setting inside Cognos to set the isolation level. Which Cognos tool are you using? Powerplay?

HI Chris,
I have plans to run many smaller databases currently estimated to=
be about 40-60 on Sun Solaris DB2 WSE Unlimited 64Bit v8=2E2=2E The=
databases all have the same schema repeated in all the=
databases=2E The bufferpools are set as 1200 4K pages for each=2E=
The database sizes is about 300MB-600MB is most of them=2E
It is estimated to be max of 1-6 GB per year=2E Do you see any=
problem in this approach=2E

Are you running 40-60 databases on a single 4way server? How busy are these databases (in terms of concurrent connections and transactions per minute).
Just curious, why do you have 40-60 databases all with the same schema?

Simon,
I wonder if you could clarify please.
You say:
"The next question asks about the number of local and remote application you expect will be running at any given time."
and the command reference says:
"Number of connected remote applications"
So after monitoring trends over time would you expect to use the db cfg of
Applications connected currently OR
Appls. executing in db manager currently
As with AVG_APPLS I am not always certain what is being asked for.
Thanks.

Hi Graham, the number you want to use here is "Applicationed connected concurrently". Not necessarily executing as it wants to know now many connection requests there are concurrently.
Even better news...with DB2 Viper, these values will be able to take the value of AUTOMATIC (in which case DB2 will manage them automatically).

Hi Tina, I haven't seen this specific error before on autoconfigure. Are you running this from the database server? If so what version of DB2 (and what platform) are you running? If you are running this from a client, what is the client version and what platform and version is the server running on?

When specifying a value for mem_percent of 20 does this include memory used at the instance_level, or does this strictly apply to the database? For example, if I have 4 instances (each having 1 database) on a server and specify mem_percent 20 for each, does that guarantee me that the overall DB2 footprint (for all 4 instances) will be no larger than 80% of system memory?

Hi Scott, then intention is that if you had 4 databases (in 4 instances) and you specify 20 as mem_percent for each then DB2 would use roughly 80% of the total memory on the box. Having said that, there is no guarantee because in DB2 v8.2 and 9.1 there is also private memory considerations that could be much higher with high number of users. In DB2 9.5 with the treaded engine and with STMM in DB2 v9.1 there is a much better solution for ensuring the system is not paging significantly at the OS level.

Chris,We are running DB2 v9 on a unix server. We do not have db2cc gui installed yet. When "db2cc -h" is typed, I got following error message: sh: /usr/java13_64/jre/bin/java: not found.DB2JAVIT : RC = 127

If I have multiple v9.1 instances sharing a single server and utilizing STMM memory tuning, does the autoconfigure mem_percent need to be changed each time I create a new database, or does STMM automatically account for the other databases/instances that are on the server? I want to take advantage of STMM, but I'm concerned that on a shared DB server, the memory needs of one instance might impact the performance of another instance. How does STMM safeguard against this, and does it have anything to do with the Autoconfigure settings?

Hi dbscott,The automatic configuration mem_percent just helps with the initial configuration. After that STMM takes over (assuming you have it on) and will manage memory between multiple instances/database on the same box based on need. So if one database is really hurting because it is memory starved in some way and another database is not hurting, STMM will take some memory from the happy database to give it to the sad database to reduce it's pain.

You can of course limit the amount of memory one database would be allowed to consume (even if more memory would help it without causing pain to others) if you wish by using the DATABASE_MEMORY parameter to cap the memory for that database.

Disclaimer: Blog contents express the viewpoints of their independent authors and
are not reviewed for correctness or accuracy by
Toolbox for IT. Any opinions, comments, solutions or other commentary
expressed by blog authors are not endorsed or recommended by
Toolbox for IT
or any vendor. If you feel a blog entry is inappropriate,
click here to notify
Toolbox for IT.

Chris is a DB2 for Linux, UNIX and Windows lifer having worked at IBM on DB2 since its inception. Follow along as Chris shares ...
more

Chris is a DB2 for Linux, UNIX and Windows lifer having worked at IBM on DB2 since its inception. Follow along as Chris shares his experiences and provides tips and techniques for getting the most out of DB2 including how to exploit new features and releases as they come on-line.
less

Receive the latest blog posts:

Share Your Perspective

Share your professional knowledge and experience with peers. Start a blog on Toolbox for IT today!