Spotfire Connectivity for Oracle Autonomous Data Warehouse (ADW) via Information Links using JDBC with Wallets

Introduction

Here are the steps needed to configure Spotfire's Information Services to connect to Oracle's Autonomous Data Warehouse via JDBC along with using a wallet as part of the authentication mechanism. This has been tested in Spotfire versions 7.11 and 10.2.

The steps to set this up are shown below.

Solution

1. You will need to copy the Oracle JDBC files to the Spotfire Servers Tomcat Lib directory (C:\tibco\tss\10.2.0\tomcat\lib in my example). These files consist of:

• ojdbc8.jar
• ons.jar
• oraclepki.jar
• orai18n.jar
• osdt_cert.jar
• osdt_core.jar
• simplefan.jar
• ucp.jar
• xdb6.jar

As of this document creation time, these files can be accessed from here via the Oracle download here

2. You will need to create an Oracle wallet file that Spotfire will use as part of its connection process. You can see information on creating a wallet here.

3. The wallet zip file you created in step 2 above needs to be copied to the Spotfire Server instance into a location that Tomcat will be able to read. In my example, I've created a directory called C:\tibco\tss\oracle_wallets and I've unzipped the wallet zip file underneath that directory.

4. Via the Spotfire Server Configuration Tool, you will need to add a new Data Source Template. In my example, I've called it ADWOracle. See the screenshot below as well as the template code.

<jdbc-type-settings>
  <type-name>oracleadw</type-name>
  <driver>oracle.jdbc.OracleDriver</driver>
  <connection-url-pattern>jdbc:oracle:thin:@&lt;service name&gt;?TNS_ADMIN=&lt;wallet path&gt;</connection-url-pattern>
  <ping-command>SELECT 1 FROM DUAL</ping-command>
  <metadata-provider>com.spotfire.ws.im.ds.sql.oracle.OracleMetadataProvider</metadata-provider>
  <sql-filter>com.spotfire.ws.im.ds.sql.oracle.OracleSQLFilter</sql-filter>
  <sql-runtime>com.spotfire.ws.im.ds.sql.oracle.OracleSQLRuntime</sql-runtime>
  <fetch-size>10000</fetch-size>
  <batch-size>100</batch-size>
  <table-types>TABLE, VIEW</table-types>
  <supports-catalogs>true</supports-catalogs>
  <supports-schemas>true</supports-schemas>
  <supports-procedures>true</supports-procedures>
  <max-in-clause-size>1000</max-in-clause-size>
  <condition-list-threshold>7000</condition-list-threshold>
  <expand-in-clause>false</expand-in-clause>
  <table-expression-pattern>[$$schema$$.]$$table$$[@$$catalog$$]</table-expression-pattern>
  <procedure-expression-pattern>[$$schema$$.]$$procedure$$[@$$catalog$$]</procedure-expression-pattern>
  <date-format-expression>TO_CHAR($$value$$, 'YYYY-MM-DD')</date-format-expression>
  <time-format-expression>TO_CHAR($$value$$, 'HH24:MI:SS')</time-format-expression>
  <date-time-format-expression>$$value$$</date-time-format-expression>
  <date-time-literal-format-expression>TO_TIMESTAMP('$$value$$', 'YYYY-MM-DD HH24:MI:SS.FF')</date-time-literal-format-expression>
  <java-to-sql-type-conversions>
    <type-mapping>
      <from max-length="4000">String</from>
      <to>VARCHAR2($$value$$)</to>
    </type-mapping>
    <type-mapping>
      <from>String</from>
      <to>VARCHAR2(4000)</to>
    </type-mapping>
    <type-mapping>
      <from>Integer</from>
      <to>NUMBER(10)</to>
    </type-mapping>
    <type-mapping>
      <from>Long</from>
      <to>NUMBER(38)</to>
    </type-mapping>
    <type-mapping>
      <from>Float</from>
      <to>REAL</to>
    </type-mapping>
    <type-mapping>
      <from>Double</from>
      <to>FLOAT</to>
    </type-mapping>
    <type-mapping>
      <from>Date</from>
      <to>DATE</to>
    </type-mapping>
    <type-mapping>
      <from>Time</from>
      <to>DATE</to>
    </type-mapping>
    <type-mapping>
      <from>DateTime</from>
      <to>DATE</to>
    </type-mapping>
  </java-to-sql-type-conversions>
  <create-temp-table-command>CREATE GLOBAL TEMPORARY TABLE $$name$$ $$column_list$$ ON COMMIT PRESERVE ROWS</create-temp-table-command>
  <drop-temp-table-command>TRUNCATE TABLE $$name$$;DROP TABLE $$name$$</drop-temp-table-command>
  <lob-threshold>4000</lob-threshold>
  <procedure-table-jdbc-type>-10</procedure-table-jdbc-type>
  <procedure-table-type-name>REF CURSOR</procedure-table-type-name>
</jdbc-type-settings>

5. You will need to Save the new Spotfire configuration and restart the Spotfire Server.

6. Once the server is restarted, launch the Spotfire Analyst client, go to the Data menu and choose Information Designer. You will then setup a new Data Source as shown below. The <service name> will hold the name of the service you are connecting to (vjtibco_low in my example) and the <wallet path> will hold the name of the wallet file directory (C:\tibco\tss\oracle_wallets\wallet_vjtibco in my example). You will probably need to use '\\' when configuring the wallet path on windows so you would use C:\\tibco\\tss\\oracle_wallets\\wallet_vjtibco for my example.