Icon

Comments have been closed on this page. Please use AppMon & UEM Plugins forum for questions about this plugin.

Overview


The DB2 plugin enables monitoring behavior metrics provided in a DB2 database.The plugin uses JDBC to connect to the DB2 Database and queries key performance  metrics. Having these measures in dynaTrace enables quick correlation of database related performance issues such as high I/O or too many database connections to application transaction performance problems such as long running transactions or slow database queries.

Since this plugin is currently in BETA, please follow the best practice of having a separate collector for your monitoring plugins.

Plugin Details

Icon

This plugin only works with DB2 LUW. It does not work with DB2 z/OS.

Plug-In Files


DB2 Monitor Plugin 0.8.0

DB2 Monitor Plugin 0.8.1

Author

Eric Burns (Dynatrace) version 0.8.0

Ingo Hackl (Dynatrace) version 0.8.1

dynaTrace Versions

>= 5.5

License

dynaTrace BSD

Support

Not Supported

Known Problems

 

Release History

2012-07-12 0.8.0 Initial BETA Release

Provided Measures

The following image shows the metrics that the monitor provides:

Configuration DB2 Monitor

The monitor requires the following configuration settings:

  • hostName: Host name of the DB2 Database Instance
  • dbName: Database Instance Name (SID) or Service name
  • dbUsername: Username that is used to access the database. User may needs to have query rights to a specific tables
  • dbPassword: Password that is used to access the database
  • dbPort: DB2 Database Port for JDBC Connections (default: 50001)

Installation

Import the Plugin into the dynaTrace Server via the dynaTrace Server Settings menu -> Plugins -> Install Plugin. For details how to do this please refer to the dynaTrace documentation.

To use the provided dashboard please leave the default name of the Monitor as "RepositoryDB", then open the Dashboard and set the Data Source accordingly.

Access Requirements

This plugin accesses the following tables, so it must be able to connect and have SELECT privileges:

  • sysibmadm.applications
  • sysibmadm.snapdb

Usage Notes

This release is very much in Beta. Please send feedback and help to improve it.

Metric Groups will be changed/altered in a later release. Would like input from DB2 DBAs.

Contribution

