Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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. |

share|improve this question
Presumably you are asking about DB2 LUW, not DB2 for i or z/OS. – WarrenT Nov 13 '13 at 17:05
That is true, I am working on LUW, specically Linux. – AngocA Nov 13 '13 at 17:07

Since most of the stuff you're monitoring (locks, bufferpools, SQL performance, etc.) is database-specific you will still need to connect to each database to fetch the information, so I don't see what's wrong with using one of these connections to get instance- or server-wide monitoring information as well.

share|improve this answer

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, ...).

share|improve this answer
Good call. So it really comes down to AngocA's company policies (if they exist) as to whether connecting to each database is a concern or not for monitoring purposes (and also probably tech stack, easy of maintainability, etc.) – Chris Aldrich Nov 13 '13 at 19:09
Optim Performance Manager does not have any server-side agents, as far as I know. It indeed polls all monitored databases (which is one of the reasons for its poor performance when monitoring databases with high load). – mustaccio Nov 13 '13 at 20:13
@mustaccio Thanks for the correction. – Ian Bjorhovde Nov 13 '13 at 20:51

First, wondering if you could create a user to SET SESSION USER into for querying this? Or create a service account that can only connect and run these?

Second, have you also considered the db2mtrk command? This can be run at the command prompt without a database connection. See Information Center.

share|improve this answer
Memory tracker could be an option for this specific scenario, but this is not the only one. Also, Administrative views and table functions are much more powerful and flexible that doing greps and awks over the command output. – AngocA Nov 13 '13 at 14:26
About the other part, the "SET SESSION AUTHORIZATION", it is used once the database connection is established. But my problem is where to establish the connection, because I want to execute administrative views against monitor elements at instance level. – AngocA Nov 13 '13 at 14:41
WEll, you would have to connect then I guess. If you are worried about physical access to the box, you could use the DB2 client and/or something like IBM Data Studio. But, yes, you would still have to grant CONNECT access. You could build an extra database, but that seems to me like overkill, unless you are going to use it more for DBA functions, storing trends and what not about your differing databases. Otherwise why not just connect to the other databases and just revoke what you don't want them to get at? – Chris Aldrich Nov 13 '13 at 16:33
That is what I am asking: what could be a best practice. I want to know if you, community, have faced this kind of issue. BTW, I am going to use the data for monitoring purposes (Nagios->Free), and not for direct query (no data studio). – AngocA Nov 13 '13 at 17:01
The problem to give permissions in other databases, is that those permissions are not related to the database itself. Why a user called NRPE has connect and other rights in the database? I am not looking for a how to do it, but what is the best practice. BTW, I gave the necessary permissions in all databases and it works quite good, but it does not look like a good practice. – AngocA Nov 13 '13 at 17:04

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.