Cleaning up the database for Spotfire Statistics Services

2017-01-10 04:58:14

By default TIBCO Spotfire Statistics Service utilises an internal H2 database engine. This is a Java SQL database which has the advantage of being extremely fast. On some occasions the list of jobs recorded in the database may fall out of sync with the actual job data located in the filesystem. 

This could manifest itself as errors such as below, which will be seen in the SPlusSever.log



SEVERE: Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'com.insightful.splusserver.SplusStateInitializer#0' defined in class path resource [applicationContext-job-execution.xml]: Invocation of init method failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Sequence "SYSTEM_SEQUENCE_28CD03AB_87F9_4985_991F_0A42C801CE58" not found; SQL statement:



The way to solve this is to start afresh with a clean database and filesystem data.

1) Stop the TSSS service

2) The H2 database can safely be deleted. Upon startup the server will create a new database with the correct schema if an existing one is not found.

Delete all of the files in the (Installation_Root)\h2 directory

Next transient data on the filesystem must be cleared:

3) Delete all of the directories in the (Installation_Root)\data\sessions directory
4) Delete all of the directories in the (Installation_Root)\data\results directory
5) Start the TSSS Service again.

You should find a fresh copy of the H2 database and all job data will start from scratch.