Database Monitoring

[Pawan Komaram]

hi,

I want to write a simple java program which can monitor any database (DB2, Oracle, SQL server...etc). This program should remotely collect database counters like no.of remote connections,no.of direct reads, no.of direct writes, no.of commit statements, no.of dead locks....etc.
How can I do this, without using any tool?

Thanks in Advance:)

[Sandeep Sanaboyina]

These parameters can be accessed from tables in the database. For examplpe, in Oracle, they will be available in V$SESSION etc. tables.
But these tables might vary from DB to DB. I am not so sure about the other databases though.

Moreover, you will need dba privileges to access these tables. Building a single application for different DB's may be tough.

[Paul Sturrock]

It's not going to be a simple tool I'm afraid. The logic is fairly simple, but the work needed for all databases is a lot. You'll need to connect to the various monitoring procedures, tables and views specific to each database (e.g. v$session on Oracle, sp_who2 on SQL Server etc.) and interpret their results.

[Sandeep Sanaboyina]

I think the hardest part will be to get your hands on different types of databases . Would be great though to have such a tool .

[Pawan Komaram]

Thank you all for your replies..........

I want to make this tool as simple as possible. My code should work for any database, but one at a time.I will configure the database type externally, based on this configuration it should invoke the appropriate code. I mean if I configure this tool for a client who is using DB2 as back end then I will set the database type as DB2.
The another interesting requirement is I don't want to use database drivers to connect to databases. Because I don't want to bundle drivers of all databases along with my tool. Instead I want to get the reference of data source object which resides at application server using JMX and then I will execute appropriate commands on it.

Now the problem I am facing right now is, I could not connect to the JMX agent from a remote machine even though it is enabled for the remote access. I put the following entry in the run.conf.bat file to enable the JMX agent remote access.

set JAVA_OPTS=%JAVA_OPTS% "-Dcom.sun.management.jmxremote.port=1234"
set JAVA_OPTS=%JAVA_OPTS% "-Dcom.sun.management.jmxremote.authenticate=false"
set JAVA_OPTS=%JAVA_OPTS% "-Dcom.sun.management.jmxremote.ssl=false" 

my server is running as a windows service using wrapper.jar.

[David O'Meara]

you need to add spaces at the end, inside the quotes otherwise you mangle the settings.

set JAVA_OPTS=%JAVA_OPTS% "-Dcom.sun.management.jmxremote.port=1234 "  
set JAVA_OPTS=%JAVA_OPTS% "-Dcom.sun.management.jmxremote.authenticate=false "  
set JAVA_OPTS=%JAVA_OPTS% "-Dcom.sun.management.jmxremote.ssl=false "   

[Pawan Komaram]

I have got it worked by just setting the custom port number in the jboss-service.xml of jmx-remoting.sar of deployment folder. after this setting I have used the following URL to connect to JMX agent of JBoss.

  service:jmx:rmi://<remote-host-ip>/jndi/rmi://<remote-host-ip>:1090/jmxconnector

So I haven't put any JAVA_OPTS in the run scripts. Does anyone know the difference between these two settings?