Database Monitoring from Confluence using DB2 [SEC=UNCLASSIFIED]

[Peter James]

Hi Vernat,

We are using JavaMelody for monitoring a reasonably large installation of Confluence.

Unfortunately the database monitoring facilities will not work because we cannot have sufficient DB privileges.
Our Confluence installation is just one of many database applications, and we will never be given sufficient access to using the database monitoring the way it currently is constructed: From our DB administrator:

Unfortunately to execute that sysproc.SNAPSHOT_DYN_SQL the user needs system-wide privileges which we can't give out.

Is it possible to configure JavaMelody in such a way that it can monitor the database connections etc, but without requiring such a high level of privileges?  

Thank you for a great tool - it has made monitoring Confluence considerably easier and more accurate.

Regards,

Peter James


********************************************************************** IMPORTANT: This e-mail is for the use of the intended recipient only and may contain information that is confidential, commercially valuable and/or subject to legal or parliamentary privilege. If you are not the intended recipient you are notified that any review, re-transmission, disclosure, dissemination or other use of, or taking of any action in reliance upon, this information is prohibited and may result in severe penalties. If you have received this e-mail in error please notify the sender immediately and delete all electronic and hard copies of this transmission together with any attachments. Please consider the environment before printing this e-mail **********************************************************************

[Vernat Emeric]

Hi,

There may be some misunderstanding on the goal of the monitoring of the sql requests.
I will try to clarify in case that may be needed.

The javamelody plugin for JIRA / Confluence / Bamboo display graphics and statistics for http requests and sql requests.
If the sql requests are not displayed for your Confluence, please read about the datasource:
http://code.google.com/p/javamelody/wiki/UserGuide#Atlassian_JIRA,_Confluence_and_Bamboo_Plugin

The statistics for sql requests does not need any privileges from your database. In fact, those statistics do not ask anything to your DB2 database.
Those statistics for http and sql requests are your main sources of information.


Besides, there are the database reports in the system informations.
The goal of those database reports is to ask the database about what it can say about past or present performance, by executing some fixed sql requests.
For example in Oracle, the times and buffer_gets are queried in the v$sqlarea and it is indeed suggested by javamelody that "select any dictionary" is granted to execute those requests.

In DB2 (your case), there are unfortunately much less database reports displayable in javamelody.
In fact, there is only one. It is the result of "select * from table(SNAPSHOT_DYN_SQL(' ',-1)) as dynsql"
Certainly, the user needs the SNAPSHOT_DYN_SQL for that. As you can see, you don't lose many reports if you don't have this privilege.

I hope that the goal of the database reports is clearer now. And I suppose that you have to accept that your DBA doesn't want that the content of SNAPSHOT_DYN_SQL could be read, for display by javamelody.


By the way, if you (or your DBA) have some important fixed sql requests about past and present performance in DB2, I would be glad to know the best of them in order to display the results in javamelody.

bye,
Emeric

[Peter James]

Hi Vernat,

Thank you for your comprehensive reply.

I have set up a separate datasource for Confluence and can successfully access it using the Confluence sql plugin, however none of the http session, jdbc or sql graphs are reported on.
Our main connection to the database is with a direct JDBC connection setup.
Does it require that we only use the datasource connection rather than the direct JDBC connection?

The datasource URL is in the form: jdbc:db2://devdb203:54000/DWIKI

