KB-1104 How to capture SQL statements using Hibernate trace logs

In certain troubleshooting scenarios, it may be necessary to retrieve the details of database queries that are being performed by Appian. This article details how to enable Hibernate Tracing, which results in the queries made by Appian being logged to the application server logs.

To enable Hibernate Tracing:

1. Open the appian_log4j.properties file. For Appian 18.3 and later, this file can be found in <APPIAN_HOME>/deployment/web.war/WEB-INF/resources. For Appian 18.2 and earlier, this file can be found in <APPIAN_HOME>/ear/suite.ear/resources.
2. Find and uncomment the "Output SQL Statements" and "Output param values within SQL statements" sections:

   # Output SQL statements
   log4j.logger.org.hibernate.SQL=DEBUG
   # Output param values within SQL statements
   log4j.logger.org.hibernate.type=TRACE
   

3. Set "Output SQL statements" to DEBUG if it is not already set.
4. Set "Output param values within SQL statements" to TRACE if it is not already set.
5. Once Hibernate Trace logging is no longer required, return to the appian_log4j.properties file, and update the above lines to the following:

   # Output SQL statements
   log4j.logger.org.hibernate.SQL=ERROR
   # Output param values within SQL statements
   log4j.logger.org.hibernate.type=ERROR
   

6. Save the file and check the application server log to verify that the DEBUG and TRACE log entries are no longer being recorded.
7. After verifying that DEBUG and TRACE entries are no longer being logged via the above step, it is safe to comment those lines out if desired.

Some things to note after following these steps:

• No application server restart is necessary as these changes are hot-deployed.
• The DEBUG and TRACE entries should show up in the application server logs.
• The DEBUG setting mentioned above will log the queries, whereas the TRACE setting mentioned above will log the values for the queries.
• It is NOT recommended to enable Hibernate Trace logging unless requested by Appian Support to troubleshoot a specific issue because these settings may negatively affect performance.

Affected Versions

This article applies to all versions of Appian.

Last Reviewed: June 2019