Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

How do I collect DB2 Key Performance Metrics in a AIX environment?

Dear Gurus,

This is about DB2 LUW 10.5 on AIX machine. A new application is being readied for performance testing in a QA environment and I have several questions about detailed key performce metrics that I should collect and report on.

 I want to collect KPIs at
(a) the server level -         CPU utilization
                    Memory utilization
                    Storage utilization metric
                    IO utilization
                    Paging space utilization
                    Top utilization CPU, Memory and IO Process ids

(b) At the Instance level -   Instance available memory and used memory

(c) the database level  -   Database total memory
                    Transaction arrival rate
                      Transaction wait times
                    How long to collect this metrics (collect evey 5, 10 ,15 or 20 minutes interval)
                    Buffer pool hit ratio
                       Storage utilization metric
                    Package cache hit ratio (Total space and available space)
                    catalog cache hit ratio (Total space and available space)
                    Total number of connections and locks
                    Total number of reads, writes, updates and deletes on the database
                    Top n SQL involved in excessive locking activities
                    Top n SQL or othe agent using very high CPU utilizing
                    Top n SQL or other agents using very high log space.

  In all categories listed above, could esteemed members of this group and especially those with superior performance
  monitoring, analysis and tuning please jump in and share your expertise and wisdom on how best to go about collecting
  the various metrics, what tools could be used (I have optim performance Monitor), what native AIX tools such as IOstat,
  vmstat, db2pd, etcetc and how best to set all this up in a CRONTAB job for the capture...
 
  Please gurus, all and any suggestion with practical examples or links to information on how to set up such metrics capture
  will be highly appreciated.

  Thank you all a million!

  Enyimba
0
Enyimba
Asked:
Enyimba
  • 5
  • 4
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Enyimba,

There are a lot of web sites with performance tuning tips and tools for DB2.  Most have their good points.  But very few of them are as good or as comprehensive as the DB2 tools.

  IBM pages

Down the left side of the page (linked above) are links to IBM resources, written by some really good DB2 people.  Take a quick look and see if any of the Best Practice Papers mentioned cover your needs.


Kent
0
 
gheistCommented:
It looks very windows performance metrics.
How do you measure CPU utilization on frequency-scaling CPU?
AIX will mirror main memory to swap - that is just normal part of it's operation.
0
 
EnyimbaAuthor Commented:
Thank you for your help. I am still working this problem
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
gheistCommented:
You need to enable DB2 SNMP subagent and extract statistics using SNMP. Tivoli does that.
0
 
EnyimbaAuthor Commented:
I do not have Tivoli. How do I enable SNMP? Is it something a DBA can do or do I have to go through hoops with AIX system's Admins to do that? And what kind of performance metrics can I expect to find/get from SNMP? Of course I will read up a little on SNMP tonight but do educate me a little..

Thanks

Enyimba
0
 
gheistCommented:
Thats the cryptic part hidden in tivoli agent documentation. Me no longer their customer.
0
 
gheistCommented:
DB2 datastudio uses SNMP agent built into DB2 to monitor it.
It configures it on windows. Probably on AIX too.
0
 
EnyimbaAuthor Commented:
gheist,

Thanks for your response to this thread. If I may ask, could you detail how data studio configures SNMP on windows? Perhaps I could glean from that what I may have to do to configure for AIX which is the environment that host my DB2 instances... Any information or links to such information will be highly appreciated.

Enyimba
0
 
gheistCommented:
It adds snmp sub-agent.
Old old docs say it does same automatically on AIX:
https://www.columbia.edu/sec/acis/db2/db2p0/db2p046.htm
0
 
EnyimbaAuthor Commented:
Thanks guys
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now