Migrating from TIBCO-branded DataDirect JDBC drivers

The TIBCO-branded versions of Progress DataDirect JDBC drivers, which came pre-installed with Spotfire Server until version 12.5, are removed. The following drivers are no longer included, or supported in Spotfire:


Driver	Class name	Driver file
DB2 - DataDirect	`tibcosoftwareinc.jdbc.db2.DB2Driver`	TIdb2.jar
MySQL - DataDirect	`tibcosoftwareinc.jdbc.mysql.MySQLDriver`	TImysql.jar
Oracle - DataDirect	`tibcosoftwareinc.jdbc.oracle.OracleDriver`	TIoracle.jar
SQL Server - DataDirect	`tibcosoftwareinc.jdbc.sqlserver.SQLServerDriver`	TIsqlserver.jar
Sybase - DataDirect	`tibcosoftwareinc.jdbc.sybase.SybaseDriver`	TIsybase.jar

You cannot use the TIBCO-branded drivers in a version where they are not included.

If you use any of the drivers in your Spotfire environment, you must migrate to using different JDBC drivers for connectivity.

How to know if your environment is affected?

You might be using the TIBCO-branded drivers for the following functionality in your Spotfire environment:

Connection to the Spotfire database (SQL Server and Oracle only)
You can review the settings for the Spotfire database connection in the configuration tool, or in the bootstrap.xml file.
Connection to the action log database
You can review the settings for the action log database connection in the configuration tool, or in the configuration.xml file.
Connection to the default join database
If you have configured a default join database, you can review the settings in the configuration tool, or with the CLI command show-join-database.
Data sources in Information Services
To find all data sources that are based on a certain data source template, either from the template name or the driver class in the template, you can search in Information Designer. For example:
```
Template name 
Spotfire.DataSourceTemplate.TypeName::mysql_datadirect

Driver class
Spotfire.DataSourceTemplate.DriverClass::tibcosoftwareinc.jdbc.mysql.MySQLDriver
```
Tip: If you have access to the installed client, you can open Information Designer and right-click in the Data Sources tab and select Copy List of Data Sources to Clipboard. The clipboard data obtained provides information about your current data sources and it can be analyzed using the Spotfire client.

When you upgrade your Spotfire environment to version 14.0 or later, the upgrade tool provides information, help, and warnings if you are using TIBCO-branded drivers for any of the above functionality.

Preparation

When you have identified what TIBCO-branded drivers you use, you must prepare your environment by acquiring and installing the drivers to migrate to.

If you select and install one of the following drivers from the official database vendors, the upgrade tool can provide assistance for facilitating the migration:


Vendor driver	Class name
Microsoft JDBC driver for SQL Server Note: This driver is included with Spotfire, along with a data source template (`SQL Server (2005 or newer)`) for Information Services.	`com.microsoft.sqlserver.jdbc.SQLServerDriver`
Oracle JDBC driver	`oracle.jdbc.OracleDriver`
MySQL Connector/J	`com.mysql.cj.jdbc.Driver`
DB2 JDBC Driver	`com.ibm.db2.jcc.DB2Driver`
Sybase JDBC Driver	`com.sybase.jdbc4.jdbc.SybDriver`

Warning: Switching to the Microsoft JDBC driver for SQL Server

If you use the TIBCO-branded JDBC driver for SQL Server, and you switch to the included vendor JDBC driver, read Upgrading to 12.3 and later: Fixing Microsoft SQL Server JDBC driver-related issues. With the default settings, the vendor driver requires that the connection to the database uses encryption. If you are not already using an encrypted connection to the database, you might have to configure your database for encryption with a valid server certificate, or make changes to the connection URL.

Installing a new driver

Acquire and download the new JDBC driver.
On the computer running Spotfire Server, place the driver .jar file in either or both of the following directories, depending on the intended use.
1. Spotfire database, and action log database:
  <installation dir>/tomcat/custom-ext
2. Data access with Information Services, and default join database:
  <installation dir>/tomcat/custom-ext-informationservices
  
  Note: In Spotfire version 12.1 and later, Information Services runs as its own subprocess, and you install JDBC drivers to the Information Services-specific directory custom-ext-informationservices. If you are upgrading from a version earlier than 12.1, install the drivers to the directory where you keep drivers for Information Services.
For Information Services, and the default join database connection, add a data source template for the new driver. Use the configuration tool, or the CLI command add-ds-template.
Tip: Data source templates for the Microsoft JDBC driver for SQL Server (SQL Server (2005 or newer)), and the Oracle JDBC driver (Oracle) are included with Spotfire. The Oracle data source template is disabled by default, and you must enable it in the configuration tool, or with the CLI command modify-ds-template, before you can use it.
For more data source templates, see this page on the Community.

