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

    Custom Alerts for DB2 per database (not instance)

    gcp

      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.

       

      e.g.

      SELECT COUNT(*) FROM SYSIBMADM.MON_LOCKWAITS

       

      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"

      INFO    MYSERVER:50000     MYDB.SYSTOOLSPACE 97 %

      INFO    MYSERVER:50000     YOURDB.SYSTOOLSPACE 97 %

       

      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)
          mandevil

          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)
              gcp

              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

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

                  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)
                      gcp

                      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)
                  roballen

                  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)
                      gcp

                      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