How to log executed SQL statements?

[tsteinmaurer]

Hello,

I'm running Tomcat 6.0.16 with a Mondrian 3.0.0.10550 web app. The web app is basically based on the Foodmart webapp sample, so just a few JPivot queries, although against a Firebird 2 database. This works quite well, but for performance reasons, I want to see the executed SQL statements against the Firebird database.

I've tried various things, e.g. placing the mondrian.properties in different locations, ... but without success.

What do I need to enable SQL tracing/logging with the combination (Tomcat 6, Mondrian 3) mentioned above?

Thanks,
Thomas

[jhyde]

Tracing config has changed since 2.4, and in particular the mondrian.trace.level property is obsolete. To print SQL statements, you now need to configure log4j.

See the configuration guide http://mondrian.pentaho.org/document...on.php#Logging for more information.

Julian

[tsteinmaurer]

Hello Julian,

I'm aware of the configuration guide, but I'm sorry, I can't figure it out, because there is no log4j.xml file in Tomcat 6. I'm also not sure how the log4j categories "mondrian.mdx" and "mondrian.sql" should be used properly.

Any examples?

Thanks for any further assistance!!!

Regards,
Thomas

[tsteinmaurer]

Any help is much appreciated.

Thanks,
Thomas

[rickokin]

I added the following to my catalina.bat file (for windows):
set JAVA_OPTS=-Xms256m -Xmx512m -Dlog4j.configuration=log4j.xml

This assumes the log4j.xml file is located in the classes directory under mondrian/WEB-INF

Here is my log4j.xml file which has been modified to have output go to a file called workbench.log in the C:\ directory:

====== START OF FILE =======
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<!-- ================================================== =================== -->
<!-- -->
<!-- Log4j Configuration -->
<!-- -->
<!-- Default log configuration file for the Workbench. -->
<!-- Copied to the user home directory, where the user can change -->
<!-- settings for their own purposes. -->
<!-- -->
<!-- Sends log messages to the console and the workbench.log file -->
<!-- in the user's home directory. -->
<!-- -->
<!-- ================================================== =================== -->

<!-- $Id: //open/mondrian/log4j.xml#2 $ -->

<!--
| For more configuration infromation and examples see the Jakarta Log4j
| owebsite: http://jakarta.apache.org/log4j
-->

<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/" debug="false">

<!-- ================================= -->
<!-- Preserve messages in a local file -->
<!-- ================================= -->

<!-- A size based file rolling appender, capturing everything -->

<appender name="FILE" class="org.apache.log4j.RollingFileAppender">
<param name="File" value="c:/workbench.log"/>
<param name="Append" value="false"/>
<param name="MaxFileSize" value="500KB"/>
<param name="MaxBackupIndex" value="1"/>

<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
</layout>
</appender>

<!-- ============================== -->
<!-- Append messages to the console -->
<!-- ============================== -->

<appender name="CONSOLE" class="org.apache.log4j.ConsoleAppender">
<param name="Target" value="System.out"/>
<param name="Threshold" value="WARN"/>

<layout class="org.apache.log4j.PatternLayout">
<!-- The default pattern: Date Priority [Category] Message\n -->
<param name="ConversionPattern" value="%d{ABSOLUTE} %-5p [%c{1}] %m%n"/>
</layout>
</appender>

<!-- ================ -->
<!-- Limit categories -->
<!-- ================ -->

<!-- Limit the org.apache category to INFO as its DEBUG is verbose -->

<category name="org.apache">
<priority value="INFO"/>
</category>

<category name="com.tonbeller">
<priority value="WARN"/>
</category>

<category name="mondrian.rolap">
<priority value="WARN"/>
</category>

<category name="mondrian.gui">
<priority value="WARN"/>
</category>

<category name="mondrian.sql">
<priority value="DEBUG"/>
</category>

<category name="mondrian.mdx">
<priority value="DEBUG"/>
</category>


<!-- ======================= -->
<!-- Setup the Root category -->
<!-- ======================= -->

<root>
<priority value="INFO"/>
<appender-ref ref="FILE"/>
</root>

</log4j:configuration>

======= END OF FILE ========

[tsteinmaurer]

Hi!

The first complete example on this topic, which works out of the box. Thanks a lot!!!


Thomas

[jashaffner]

I thought you would not have to use java option to include log4j configuration if you have log4j.xml in either <webapp>/WEB-INF/classes or tomcat/common/classes?

Good to know that Mondrian is using log4j. Was wondering how to trace down on generated sql statements. Thanks!