Enabling DataSource Spy Logging in JBoss EAP 6/7

Solution Verified - Updated

Environment

  • Red Hat JBoss Application Platform (EAP)
    • 6
    • 7

Issue

  • Is it possible to log the executed statements for a managed DataSource?
  • Is there a way to enable spy logging at the DataSource level in EAP6 in order to debug the DataSoruce statistics/queries ?
  • How to verify information related to connections and statements in a datasource?
  • is it possible to monitor the statements send to the database
  • How to log the SQL statements if a datasource connection is used

Resolution

JBoss EAP 6 and 7 provides a very efficient way of enabling the spy logging which can log all the information related to the connections/statements and any method which is invoked on the JDBC Objects.

Add the category jboss.jdbc.spy in the logging subsystem related to your configuration file standalone*.xml or if you run in domain mode to the related profile of the domain.xml.

Add the spy flag to the datasource

The datasource contain an attribute to enable the jdbc logging. The attribute spy is set to false by default.

<datasource jndi-name="java:boss/datasources/MySQLDS" pool-name="MySQLDS_Pool" enabled="true" spy="true">

Note: It is possible to use separate datasource configurations, so it is important to check if your server contains such separate files for datasource configuration in development, then you will need to add this parameter on that separated file.

Enable the logging

<logger category="jboss.jdbc.spy">
   <level name="TRACE"/>
</logger>

Use CLI to enable monitoring

To enable the datasource spy flag and add the logger the following CLI commands can be used.

/subsystem=logging/logger=jboss.jdbc.spy:add(level=TRACE)
# optional to enable log output in the console
/subsystem=logging/console-handler=CONSOLE:write-attribute(name=level, value=TRACE)

/subsystem=datasources/data-source=MySQLDS_Pool:write-attribute(name=spy, value=true)

Remember that the commands must use the profile=ProfileName prefix if the domain mode is used.

  • You need to restart the server to enable the spy logging. Remember that the DEBUG logging will not shown at the CONSOLE by default.

output of spy logging

Spy logging will provide the information regarding the Opening and Closing of the connection is happening properly or not including the Queries which are getting executed:

DEBUG [jboss.jdbc.spy] (http-localhost-127.0.0.1-8080-1) java:jboss/datasources/MySQLDS [Connection] createStatement()
DEBUG [jboss.jdbc.spy] (http-localhost-127.0.0.1-8080-1) java:jboss/datasources/MySQLDS [Statement] executeQuery(SELECT * FROM emp)
DEBUG [jboss.jdbc.spy] (http-localhost-127.0.0.1-8080-1) java:jboss/datasources/MySQLDS [ResultSet] next()
DEBUG [jboss.jdbc.spy] (http-localhost-127.0.0.1-8080-1) java:jboss/datasources/MySQLDS [ResultSet] getString(1)
DEBUG [jboss.jdbc.spy] (http-localhost-127.0.0.1-8080-1) java:jboss/datasources/MySQLDS [ResultSet] getString(2)
DEBUG [jboss.jdbc.spy] (http-localhost-127.0.0.1-8080-1) java:jboss/datasources/MySQLDS [ResultSet] next()
DEBUG [jboss.jdbc.spy] (http-localhost-127.0.0.1-8080-1) java:jboss/datasources/MySQLDS [Connection] close()

If there is only a need to track connection leaks or the pool usage the following links are relevant:
Enable logging to monitor the usage of the DataSource connection pool
How to enable cached connection manager on JBoss EAP 6

SBR
Product(s)
Components
Category
Tags

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.