After installing your new JDBC drivers, you might prefer to manually change your configuration to use the drivers, before you upgrade Spotfire. See Switching drivers manually below.

After the switch to new drivers, what is different?

After switching to new JDBC drivers, the drivers are no longer updated to new versions when you upgrade Spotfire. It is important that you maintain your drivers by installing new versions as they become available.

Note: The Microsoft JDBC Driver for SQL Server, which you can use in place of the TIBCO-branded JDBC driver for SQL Server, is included with Spotfire Server. Any other driver, you must obtain from the vendor.

Also keep in mind that there might be driver-specific differences between the TIBCO-branded drivers and the ones you decide to switch to.

Switching drivers manually before you upgrade

After you install your new JDBC driver, you can manually update your configuration to use it. This way, you can perform the migration before you upgrade Spotfire.

The changes you have to make might depend on the driver you are changing to. The minimum required configuration change is to update the connection URL/connection string and the driver class setting.

Spotfire database connection

Change the setting driver-class to the driver class of your new JDBC driver.
Change the setting database-url, according to the format required for your new JDBC driver.

The connection settings for the Spotfire database are stored in the bootstrap file, bootstrap.xml. To change settings in the bootstrap file, use the configuration tool, or the CLI command update-bootstrap.

Action log database connection

Change the setting driver-class to the driver class of your new JDBC driver.
Change the setting database-url, according to the format required for your new JDBC driver.

To modify the action log database connection settings, you can use the configuration tool, or the CLI command config-action-log-database-logger.

Default join database connection

Change the setting type to the type name of the new data source template.
Change the setting database-url, according to the format required for your new JDBC driver.

To modify the default join database connection, use the configuration tool, or the CLI command create-join-db.

Information Services

If you only have a few data sources that use TIBCO-branded JDBC drivers, you can update the sources manually in Information Designer before you upgrade Spotfire. For each data source, you must update the settings Data source template, and Connection URL.

If you have a very large number of data sources that use TIBCO-branded JDBC drivers, it might be more convenient to update the data sources after you have upgraded Spotfire, with the use of the CLI command create-datadirect-datasources-update-script. See Updating Information Services data sources below.

Switching drivers during the upgrade of Spotfire

If you run the upgrade tool interactively, you can migrate to your new drivers during the upgrade of Spotfire to version 14.0 or later. Before you start the upgrade, make sure you have followed the steps in Preparation.

Note: If you run the upgrade tool silently, you must switch to your new drivers by manually changing the configuration before the upgrade. See Switching drivers manually.

Warning: Before you run the upgrade tool, remember to create a working backup of your Spotfire database.

During the upgrade, if you run the upgrade tool interactively, the upgrade tool assists you with migrating to your new JDBC drivers in the following ways:

Spotfire database and action log database connections: If you have installed a suggested database vendor JDBC driver, the upgrade tool suggests a modified connection URL/connection string for using the new driver. Review the connection URL before you continue.
Default join database: If you have installed a new JDBC driver, and added a data source template for the driver, you can configure your default join database connection during the upgrade. You must manually select the data source template, and edit the connection URL with the driver class of your new driver.
Information Services: See the section Updating Information Services data sources below.

Updating Information Services data sources

If you use TIBCO-branded drivers for data access with Information Services, you must add new data source templates, and update all corresponding data sources.

After upgrading Spotfire, you can update all data sources that use a certain TIBCO-branded JDBC driver (based on the data source template) at the same time, with the use of the CLI command create-datadirect-datasources-update-script. When you run the command, Spotfire identifies all data sources that are based on the specified data source template, and generates a script file. With the script file, you can update all the identified data sources to use a new data source template, and an updated connection URL.

Updating data sources with create-datadirect-datasources-update-script

To generate a script file for updating all data sources for a certain data source template, run the following command:

config create-datadirect-datasources-update-script --source-type <Template type name> --target-type <Target template type name>

Before you run the update script file that was generated, review the report file UpdateReport.html, which lists all the data sources and changes that the script will make.

Note: For Microsoft SQL Server data sources, be aware that the included vendor driver has different default settings for encryption. With the default settings, TLS encryption is enabled, and a valid, not self-signed, server certificate is required. To learn more, see Upgrading to 12.3 and later: Fixing Microsoft SQL Server JDBC driver-related issues.
If you do not configure your Microsoft SQL Server instance for encryption, with a valid server certificate, you can add the setting trustServerCertificate=true either to the data source template, or to each connection URL in the UpdateScript.txt script file.

To run the script file, run the following command:

config run UpdateScript.txt -fail-on-undefined-variable -VlibraryAdmin=<libraryAdmin>