Changing the default join database for Information Services in Spotfire

FAQ - Deprecation of Spotfire database as default join database

When you join data tables from different data sources in Information Services, Spotfire needs a database for creating temporary tables and joining the final result.

The ability to use the Spotfire database as the default join database is deprecated since version 11.3. In Spotfire versions 11.7 and later, it is not possible to use the Spotfire database for this purpose. This does not mean that you can not use the Database Management System (DBMS) instance where the Spotfire database resides. You could still set up a different database on the same DBMS instance, and use that as the default join database. 

If you use Information Services for data access, and the default join database is your Spotfire database, you need to update some settings before the functionality is removed. Then you have to make sure there is a database available that Spotfire can use for joins. In this article, you will find information to help you to do the necessary changes. 

How do I know if I need to make changes?

If you have not configured a custom default join database, the Spotfire database is set as the default join database. The Spotfire database is then used for performing joins, if no other writable data source is available. To see what database is your default join database, use the command show-join-database. See Executing commands on the command line.

If you have information links that depend on using the Spotfire database as the default join database, you have to update your settings. In Spotfire versions 11.3 and later, you will see a message in your server log when an information link is run that uses the Spotfire database for joins. It looks like this:

The possibility to use the Spotfire database as the join database is deprecated since version 11.3, and it has been removed in Spotfire versions 11.7 and later.

In earlier versions of Spotfire, you can see the following line in the server debug log when an information link uses the Spotfire database for performing a join:

Using the server database as default join database.

[...]

Use the default join data source:

If you don't use joins between different data sources, you don't need to make any changes.

Making sure there is a database to use for joins

To be able to join data from different data sources in Information Services, you have to make sure there's at least one database that Spotfire can use to perform the join. Spotfire can use the data sources in the join, other data sources in the library, or the default join database. 

Making data sources in Information Services possible to use as the join db

Spotfire can use Information Services data sources in your library as the join database. 

The following are requirements for being able to use a data source as the join database:

• The setting Allow writing in temporary tables must be enabled for the data source. You can change this in Information Designer.
• The database user account that you use to log in to the data source has to have permission to write to the data source.

Configuring a custom default join database

The default join database is a setting with which you can specify a database that Spotfire can use for joins in Information Services. 

To set or configure a default join database, use the Spotfire Server configuration tool. You can also use the command create-join-db. To learn more, see

• Default join database
• create-join-db

Why configure a default join database? 

• Spotfire can use the default join database if there are no writable data sources available. 
• It is good to set up a default join database, even if you have writable data sources in the library. Spotfire can use it as a fallback, preventing joins from failing.
• You can control where joins are performed, for example, if you want to use a specific database or database user. To prioritize using the default join data source for joins, set  information-services.join-data-source-selection-rule to DEFAULT_JOIN_DATA_SOURCE. See Manually editing the server configuration in the configuration tool.

Frequently asked questions

What happens if I don't configure the default join database?

In versions 11.3 and earlier, the Spotfire database is pre-configured as the default join database. So, if you don't configure your own join database, the Spotfire database will be used if none of your Information Services data sources are writable.

Attention! The functionality to use the Spotfire database is deprecated since version 11.3 and thus this database will no longer be pre-configured, and cannot be used as the default join database in a Spotfire version 11.7 or later. If you update to a Spotfire version where this functionality is removed, and you don't update your settings before, you won't have a default join database configured. 

When you don't configure a custom default join database, and any of your Information Services data sources is writable, the join will still work without any issues. 

On the other hand, when you are not sure if you have a writable data source, you risk the join to fail when you haven't configured a default join database as a "back-up plan".

How can I decide what database to use for joins?

Spotfire chooses a join database according to certain selection rules. You can determine the order of these selection rules with the configuration property information-services.join-data-source-selection-rule in the Spotfire server configuration. 

With the default settings, Spotfire first searches the data sources in the join for a suitable join database. If it cannot find a writable database then it will use the default join database, if configured.

If you always want to use your configured default join database, change the join-data-source-selection-rule setting to DEFAULT_JOIN_DATA_SOURCE. This modifies the selection order so that Spotfire tries to use the configured default join database first.

You can also determine the database that Spotfire should use for specific joins. In Information Designer, you can determine a join database on the Information link tab under Join path. Here, you can either select one of the data sources in your library, or you can select Use default. In this case, Spotfire follows the selection rules described above.

What database can I use as the default join database? 

You can use a JDBC-compliant database of your choice as the default join database. You need the following to configure a database as the default join database

• You must have an Information Services data source template and a JDBC driver for the database on the Spotfire Server. See Adding a data source template and Installing database drivers.
• The connection URL to the database.
• Username and password for a database user with permission to write temporary tables.

Hint: Although you should not continue using the Spotfire database itself as the default join database, you could set up a different database in the same Database Management System (DBMS) instance, and use that as the default join database.