AIX 5.3
DB2 8.2

We have been receiving complaints that the system is slow. It is when the user uses the application's client user interface that slow performance is noticed and massive paging faults are noticed by support staff.

One thought is that DB2 is configured with settings that are set for more memory than what the system actually has. The system physically has 4G of RAM. If this is the case, would DB2 cause the massive paging or would DB2 just use what it could of RAM?

My task is to prove or disprove that DB2 is the culprit and fix DB2 settings if it is DB2 that is misbehaving.

I doubt this is related, but the SPM name value concerns me. I am not sure what SPM is or does, but I am reasonably certain we do not use it. I know the value is based on the first so many letters of the host's name. The problem with this is that with our naming scheme it is possible for more than one of our systems to have the same SPM name value. How do I set this parameter to have no value?

Part of what I need guidance on as well is how to record or properly identify performance statistics.

Thanks.

You should never allocate more memory for DB2 than is free on your system as real memory. This can be hard to determine because AIX will start using free memory for disk caching, but will give it up if someone asks for it.

The main use of memory are the bufferpools, so add up your bufferpools and post the results here (select * from syscat.bufferpools). Also, tell us how many instances and databases you have, and whether they are 32-bit or 64-bit instances (db2level command).

You can run topas as root and see how much paging is going on. On a 32 bit db2, you CANNOT allocate more than 1.75gig for db2. On AIX, The default maxperm% of 80 should be set to 25 to reduce filecache. minperm% reset to 2; can run vmo -L to get the values. If you use jfs2, other parameters need to be tuned. Experiment with these changes and see if paging decreases.

If we did allocate more memory than is free, it certainly was not on purpose. There is only one instance and only one database for that instance. It is a 64-bit instance.

Sorry for the lack of better presentation, but here is that query you suggested:

$db2 "select * from syscat.bufferpools"

BPNAME BUFFERPOOLID DBPGNAME NPAGES PAGESIZE ESTORE NUMBLOCKPAGES BLOCKSIZE NGNAME
-------------------------------------------------------------------------------------------------------------------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ------ ------------- ----------- --------------------------------------------------------------------------------------------------------------------------------
IBMDEFAULTBP 1 - 132986 16384 N 0 0 -

1 record(s) selected.

So, if I did my math correctly, this buffer pool is just over 2G, is that right?

Thanks.

We are using JFS2. I am curious what other params should be changed due to JFS2 and is there any justifying documentation for your suggestions?
Thanks,
-Gary

Yes, but you may not have that much free memory. I would try to cut it in half and see if that helps, and slowly start raising it until you see a problem.

But 2GB is a very large bufferpool for most applications, so you need to determine if you really need that much and if it really helps bufferpool hit ratio.

Since you don't seem to have a 4K bufferpool, then I assume that your system catalog tablespace size was converted to 16K? Personally, I don't think that is a good idea. Usually only very large tables (number of rows) and very large indexes used in a data warehouse application (frequent table scans) should be on tablespaces with a 16K pagesize, unless the row will not fit on a smaller page size.

Thanks again. I will likely try to do as you suggest.

Most of our DB2 systems are for use with SAP and the current SAP install sets a uniform 16K pagesize for everything. The system in question here is not for SAP (directly anyway), but the SAP systems' param values are what we used for examples. It is becoming obvious that this was probably not the wisest thing to do.