db/2 KPI for 9.5/9.7

Hi Im looking for db/2 9.5/9.7 KPI to monitor regularly the performance.

Can you give the major ones, which I should look daily, Pls let me know the threshold value as well.. thanks
bsarahimAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Hi bsarahim,

Apologies...  I didn't see your previous response come through....

A common set of KPIs may not be possible or practical.  Given your topogarphy, it'll certainly be a challenge to establish them without limited knowledge of you systems.

For most systems, physical I/O is the most critical item.  There's an old adage among mainframe guys that says "all processors wait at the same speed" and that's especially true in databases.  If the database manager is waiting for data to be read from disk, it's doing nothing.

With a diverse mix of databases, you could easily have a requirement that response times from some of them are subsecond, but response times from the data warehouse or other analytical systems in the minutes is acceptable.  As such, a common KPI would be interpretted quite a bit differently.

Look for these items:
  Number of queries,
  Min, max, avg query times,
  Physical I/O,
  Peak I/O rates,
  Sustained I/O rates,

With those numbers in hand you should have a good idea of any more detailed items that you might want.


Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi bsrahim,

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


Kent
0
 
momi_sabagCommented:
you will have to be a bit more specific than that...
0
The Firewall Audit Checklist

Preparing for a firewall audit today is almost impossible.
AlgoSec, together with some of the largest global organizations and auditors, has created a checklist to follow when preparing for your firewall audit. Simplify risk mitigation while staying compliant all of the time!

 
bsarahimAuthor Commented:
yes we are experiencing a slow down and i want to be proactive checking
0
 
momi_sabagCommented:
do you have optim installed?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
bsarahimAuthor Commented:


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)
0
 
bsarahimAuthor Commented:
still no response.. Pls help
0
 
bsarahimAuthor Commented:
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
0
 
Kent OlsenData Warehouse Architect / DBACommented:

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
0
 
waynezhuCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.