How to collect DB2 performance counter values by using query?

[ambilalmca]

I am new to DB2. Now i am developing a java application yo collect performance counter values. So if any one tell me whats the query used to retrieve those counter values, please help me. I did it for Mysql by using " SHOW GLOBAL STATUS". like this i want a query to collect it.

Thanks in advance...

[Anas Mosaad]

What version of DB2 do u use?

[ambilalmca]

DB2 Database server versions from 8.1 upwards @Anas mosaad

[Anas Mosaad]

To my knowledge, only 9.7 and above is in support now -unless u are entitled for extended support. For DB2 9, please check the monitor table functions, snapshot views or may be db2pd command.

There are lots of stats to collect, it's not a single command to get everything. Know what you are looking for and you will find the appropriate view or table function.

[ambilalmca]

@Anas mosaad, can you tell me any sample command to view this?

[Anas Mosaad]

By command I guess u mean db2pd. Let me give you an example of collecting the buffer pools data. db2pd -d your_db_name -bufferpools. Note that the database has to be activated.
The same data but probably not the same format can be retrieved using other ways. For example, the CLP command "GET SNAPSHOT FOR ALL BUFFERPOOLS" will get the buffer pools data for you. Also SNAP_GET_BP will get the same data. The system view "SYSIBMADM.BP_HITRATIO" will also get you considerable amount of the same data. The MONREPORT module also can be helpful if you collect this info in SQL/PL or PL/SQL code.

You have lots of options and you need to decide based on your needs, applications and environment. My recommendation to you is not to reinvent the wheel and use one of the pre-existing tools like IBM Optim Performance Manager.
Also note that certain metrics are not collected unless you turn the proper monitor switch on.