How to trace SQL requests for H2 in-memory databases?

Question

How can I trace SQL requests for H2 in-memory embedded databases?

The documentation says the trace file is located in the same directory as the database file, but for in-memory embedded database I have no database directory (under Windows).

I tried to redirect traces to a given file with slf4j/logback by using

TRACE_LEVEL_FIle=4;TRACE_LEVEL_SYSTEM_OUT=3

in the database URL and the following logback.xml config file but with no luck:

<configuration scan="true">
  <appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
    <file>mylogfile.log</file>
    <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
      <fileNamePattern>%d{yyyyMMdd}_mylogfile.log</fileNamePattern>
    </rollingPolicy>
    <encoder>
      <pattern>%date{yyyyMMdd HH:mm:ss} %contextName[%thread] %level %logger{0} - %msg%n</pattern>
    </encoder>
  </appender>

  <logger name="com.myapp" level="WARN">
    <appender-ref ref="FILE" />
  </logger>

  <logger name="h2database" level="TRACE">
    <appender-ref ref="FILE" />
  </logger> -->

  <root level="WARN">
    <appender-ref ref="FILE" />
  </root>
</configuration>

My logback config file works correctly for other logs.

Any idea?

Answer 1

I'm not completely sure, but according to my test it seems if you remove TRACE_LEVEL_SYSTEM_OUT=3 then it would work. Could you try this? Example database URL:

jdbc:h2:mem:test;TRACE_LEVEL_FILE=4

instead of

jdbc:h2:mem:test;TRACE_LEVEL_FILE=4;TRACE_LEVEL_SYSTEM_OUT=3

http://www.h2database.com/html/features.html#trace_options