Looking for a JavaEE Architect or Potential Tech Co-Founder?
Please don't hesitate to contact me.

How to monitor the data source connections and sql queries in jboss

To allow us to debug our jboss server for possible connection leaks, wrong sql queries normally we do 2 changes:

1.) In persistence.xml, set hibernate.show_sql to true

2.) In jboss's standalone.xml, we set the hibernate logger to DEBUG
<logger category="org.hibernate">
 <level name="DEBUG"/>
</logger>

But there's another not so popular way I guess, enabling spy setting in jboss's datasource.
1.) In your datasource setting add spy="true", example:
<datasource jta="true" jndi-name="java:jboss/datasources/czetsuyaDS" pool-name="czetsuyaPool" enabled="true" use-java-context="true" spy="true" use-ccm="true">

2.) Add jboss.jdbc.spy logger in the logging section of jboss
<logger category="jboss.jdbc.spy">
 <level name="TRACE"/>
</logger>

After that restart jboss and you should see logs similar to:
19:43:40,006 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] getMetaData()
19:43:40,041 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] getCatalog()
19:43:40,041 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] getMetaData()
19:43:40,041 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] createClob()
19:43:40,042 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] close()
19:43:41,256 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] getAutoCommit()
19:43:41,256 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] getMetaData()
19:43:41,256 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] createStatement()
19:43:41,257 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Statement] executeQuery(select relname from pg_class where relkind='S')
19:43:41,257 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [ResultSet] next()
19:43:41,258 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [ResultSet] close()
19:43:41,258 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Statement] close()
19:43:41,258 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] createStatement()
...
executeUpdate(...)
...
How to monitor the data source connections and sql queries in jboss How to monitor the data source connections and sql queries in jboss Reviewed by Edward Legaspi on Sunday, May 11, 2014 Rating: 5

2 comments:

erick de oliveira leal said...

How to append the user of the session that is doing the query?

Unknown said...

Is there a way to limit the output to just the SQL?

Powered by Blogger.