How to enable additional logging of operations against the Spotfire server database.

Product:TIBCO Spotfire Server
Versions:10.10 and higher

Summary:
This article describes how to enable additional logging for operations against the Spotfire server database.

Details:
In order to better understand and troubleshoot operations done against the Spotfire server database, including timing information, additional logging of database operations can be enabled as described below.

Resolution:

• Locate the bootstrap.xml file under <Spotfire Server>\tomcat\webapps\spotfire\WEB-INF
• Open bootstrap.xml file in a text editor like notepad++
• Add a new connection property - spotfire.db.logging - in bootstrap.xml , Note that it's added in two places as shown below

<bootstrap>
<server>
<driver-class>XXXXXX</driver-class>
<database-url>XXXXXX</database-url>
<username>XXXXXX</username>
<password>XXXXXX</password>
<connection-properties>
<connection-property>
<key>spotfire.db.logging</key>
<value>true</value>
</connection-property>
</connection-properties>
</server>
<config-tool>
<driver-class>XXXXXX</driver-class>
<database-url>XXXXXX</database-url>
<username>XXXXXX</username>
<password>XXXXXX</password>
<connection-properties>
<connection-property>
<key>spotfire.db.logging</key>
<value>true</value>
</connection-property>
</connection-properties>
</config-tool>
<server-alias>XXXXXX</server-alias>
<server-id>XXXXXX</server-id>
</bootstrap>

• Add the below lines where it "fits" in <Spotfire Server install dir>\tomcat\spotfire-config\log4j2.xml file, in the Appenders and Loggers sections:

<Appenders>
<RollingFile name="sqltiming" fileName="${basedir}/db_timing.log"
filePattern="${basedir}/db_timing.log.%i">
<PatternLayout pattern="-----&gt; %d{yyyy-MM-dd HH:mm:ss.SSS}
%m%n%n"/>
<Policies>
<SizeBasedTriggeringPolicy size="10000KB"/>
</Policies>
<DefaultRolloverStrategy max="9"/>
</RollingFile>
<RollingFile name="connection" fileName="${basedir}/db_conn.log"
filePattern="${basedir}/db_conn.log.%i">
<PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} %m%n"/>
<Policies>
<SizeBasedTriggeringPolicy size="10000KB"/>
</Policies>
<DefaultRolloverStrategy max="9"/>
</RollingFile>
<RollingFile name="jdbc" fileName="${basedir}/db_jdbc.log"
filePattern="${basedir}/db_jdbc.log.%i">
<PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} %m%n"/>
<Policies>
<SizeBasedTriggeringPolicy size="10000KB"/>
</Policies>
<DefaultRolloverStrategy max="9"/>
</RollingFile>
<RollingFile name="sql" fileName="${basedir}/db_sql.log"
filePattern="${basedir}/db_sql.log.%i">
<PatternLayout pattern="-----&gt; %d{yyyy-MM-dd HH:mm:ss.SSS}
%m%n%n"/>
<Policies>
<SizeBasedTriggeringPolicy size="10000KB"/>
</Policies>
<DefaultRolloverStrategy max="9"/>
</RollingFile>
</Appenders>

<Loggers>
<Logger name="jdbc.sqlonly" level="FATAL">
<AppenderRef ref="sql"/>
</Logger>
<Logger name="jdbc.audit" level="FATAL">
<AppenderRef ref="jdbc"/>
</Logger>
<Logger name="jdbc.resultset" level="FATAL">
<AppenderRef ref="jdbc"/>
</Logger>
<Logger name="jdbc.connection" level="FATAL">
<AppenderRef ref="connection"/>
</Logger>
<Logger name="jdbc.sqltiming" level="DEBUG">
<AppenderRef ref="sqltiming"/>
</Logger>
</Loggers>

• Restart the TIBCO Spotfire Server service to apply.

Result:
In <Spotfire Server install dir>\tomcat\logs, you should now see a new kind of logs being produced, prefixed with db_*, such as db_timing.log.

The following is an example entry from the db_timing.log, which provides timing information:

-----> 2023-03-15 09:35:50.057  com.spotfire.server.util.sql.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:85)
7. { call usp_deleteItem ('6a1062b8-d54a-4441-aa8c-87604e562a37', 'bf0e8806-f50e-4f1e-8558-d73a308cb659',
0, 1, 1) }
 {executed in 15 msec}
 

Note:

• Take a backup of the above files before editing
• These new logs files will be included when you create Troubleshooting bundles.