Best practice to query administrative views for instance level elements I want to query a monitor element at instance level (for example the memory used for the instance) via a query like this:

SELECT MEMBER, MAX_MEMBER_MEM, CURRENT_MEMBER_MEM, PEAK_MEMBER_MEM
FROM TABLE(SYSPROC.ADMIN_GET_MEM_USAGE()) AS T


However, in order to execute the query I need to be connected to a database. What method do you use to query this kind of elements (instance level) and establishing a connection (database level):


Connect to the first database available in the db directory.
Create another database just for monitoring purposes.


1) For this scenario, I have to give the necessary grants in at least one database of each instance.

db2 list db directory | awk '/alias/ {print $4}'
db2 connect to $i
db2 -vf query.sql


2) In this scenario, I have to create a database for monitoring purposes in each instance of each server, and I give the necessary grant in this database.

db2 connect to monitodb
db2 -vf query.sql


I have hundreds of databases, distributed in many instances on different servers. And for this reason, the way to do a query creates a great impact in the set of database I am currently administering.
|
This is the classic question / fight over whether monitoring should have a monitoring agent installed on each server, or if it should be "agentless".  

With an agent installed on each server, a process/script/etc. wakes up every so often, collects data, and reports it back to a central location (i.e. monitoring server).  

With an agentless solution, the central server polls each monitored server/database/etc on a schedule to collect the information.  Cacti and Nagios typically use this solution.

There are pros and cons to each method.  There is not necessarily a best practice for which method to use, discussion usually just results in a holy war (similar to Emacs vs. vi, DB2 vs Oracle, ...).