Custom Alerts for DB2 per database (not instance)

Have you tried a multiple numeric alert?  Not familiar with DB2 views, do you know where to get the database from?  Can you join to another view for that info from mon_lockwaits?  Something like:

select c.tbsp_name, a.count(*)

from sysibmadm.mon_lockwaits a, sysibmadm.mon_get_table b, sysibmadm.container_utilization c

where a.tabname = b.tabname

     and b.tbsp_id = c.tbsp_id

group by c.tbsp_name

Just kind of thinking out loud on this.  Again, not as familiar with DB2 admin views as other database platforms.

Thanks for the suggestion. I have tried a multiple numeric alert.

The problem is that while I can get the dbname  I only get 1 result per instance.  So DPA is connecting to a database (possibly every database) but only returning 1 result.

e.g. On a server with 6 databases in the instance (let's call them MYDB1 - MYDB6) I only get a single row returned:

SELECT CURRENT SERVER AS DBNAME, COUNT(*) FROM SYSIBMADM.MON_LOCKWAITS

Status "Database Instance" "Alert Value"            "Description"

INFO     MYSERVER:50000  MYDB1 0

What I should see is:

Status "Database Instance" "Alert Value"            "Description"

INFO     MYSERVER:50000  MYDB1 0

INFO     MYSERVER:50000  MYDB2 0

INFO     MYSERVER:50000  MYDB3 0

INFO     MYSERVER:50000  MYDB4 0

INFO     MYSERVER:50000  MYDB5 0

INFO     MYSERVER:50000  MYDB6 0

I suspect this may be a bug/feature in DPA for DB2, but I'd like to be wrong

Ah, you may actually only be attached to one database.  What version of DPA are you running and what version of DB2?

DPA 8.3 and DB2 9.7

I think I see what is happening.  When I look at the COND table,  DPA is only reporting for the database recorded in CONN_DATABASE that was used when I registered the Instance to DPA.

It won't let you register the other databases as the registration is per instance - This database instance is already registered in this Repository.

Also applies to the Resources tab - the graphs for DB Buffer Pool Hit Ratio, DB Package Cache Hit Ratio & DB Catalog Cache Hit Ratio should have multiple databases, but they don't.

Same with the Sessions tab graphs.

(just looking at the built-in Wait Time Alerts, looks like more issues around this too)

I'd call it a bug. I'll open a support ticket   (681078)

A support ticket might be the best option in this case.  Did you try looking at the options?  From the home page, for the instance in question, select the action dropdown and choose advanced options.  Make sure the support options is chosen and you'll have to scroll down to more than half way so that you see the instance specific options.  Look for one called DB2_MONITOR_SINGLE_DB.  Check to see if it's set to true.  If it is toggle it to false.

Thanks, have just checked the DB2_MONITOR_SINGLE_DB and it is set to false.

Appreciate your help - I've opened a ticket.

Unfortunately, the custom alerts aren't able to do some of the things that the built in ones do. However, I think this may get you close to what you are looking for.

First, the "Tablespace Freespace" alert query uses a snapshot view that allows us to specify the databases to query.

To build the alert query, we first query DB2 to get a list of all databases. We then iterate through the results building a query for each database using "UNION ALL" to query all databases at once.

Unfortunately, the MON_LOCKWAITS table only retrieves information from the currently connected database. From the documentation:

The MON_LOCKWAITS administrative view returns information about agents working on behalf of applications that are waiting to obtain locks in the currently connected database.

To accomplish something similar, I think you would have to query the SNAP_GET_LOCKWAIT table function, since it allows you to specify the databases to query. You then would have to write a query for each database and use a "UNION ALL" to execute them all at once.

For example:

Get the list of all databases available.

select DISTINCT DB_NAME from table(SNAP_GET_DB(CAST(NULL as VARCHAR(1)), -1)) as DB

For each DB_NAME result, build a query that looks like this (replace DATABASE_X with the names returned from the above query):

select 'DATABASE_X', count(1) from table(SNAP_GET_LOCKWAIT('DATABASE_X', -1)) as DB

UNION ALL

select 'DATABASE_X', count(1) from table(SNAP_GET_LOCKWAIT('DATABASE_X', -1)) as DB

Then, in DPA, create "Custom SQL Alert - Multiple Numeric Return" and use the query you built above.

The output will look something like this:

The problem with this solution is that it won't dynamically pick up any new databases...if a new one is added, you will need to modify the alert query.

I hope this helps!

Thanks roballen much appreciated.

I'll try that out as a workaround, that solution may be ok for a limited set of queries where you can use snapshots.

However, as a DPA user, why would I think a custom alert would operate any differently to a normal alert?

My expectation is that I provide the custom sql, and that DPA runs it against each known database.  I think that is what most people would expect from a monitoring tool.

At the moment, custom alerts are severely limited in function, and the documentation doesn't even hint that you don't get a choice of database.

Custom Alerts

Custom Alerts are user-specified queries that are run against the monitored database or the Database Performance Analyzer Repository. The query returns a

number (or set of numbers) that may trigger an alert depending on user-defined threshold settings.

For example, you could enter a query to detect the number of cancelled orders in the last ten minutes.

select count(*) from orders where status=’CANCELLED’ and date > sysdate – 10/1440;

How can you run that custom SQL in the example above if you do not know which database it will connect to? 

At the very least, I would expect an option to select which database(s) to run it against.

Sorry for the rant - my new toy is borked, and I want it fixed