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.
This plugin only works with DB2 LUW. It does not work with DB2 z/OS.
DB2 Monitor Plugin 0.8.0
DB2 Monitor Plugin 0.8.1
Eric Burns (Dynatrace) version 0.8.0
Ingo Hackl (Dynatrace) version 0.8.1
2012-07-12 0.8.0 Initial BETA Release
The following image shows the metrics that the monitor provides:
The monitor requires the following configuration settings:
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.
This plugin accesses the following tables, so it must be able to connect and have SELECT privileges:
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.
Feel free to contribute any changes on Github
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?
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.
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.
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.
I am trying to use the plugin to monitor two DB2 instances, are there any updates on how this can be accomplished?
Is this only supported on DB2 LUW - I can't find any doc on these views in Z/OS
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 firstname.lastname@example.org? I can send you some more details - then we can update this page when we know more.
Are these tables available on DB2 for Z/OS? My DBA doesn't see them.
They do not exist on Z/OS. SO the plugin can not be used for that connection
Is something similar available for DB2 on z/OS?
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.
What would the relevant table(s) be on DB2 on z/OS?
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.
Did you call the Monitor definition RepositoryDB as explained in the description of this plugin?
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.
Monitor is renamed as 'RepositoryDB'.
Removed and added the measures back & seems fine.
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.
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.
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.
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:
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.
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
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.
How can I turn up the logging? I can send you from both the DBs.
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.
Got it. I will have to create a new collector for this or will do later as cannot restart now.
Where can I find the logs so that I can send to you once I make the change?
One piece of documentation says:
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.
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
Eric - I have sent you the logs.
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.
I could not figureout from code how the connection time is calculated.
Hi All ,
We are planning to set DB2 monitor on dynatrace client with dynatrace user having read permission on
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 ?
What are the exact queries that this monitor uses? I am having a difficult time decompiling the jar file.
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.
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
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?
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.
Just to confirm: that means that the plugin is now working for you? or are you still running into the same issue?
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?
Did anyone get this plugin to work for DB2 z/OS?
Dear,You could support me to set correctly the plugin, since I can not connect to the database.
you have to configure the database you want to connect to in the plugin properties:
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
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.
HelloI 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?
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.
I need to monitor the %free space in the DB2 tablespace.
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
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.
when you click on the live view you will be able to check Tablespace,deadlocks,slow running queries etc.