db/2 KPI for 9.5/9.7

Hi bsrahim,

Are you experiencing a slowdown or is this for general maintenance?


Kent

you will have to be a bit more specific than that...

yes we are experiencing a slow down and i want to be proactive checking

do you have optim installed?

The next question is budget.  :)  If you're a large DB2 shop with a big IT budget, IBM's Rational Performance Tester is about as good as there is.  But it's not free.

That said, let's figure out what you have.

-- How old is the database?  
-- Was it properly designed to the hardware that you have in place?  
-- Is an older database that you moved from a different architecture?  
-- Is it an older database that you're trying to squeeze more performance out of?

Without knowing more about what you're running, the number 1 performance comment in a DB2 environment is "buffer pools".  Do you have enough?  Are they the right size?  Do your bufferpools overcommit memory so that the operating system is paging DB2 to/from the swap space?



If you want to read up on the basics of buffer pools and table spaces, IBM got a very good article here:

  http://www.ibm.com/developerworks/data/library/techarticle/0212wieser/index.html


Kent

i'm a management guy, who wants to keep monitor regular during this peak period.. and i have many db/2 instance in different servers..
So i'm looking common set of KPI's.. (where DBA will provide me the data regular daily basis and he recommendations with thresholds)

all this KPI/threshold on daily basis, I can report to the customer.

what are the things should dba look to provide me a report..(not a action/buffer pool recommendations are based on the KPI's, this a later which i will ask for action to do..)

Im looking for KPI's basically
for eg: active connections,time outs, errors in bd..(this what im looking)

still no response.. Pls help

Hi Kent..

can you give me the threshold of the each KPI.. which I will monitor and request from the dba on the daily basis...

currently im looking also topas command that will provide me the screen shot of the % of processor being used for each database.

pls let me know, if you have any other good thoughts or more KPI's for daily basis..

Let me know at the OS level as well at the db level

I can't help with threshold numbers.  They will vary, perhaps a lot, by your configuration, database design, workload, and needs.  

Monitor the key items record the numbers.  If/when database performance slips, compare these items and look for increased resource demands.  It may be that you can then tune the database and/or server, but it may also be that the increase is due to factors outside of your control, like increased usage or usage that's not consistent with the design (reporting on non-keyed fields, joins that generate a lot of rows, etc.)


Kent

To begin with, you may pick up few pertinent ones from IBM defined  health indicators
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.mon.doc%2Fdoc%2Fr0011402.html
such as
db.spilled_sorts
db.catcache_hitratio
db.pkgcache_hitratio
etc
It is preferable to develop your own script to calculate the indicator's values.
In this way, you can adopt more KPIs later easily.
See below for other possible KPIs:
http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/10a5f238-d09a-2c10-9a8a-d0a64f6a506b?QuickLink=index&overridelayout=true
http://docs.oracle.com/cd/E11857_01/em.111/b28748/database_db2.htm