8 Replies Latest reply on Sep 18, 2014 8:34 PM by gcp

    Custom Alerts for DB2 per database (not instance)


      If I create a custom alert for DB2, how do I get the actual Database Name (not the Instance) returned with the results?


      A DB2 Instance can have multiple databases defined (especially in our Test Regions) so knowing which database the alert is for is important.


      I can create a Custom SQL Alert - Single Numeric Return with Execute Against Monitored Database and it will run, but the output does not identify which database the result is from.





      If I test this, I get this output which is not very helpful.


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

      INFO   MYSERVER:50000       2


      Yet, if you look at the Administrative Alert for Tablespace Freespace then you get the Database name shown in the Alert Value:


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




      The DPA/Ignite doco is rather sparse in this area (that's a hint, by the way ).

        • Re: 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.

          1 of 1 people found this helpful
            • Re: Re: Custom Alerts for DB2 per database (not instance)

              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:




              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

                • Re: Re: Custom Alerts for DB2 per database (not instance)

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

                    • Re: Re: Re: Custom Alerts for DB2 per database (not instance)

                      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)

                • Re: Custom Alerts for DB2 per database (not instance)

                  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!

                    • Re: Re: Custom Alerts for DB2 per database (not instance)

                      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