1

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

Improve this question
2
  • Presumably you are asking about DB2 LUW, not DB2 for i or z/OS. Commented Nov 13, 2013 at 17:05
  • That is true, I am working on LUW, specically Linux. Commented Nov 13, 2013 at 17:07

3 Answers 3

Reset to default
1

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.

Improve this answer
1

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

Improve this answer
2
  • 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.) Commented Nov 13, 2013 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). Commented Nov 13, 2013 at 20:13
0

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.

Improve this answer
6
  • 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. Commented Nov 13, 2013 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. Commented Nov 13, 2013 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? Commented Nov 13, 2013 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). Commented Nov 13, 2013 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. Commented Nov 13, 2013 at 17:04

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.