Db2 – Best practice to query administrative views for instance level elements

db2linux

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

Best Answer

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.

Related Solutions

DB2 passwords with special characters

This is a long-standing bug in the GUI tools.

However, as long as your password does not contain a double quote (") character, then you can work around this by putting double quotes around the password to connect.

connect to sample user test using test!
--> SQL0104N

connect to sample user test using "test!"
--> connection succeeded.

This method will also work if the GUI tools prompt you for a password with a dialog box.

If you have passwords with double quotes in them, you may be out of luck. You could open a ticket with IBM DB2 support and/or just change the password.

How to Grant All Privileges to Local db2admin with DB2 9.7 on Windows

Based on the last above links in my edit to my question I found my answer. I cannot tweak the system account since our logons to our system are from an LDAP and I cannot control the groups and what not.

So I did the following:

Opened the DB2 GUI (was easiest to do this way).
Connected to the desired database as db2admin.
Added my logon to the database as a user.
GRANT all authorities to that id (my id).
Disconnect.
Connect to the desired database as my id.
GRANT all authorities to db2admin.
Disconnect.

Voila! The db2admin logon now as all authorities.

EDIT: I'm going to leave the above as it helped me learn how to do some interesting stuff in DB2. However, I have learned that the DBADM/SECADM with DATAACCESS and ACCESSCTRL authorities granted the instance owner (in my case the db2admin id) have all the authority needed to interact with the database. I could have actually just commented out those grant lines above in the script. Those were left over from a script which ran against an older version of DB2. I have also found if I need to have the instance owner DBADM after doing a restore to database A from database B, it is easiest just to set the registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITY to YES (available in Fix Pack 2 and above). Then I don't have to try to grant instance owner DBADM. It automatically is granted that to any database restored into the instance. If you are not at Fix Pack 5, you have to bounce the instance for this to take affect.

Related Question