Adding a database connection

To open a database, first select the data source to which you want to connect. On the Data tab, select Add new database connection from the Data Source drop-down list.

This opens the Database Connections dialog box.

Note: For an alternative way to open this dialog, from the main menu, choose: Tools > Databases...

Data sources. Lists the available data sources. Scroll down if you do not see the desired database. Once you have selected a data source and entered any passwords, click Connect. Click Refresh to update the list.

Mode. Select one of the following modes:
  • Username and password. If the data source is password protected, enter your user name and the associated password.
  • Stored credential. If a credential has been configured in IBM® SPSS® Collaboration and Deployment Services, you can select this option to browse for it in the repository. The credential's user name and password must match the user name and password required to access the database.

Connections. Shows currently connected databases.

  • Default. You can optionally choose one connection as the default. Doing so causes Database source or export nodes to have this connection predefined as their data source, though this can be edited if desired.
  • Save. Optionally select one or more connections that you want to redisplay in subsequent sessions.
  • Data source. The connection strings for the currently connected databases.
  • Preset. Indicates (with a * character) whether preset values have been specified for the database connection. To specify preset values, click this column in the row corresponding to the database connection, and choose Specify from the list. See the topic Specifying preset values for a database connection for more information.

To remove connections, select one from the list and click Remove.

Driver. If you select Driver for the mode instead of Data Source, choose the desired driver from the list.
  • In the Attributes field, enter the database connection string. See your database documentation for the proper string to use.
  • Enter any display name.
  • Enter the database user name and password and click Connect.

To read or write to a database, you must have an ODBC data source that is installed and configured for the relevant database, with read or write permissions as needed. The IBM SPSS Data Access Pack includes a set of ODBC drivers that can be used for this purpose, and these drivers are available from the download site. If you have questions about creating or setting permissions for ODBC data sources, contact your database administrator.

Supported ODBC drivers

For the latest information on which databases and ODBC drivers are supported and tested for use with IBM SPSS Modeler, see the product compatibility matrices on the corporate Support site (http://www.ibm.com/support).

Where to install drivers

Note: ODBC drivers must be installed and configured on each computer where processing might occur.
  • If you are running IBM SPSS Modeler in local (standalone) mode, the drivers must be installed on the local computer.
  • If you are running IBM SPSS Modeler in distributed mode against a remote IBM SPSS Modeler Server, the ODBC drivers need to be installed on the computer where IBM SPSS Modeler Server is installed. For IBM SPSS Modeler Server on UNIX systems, see also "Configuring ODBC drivers on UNIX systems" later in this section.
  • If you need to access the same data sources from both IBM SPSS Modeler and IBM SPSS Modeler Server, the ODBC drivers must be installed on both computers.
  • If you are running IBM SPSS Modeler over Terminal Services, the ODBC drivers need to be installed on the Terminal Services server on which you have IBM SPSS Modeler installed.

Configuring ODBC drivers on UNIX systems

By default, the DataDirect Driver Manager is not configured for IBM SPSS Modeler Server on UNIX systems. To configure UNIX to load the DataDirect Driver Manager, enter the following commands:

cd <modeler_server_install_directory>/bin
rm -f libspssodbc.so

Then run this command if you want to use the UTF8 driver wrapper:

ln -s libspssodbc_datadirect.so libspssodbc.so

Or run this command instead if you want to use the UTF16 driver wrapper:


ln -s libspssodbc_datadirect_utf16.so libspssodbc.so

Doing so removes the default link and creates a link to the DataDirect Driver Manager.

Note: The UTF16 driver wrapper is required to use SAP HANA or IBM Db2 CLI drivers for some databases. DashDB requires the IBM Db2 CLI driver.

To configure SPSS Modeler Server:

  1. Configure the SPSS Modeler Server start up script modelersrv.sh to source the IBM SPSS Data Access Pack odbc.sh environment file by adding the following line to modelersrv.sh:
    . /<pathtoSDAPinstall>/odbc.sh
    Where <pathtoSDAPinstall> is the full path to your IBM SPSS Data Access Pack installation.
  2. Restart SPSS Modeler Server.
In addition, for SAP HANA and IBM Db2 only, add the following parameter definition to the DSN in your odbc.ini file to avoid buffer overflows during connection:
DriverUnicodeType=1
Note: The libspssodbc_datadirect_utf16.so wrapper is also compatible with the other SPSS Modeler Server supported ODBC drivers.

Configuring Snowflake or Big Query databases

If you connect Snowflake or BigQuery using an SDAP driver, which works with either an Excel node or XML node that needs to start with the JVM instance, you might receive this error: 

Internal Error. Failed to open Java VM.

To resolve this issue, edit the SDAP ODBC configuration for these databases so that Modeler and SDAP use the same JVM instance. The following are examples of how to configure the JVM instance for Snowflake.

Windows
  1. In the Progress DataDirect Snowflake ODBC Driver Setup dialog, go to the SQL Engine tab.
  2. Change SQL Engine Modeler to 1-Server.
  3. Click Edit Server Settings, and change the Java Path to point to Java™ installed with Modeler.

    For example, [INSTALLDIR_MODELER]\jre\bin\java.exe

  4. Add -cp com.ddtek.snowflake.phoenix.sql.server.Server after the default settings in JVM Arguments.
  5. Click Apply to save the changes.
  6. Edit the environment variables for Windows, and add [INSTALLDIR_MODELER]\jre\bin and [INSTALLDIR_MODELER]\lib in variable Path.
  7. Start the Modeler client, and reconnect to the snowflake driver.
Linux®
  1. Run these commands:
    1. export JAVA_HOME=/[INSTALLDIR_MODELERSERVER]/jre/
    2. export PATH=$JAVA_HOME/bin:$PATH
  2. In [INSTALLDIR_SDAP]\odbc.ini, change SQLEngineMode=0 to SQLEngineMode=1 for the snowflake driver.
  3. Run this command in the background to start SQL Engine: 
    java -Xmx1024m -cp /[INSTALLDIR_SDAP]/java/lib/snowflake.jar com.ddtek.snowflake.phoenix.sql.server.Server -port 19947 &
  4. Restart the Modeler server in the same console.