asked on

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

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gheist

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.

Enyimba

ASKER

Thank you for your help. I am still working this problem

SOLUTION

gheist

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Enyimba

ASKER

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

gheist

Thats the cryptic part hidden in tivoli agent documentation. Me no longer their customer.

gheist

DB2 datastudio uses SNMP agent built into DB2 to monitor it.
It configures it on windows. Probably on AIX too.

Enyimba

ASKER

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

gheist

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

Enyimba

ASKER

Thanks guys