The reports that our DBA have suggested (which don't require a high level of access) are:

Monitor (MON) views
The monitor views return metrics on various database activities, for example:

• MON_CURRENT_SQL returns metrics for all activities that were submitted on all members of the database and have not yet been completed, including a point-in-time view of currently executing SQL statements.
• MON_DB_SUMMARY returns metrics aggregated over all service classes.
• MON_LOCKWAITS returns information about agents working on behalf of applications that are waiting to obtain locks in the currently connected database.
• MON_SERVICE_SUBCLASS_SUMMARY returns metrics for all service subclasses, showing work executed per service class.
• MON_CURRENT_UOW returns metrics for all units of work.
• MON_WORKLOAD_SUMMARY returns metrics for all workloads, showing incoming work per workload.

They wont give a historical summary like the snapshot but they will give a good picture of how the database is travelling at a certain point in time. All of these views are in the SYSIBMADM schema.

Regards,

Peter James
DHS Wiki Administrator

[Vernat Emeric]

Hi,

Yes, if you want to monitor the jdbc connections and the sql requests in Confluence, you need to use only the datasource connection and not the direct JDBC connection.
Reference: http://code.google.com/p/javamelody/wiki/UserGuide?tm=6#Atlassian_JIRA,_Confluence_and_Bamboo_Plugin

In fact, using net.bull.javamelody.JdbcDriver in confluence.cfg.xml, as said for Bamboo, does not work in Confluence, because that class from the plugin is not found when using Confluence.
So you need to use a datasource connection.

You can read the Atlassian documentation for that:
http://confluence.atlassian.com/display/DOC/Database+Configuration
and this example for Oracle:
http://confluence.atlassian.com/display/DOC/Database+Setup+for+Oracle
(the datasource to use is written in confluence.cfg.xml by the "Production setup", if I remember well.)

I suggest to try the setup in a local test server first.


Many thanks for the monitor views for DB2. I have created an issue for them and I will try to add those reports for the next release:
http://code.google.com/p/javamelody/issues/detail?id=179

bye,
Emeric

[evernat]

Hi,

The reports for DB2 are now added in "Database reports" in the "System
information" part of the monitoring page and ready for the next
release (1.35).
But I have no DB2 database to test: you can test if you wish by
downloading some new build,

for JIRA/Confluence/Bamboo javamelody plugin at:
http://javamelody.googlecode.com/files/jira-javamelody-20120212.jar

or for javamelody at:
http://javamelody.googlecode.com/files/javamelody-20120212.jar

bye,
Emeric

On 5 fév, 19:10, Vernat Emeric <ever...@free.fr> wrote:
> Hi,
>
> Yes, if you want to monitor the jdbc connections and the sql requests in
> Confluence, you need to use only the datasource connection and not the
> direct JDBC connection.

> Reference:http://code.google.com/p/javamelody/wiki/UserGuide?tm=6#Atlassian_JIR...

>
> In fact, using net.bull.javamelody.JdbcDriver in confluence.cfg.xml, as
> said for Bamboo, does not work in Confluence, because that class from
> the plugin is not found when using Confluence.
> So you need to use a datasource connection.
>
> You can read the Atlassian documentation for that:http://confluence.atlassian.com/display/DOC/Database+Configuration
> and this example for Oracle:http://confluence.atlassian.com/display/DOC/Database+Setup+for+Oracle
> (the datasource to use is written in confluence.cfg.xml by the
> "Production setup", if I remember well.)
>
> I suggest to try the setup in a local test server first.
>
> Many thanks for the monitor views for DB2. I have created an issue for
> them and I will try to add those reports for the next release:http://code.google.com/p/javamelody/issues/detail?id=179
>
> bye,
> Emeric
>

> Le 01/02/2012 05:53, Peter James a �crit :

>
>
>
>
>
>
>
>
>
> > Hi Vernat,
>
> > Thank you for your comprehensive reply.
>
> > I have set up a separate datasource for Confluence and can
> > successfully access it using the Confluence sql plugin, however none
> > of the http session, jdbc or sql graphs are reported on.
> > Our main connection to the database is with a direct JDBC connection
> > setup.
> > Does it require that we only use the datasource connection rather than
> > the direct JDBC connection?
>
> > The datasource URL is in the form: jdbc:db2://devdb203:54000/DWIKI
>
> > The reports that our DBA have suggested (which don't require a high
> > level of access) are:
>

> > *Monitor (MON) views*

> > The monitor views return metrics on various database activities, for
> > example:
>

> >   * MON_CURRENT_SQL returns metrics for all activities that were

> >     submitted on all members of the database and have not yet been
> >     completed, including a point-in-time view of currently executing
> >     SQL statements.

> >   * MON_DB_SUMMARY returns metrics aggregated over all service classes.
> >   * MON_LOCKWAITS returns information about agents working on behalf

> >     of applications that are waiting to obtain locks in the currently
> >     connected database.

> >   * MON_SERVICE_SUBCLASS_SUMMARY returns metrics for all service

> >     subclasses, showing work executed per service class.

> >   * MON_CURRENT_UOW returns metrics for all units of work.
> >   * MON_WORKLOAD_SUMMARY returns metrics for all workloads, showing