Feel free to contribute any changes on Github

  1. Anonymous (login to see details)

    I can't find where to put the hostName, the plugin properties dialog only shows dbName, dbUsername, dbPassword, and dbPort. Where is the hostName set?

    1. Anonymous (login to see details)

      When you go into your Profile to use it (via the Monitor section) you'll be able to specify a group of hosts.

      Each system in that group will be checked for that dbName using the supplied dbUsername, dbPassword (and of course the correct port reachable) . . .

      Please let me know if this is not enough and I'll add more details/screenshots.
      -Eric

  2. Anonymous (login to see details)

    Hi,

    How do i configure DB2 monitor to poll 4 db2 instances on the same server to poll simultaneously without configure a monitor for each instances...

    example.: see the attached screenshot.
    dbname : TestDB1,Testdb2,Testdb3 etc.

    Regards,
    meraj.

    1. Anonymous (login to see details)

      Hi Meraj, I'd have to give that one some thought. The issue is that monitors basically return numerical data for different items. When you go from 1 database on a system to 4 it makes for a completely different data structure that you'd need to return and somehow process. I'll chat with another dynaTrace person and see if we can figure out a way to extend this monitor to work like that.

      1. Anonymous (login to see details)

        Hi Eric,

        I am trying to use the plugin to monitor two DB2 instances, are there any updates on how this can be accomplished?

         

        Thanks,

        Alex.

  3. Anonymous (login to see details)

    Hi

    Is this only supported on DB2 LUW - I can't find any doc on these views in Z/OS

    Hermod

    1. Anonymous (login to see details)

      Hi Hermod, technically the DB2 monitor plugin is not supported. But I'm happy to get more details from you regarding DB2 on Z/OS. The monitor basically connects to the database using JDBC and does a few queries.

      I can't imagine that DB2 on Z/OS would not accept the connection. The big question is if the queries are the same. (I would expect them to be, but we all know how complex DBA work can be.)

      Can you e-mail me at eric.burns@compuware.com? I can send you some more details - then we can update this page when we know more.
      -Eric

      1. Anonymous (login to see details)

        Are these tables available on DB2 for Z/OS? My DBA doesn't see them.

        sysibmadm.applications
        sysibmadm.snapdb

      2. Anonymous (login to see details)

        They do not exist on Z/OS. SO the plugin can not be used for that connection

        Hermod

  4. Anonymous (login to see details)

    Is something similar available for DB2 on z/OS?

    1. Anonymous (login to see details)

      If someone can provide some relevant queries that work for z/OS it wouldn't be hard to modify the existing plugin and create a z/OS version.  Anything that is gleaned via a query and returns numeric data would work - or even data that can be converted to 3 numbers to represent Green, Yellow, Red, etc.

      1. Anonymous (login to see details)

        The challenge I see is that the tables queried in this plugin do not exist in DB2 on z/OS.
        • sysibmadm.applications
        • sysibmadm.snapdb

        What would the relevant table(s) be on DB2 on z/OS?

         

  5. Anonymous (login to see details)

    What am I missing? I added the monitor and that seems to be working fine (Last polling execution terminated successfully is shown for the last runs). 


    Now I took the dashboard and changed the source to the server and the system profile under which the monitor is configured. But looks like the measures for the dashboard are not available.

     

     

     

    1. Anonymous (login to see details)

      Did you call the Monitor definition RepositoryDB as explained in the description of this plugin?

    2. Anonymous (login to see details)

      The plugin queries some specific tables, and the login has to be explicitly granted access to those tables.

      Let me dive into the source and get some details.

  6. Anonymous (login to see details)

    Monitor is renamed as 'RepositoryDB'.

  7. Anonymous (login to see details)

    Removed and added the measures back & seems fine.

  8. Anonymous (login to see details)

    The DB@ LUW worked and then I created the same for another DB2 on AIX. There all but sync read and async write is being reported. Any idea why? If I look at the monitor response, I see a NULL response for both the read and write measure. 

  9. Anonymous (login to see details)

    Just to be clear the dashboard for the DB on Linux host is working fine but the one I created for the AIX host is not showing read and write measrues.

     

    Also DBA's want to know what SQLs are being used by the monitor in the background so that they can suggest any improvements etc. 

  10. Anonymous (login to see details)

    I ran - select 100 - (((pool_async_data_reads + pool_async_index_reads) * 100 ) / (pool_data_p_reads + pool_index_p_reads + 1)) as SRP from sysibmadm.snapdb with ur - against this DB and it is returning me value... I got 77 for this case... So... not sure what is wrong yet.

  11. Anonymous (login to see details)

    It is very possible that Linux versus AIX has different tables.  I'll add these up above once I can edit - but here are some more details.

    The plugin is based on details from these two pages:

    http://www.dbisoftware.com/blog/db2_performance.php?id=96

    http://www.dbisoftware.com/blog/db2_performance.php?id=117

    If you open up the Jar file there is a source file with the queries.  Let me know if you'd like them added here.

     

  12. Anonymous (login to see details)

    Eric - Looks like we are using the same tables. Still not sure why it is not working on AIX. I was also reading the posts on dbisoftware and ran the same queries. I tried the queries using a DB2 Client and both(Linux & AIX)  return data for the same queries...  

     

    select 100 - (((pool_async_data_reads + pool_async_index_reads) * 100 ) / (pool_data_p_reads + pool_index_p_reads + 1)) as SRP from sysibmadm.snapdb


     

    1. Anonymous (login to see details)

      I am wondering if there is something wrong with how I've coded the connection or the query.  I would think they would work consistently across platforms.

      I think the next step would be to turn up logging and see what comes in at a FINE level for the plugin.

  13. Anonymous (login to see details)

    How can I turn up the logging? I can send you from both the DBs.

    1. Anonymous (login to see details)

      You should be able to set it in the properties for the plugin - a drop-down at the bottom that says "Log Level"

      Note that the Collector has to be restarted when this is changed in order for it to register.

  14. Anonymous (login to see details)

    Got it. I will have to create a new collector for this or will do later as cannot restart now. 

     

    Jibi 

  15. Anonymous (login to see details)

    Where can I find the logs so that I can send to you once I make the change? 

    1. Anonymous (login to see details)

      One piece of documentation says:

      Log Messages

      Each plugin sends log messages either to <dtinstallation>/log/server or to Collector log directories, depending on the execution target of the Plugin. The log file name starts with the ID of the Task, Monitor, or Action..

       

      If this doesn't help let me know and I'll confirm where to look.

  16. Anonymous (login to see details)

    I think I see them under the collector log directories.

     

    -rw-rw-r-- 1 dynatrace dynatrace 2205 Oct 14 12:49 CollectorLauncher_20141014-125054.log
    -rw-rw-r-- 1 dynatrace dynatrace 1365 Oct 15 11:16 CollectorLauncher_20141015-111642.log
    -rw-rw-r-- 1 dynatrace dynatrace 1367 Oct 27 10:26 CollectorLauncher_20141027-102715.log
    -rw-rw-r-- 1 dynatrace dynatrace 1365 Nov 12 19:17 CollectorLauncher_20141112-191802.log
    -rw-rw-r-- 1 dynatrace dynatrace 1199 Nov 12 19:18 CollectorLauncher.log
    -rw-rw-r-- 1 dynatrace dynatrace 165845 Nov 19 16:58 com.dynatrace.diagnostics.plugins.DB2Plugin.monitor.0.0.log
    -rw-rw-r-- 1 dynatrace dynatrace 0 Nov 19 13:09 com.dynatrace.diagnostics.plugins.DB2Plugin.monitor.0.0.log.lck

     

    Thanks Eric. 

  17. Anonymous (login to see details)

    Eric - I have sent you the logs. 

  18. Anonymous (login to see details)

    Eric - Also how do you calculate the connection time? Also what is the definition for that and in future release add that as the description for measure.  

  19. Anonymous (login to see details)

    I could not figureout from code how the connection time is calculated.

  20. Anonymous (login to see details)

    Hi All ,

    We are planning to set DB2 monitor on dynatrace client with dynatrace user having read permission on

    • sysibmadm.applications
    • sysibmadm.snapdb

     None of the measurement other than connection time is available on dashboard and log says  :

    2015-05-11 15:42:55 WARNING [DB2Plugin@DB2 Monitor_0] db read failed.: DB2 SQL Error: SQLCODE=-1092, SQLSTATE=     , SQLERRMC=DYNTRUSR, DRIVER=4.3.85

    2015-05-11 15:42:55 INFO [DB2Plugin@DB2 Monitor_0] Doing SRP - query string is ... select 100 - (((pool_async_data_reads + pool_async_index_reads) * 100 )  / (pool_data_p_reads + pool_index_p_reads + 1)) as SRP  from sysibmadm.snapdb where DB_NAME = 'wcq01'

     

    Can anyone suggest the access functions for the dynatrace users , which will allow other measurements to be captured ?

     

    Thanks

    Shailesh Verma

     

     

     

  21. Anonymous (login to see details)

    Hello All,

    What are the exact queries that this monitor uses? I am having a difficult time decompiling the jar file.

     

    Thanks,

    Eric

    1. Anonymous (login to see details)

      Hi Eric,

      Did you ever find out what queries this plugin is using? The tables outlined in this documentation do not exist in my customer's database.

      Thanks,

      Matt

  22. Anonymous (login to see details)

    Who can I contact for support of this plugin?

    I am running into the same issue as Shailesh Verma.

    Able to connect to DB but no data is polled.

    DB2 SQL Error: SQLCODE=-1092

    1. Anonymous (login to see details)

      When I google the error code it says "doesnt have the authority to perform this request". could it be that the username you specified doesn't have the required privileges to execute SELECT on the two tables listed in the plugin description?

      1. Anonymous (login to see details)

        Hey Andi!

        I worked with our DBA to make sure that this user has select privileges to that DB instance. We also tested by running a couple generic select statements on the DB2 command line under that user. 

         

        Thanks,

        Alex

        1. Anonymous (login to see details)

          Just to confirm: that means that the plugin is now working for you? or are you still running into the same issue?

          1. Anonymous (login to see details)

            Hi Andy, 

            I work with Alex Soto, we gave up our efforts and never got back to it. 

            We would love to use this plugin but we cannot figure it out. 

            Do you know anyone with previous experience that can help? 

  23. Anonymous (login to see details)

    Did anyone get this plugin to work for DB2 z/OS?

    Thanks, Tom

  24. Anonymous (login to see details)

    Dear,
    You could support me to set correctly the plugin, since I can not connect to the database.

    1. Anonymous (login to see details)

      Hi Richard,

      you have to configure the database you want to connect to in the plugin properties:

      Ingo

       

      1. Anonymous (login to see details)

        Thank you,

        but you could help me, I have given this information and would like to know if it is enough to install the plugin, do not understand where "RepositoryDB" is placed.

        I hope your help, Greetings

         

        the access to DB2 Maqui18 and NIAPPTES were created:

        USR_MON has created the account in NIAPPTES and MAQUI18 servers.

        Being a password for connection is fixed and never expires.

        The password to use on both servers is: f$01mon2

         

        1. Anonymous (login to see details)

          Hi Richard,

          do the following steps

          1) Edit System Profile

          2) select "Monitors"

          3) Create a new monitor "DB2 monitor". You have to provide the host where your database is running here.

          The end result should look like the following:

          Hope that helps.

          Ingo

           

           

          1. Anonymous (login to see details)

            Hello
            I do settings as you indicated, but an error is displayed.
            please help 

             


            What validations would have to take to resolve the problem?

            Could you give me your email?

             

            Thank you

  25. Anonymous (login to see details)

    Hi all,

    I made this Plugin running on DB2 LUW without problems. I only had to fix the "DB2 Overview dashboard". See new version 0.8.1

    These are the db-statements used for the different measures:

    Wait Locks: SELECT lock_waits FROM sysibmadm.snapdb

    Dead Locks: SELECT deadlocks FROM sysibmadm.snapdb

    Synchronous Read Percentage: select 100 - (((pool_async_data_reads + pool_async_index_reads) * 100 )

                            / (pool_data_p_reads + pool_index_p_reads + 1)) as SRP from sysibmadm.snapdb where DB_NAME = '

    Total Users: SELECT COUNT(*) FROM sysibmadm.applications

    Index Usage: SELECT rows_read, (rows_selected + rows_inserted + rows_updated + rows_deleted) FROM sysibmadm.snapdb

    Asynchronous Write Percentage: SELECT (((pool_async_data_writes + pool_async_index_writes) * 100 ) /

                        (pool_data_writes + pool_index_writes + 1))

                        AS AWP FROM sysibmadm.snapdb WHERE db_name = " + dbService

    So make sure that the used db user is allowed to execute these statements.

    Ingo

     

  26. Anonymous (login to see details)

    Hi all,

    I need to monitor the %free space in the DB2 tablespace.

    Any idea?

    Thank You,

    Tiago Carrega

     

    1. Anonymous (login to see details)

      I believe that the next Dynatrace AppMon version - May 2017 - will help you as it introduces the DB2 Database Agent - here is some information in the doc: https://www.dynatrace.com/support/doc/appmon/database-monitoring/database-health-monitoring/#db2

      1. Anonymous (login to see details)

        Thank You Andreas, but the DB2 plugin does not have a counter to monitor the % free space of the tablespace. I believe that it is necessary to set up a plugin to perform this monitoring.

        Regards,

        Tiago

        1. Anonymous (login to see details)

          Hi Tiago,

          when you click on the live view you will be able to check Tablespace,deadlocks,slow running queries etc.

           

          Thanks,

          Tarun

          1. Anonymous (login to see details)

            Thank You.