Client drivers

• 1: Driver prerequisites
• 1.1: ODBC prerequisites
• 1.2: ADO.NET prerequisites
• 1.3: Python prerequisites
• 1.4: Perl prerequisites
• 1.5: PHP prerequisites
• 2: Upgrading the client drivers
• 3: Setting a client connection label
• 4: Additional parameter settings
• 5: Using legacy drivers
• 6: Modifying the Java CLASSPATH
• 7: Installing the client drivers on Linux and UNIX-Like platforms
• 7.1: Installing driver managers on Linux and other UNIX-like platforms
• 7.2: Installing the client RPM on red hat and SUSE
• 7.3: Installing JDBC driver on Linux
• 7.4: Installing ODBC drivers on Linux
• 7.4.1: Required ODBC driver configuration settings for Linux and UNIX
• 7.4.2: Setting ODBC driver settings on Linux and UNIX-Like platforms
• 8: Installing the FIPS client drivers
• 8.1: Installing the FIPS client driver for ODBC and vsql
• 8.2: Installing the FIPS client driver for JDBC
• 9: Installing the client drivers and tools on Windows
• 9.1: Installing the JDBC client driver for windows
• 9.2: The Vertica client drivers and tools for windows
• 9.2.1: System prerequisites
• 9.2.1.1: .NET framework
• 9.2.1.2: Microsoft visual studio
• 9.2.1.3: Microsoft SQL server
• 9.2.2: Download the client drivers and tools for windows
• 9.2.3: Install or upgrade client drivers and tools for windows
• 9.2.4: Post-installation steps for ODBC driver and vsql client
• 9.2.5: Uninstalling, modifying, or repairing the client drivers and tools
• 10: Components of the client drivers and tools on Windows
• 10.1: The ODBC client driver for windows
• 10.1.1: ODBC driver settings on Windows
• 10.1.2: Diverting ODBC log entries to ETW
• 10.2: The vsql client for windows
• 10.2.1: Using vsql for windows users
• 10.3: The Microsoft connectivity pack for windows
• 10.3.1: Microsoft components
• 10.3.1.1: Microsoft component configuration
• 10.3.1.2: BIDS and SSDT-BI
• 10.3.1.3: SQL server analysis services (SSAS) support
• 10.3.1.4: SQL server integration services (SSIS) support
• 10.3.1.5: SQL server reporting services (SSRS) support
• 10.3.2: Compatibility issues and limitations
• 10.3.2.1: BIDS and SSDT-BI limitations
• 10.3.2.2: SSAS limitations
• 10.3.2.3: SSIS data type limitations
• 10.3.2.4: SSRS limitations
• 10.4: The OLE DB client driver for windows
• 10.4.1: OLE DB connection properties
• 10.4.2: Diverting OLE DB log entries to ETW
• 10.5: The ADO.NET driver for windows
• 10.6: The visual studio plug-in for windows
• 10.6.1: Visual studio limitations
• 11: Installing the client drivers on Mac OS X
• 11.1: Installing the JDBC driver on Mac OS X
• 11.2: Installing the ODBC driver on Mac OS X
• 11.2.1: Download the driver
• 11.2.2: Install the Mac OS X ODBC driver
• 11.2.3: Silently install the Mac OS X ODBC driver
• 11.2.4: Uninstall the Mac OS X ODBC driver
• 11.2.5: Upgrade or downgrade the Mac OS X ODBC driver
• 11.2.6: ODBC driver settings on Mac OS X

1 - Driver prerequisites

The following topics describe the system requirements for the client drivers. You need to ensure that your client system meets these requirements before installing and using the client drivers.

1.1 - ODBC prerequisites

There are several requirements your client systems must meet before you can install the Vertica ODBC drivers.

Operating system

The Vertica ODBC driver requires a supported platform. The list of currently-supported platforms can be found at Client drivers support.

ODBC driver manager

ODBC driver requires that the client system have a supported driver manager. See Installing driver managers on Linux and other UNIX-like platforms for details.

UTF-8, UTF-16 and UTF-32 support

The Vertica ODBC driver is a universal driver that supports UTF-8, UTF-16, and UTF-32 encoding. The default setting depends on the client platform. (see Required ODBC driver configuration settings for Linux and UNIX for more information).

When using the driver with the DataDirect Connect driver manager, DataDirect Connect adapts to the ODBC driver's text encoding settings. You should configure the ODBC driver to use the encoding method that your application requires. This allows strings to be passed between the driver and the application without intermediate conversion.

See also

• Client drivers
• Programming ODBC client applications
• Creating an ODBC data source name (DSN)

1.2 - ADO.NET prerequisites

The Vertica driver for ADO.NET requires the following software and hardware components:

Operating system

The Vertica ADO.NET driver requires a supported Windows operating system. The list of supported platforms can be found in the Supported Platforms document at https://docs.vertica.com/latest.

Memory

Vertica suggests a minimum of 512MB of RAM.

.NET framework

The requirements for the .NET framework for ADO.NET in Vertica can be found in the Supported Platforms document at https://docs.vertica.com/latest.

See also

• Programming ADO.NET applications

1.3 - Python prerequisites

Python is a free, agile, object-oriented, cross-platform programming language designed to emphasize rapid development and code readability. Python has been released under several different open source licenses.

Vertica's ODBC driver is tested with multiple versions of Python. See Perl and Python requirements for details.

Python driver

Vertica requires the open source Vertica Python Client or the pyodbc driver module. See your system's Python documentation for installation and configuration information.

Supported operating systems

The Vertica ODBC driver requires one of the operating systems listed in ODBC prerequisites.

For usage and examples, see Programming Python client applications.

1.4 - Perl prerequisites

Perl is a free, stable, open source, cross-platform programming language licensed under its Artistic License, or the GNU General Public License (GPL).

Your Perl scripts access Vertica through its ODBC driver, using the Perl Database Interface (DBI) module with the ODBC Database Driver (DBD::ODBC). The Vertica ODBC driver is known to be compatible with these versions of Perl:

• 5.8

• 5.10

Later Perl versions may also work.

Perl drivers

The following Perl driver modules have been tested with the Vertica ODBC driver:

• The DBI driver module, version 1.609

• The DBD::ODBC driver module, version 1.22

Other versions may also work.

Supported client systems

The Vertica ODBC driver requires one of the operating systems and driver managers listed in ODBC prerequisites.

1.5 - PHP prerequisites

PHP is a widely-used general-purpose scripting language that is especially suited for Web development and can be embedded into HTML. PHP is licensed under the PHP License, an open-source BSD-style license certified by the Open Source Initiative.

PHP modules

The following PHP modules are required:

• php

• php-odbc

• php-pdo

• UnixODBC (if you are using the Unix ODBC driver)

• libiodbc (if you are using the iODBC driver)

Supported client systems

The Vertica ODBC driver requires one of the operating systems and driver managers listed in ODBC prerequisites.

2 - Upgrading the client drivers

The Vertica client drivers are usually updated for each new release of the Vertica server. The client driver installation packages include the version number of the corresponding Vertica server release. Usually, the drivers are forward-compatible with the next release, so your client applications are still be able to connect using the older drivers after you upgrade to the next version of Vertica Analytics Platform server. See Client driver and server version compatibility for details on which client driver versions work with each version of Vertica server.

You should upgrade your clients as soon as possible after upgrading your server, to take advantage of new features and to maintain maximum compatibility with the server.

To upgrade your drivers, follow the same procedure you used to install them in the first place. The new installation will overwrite the old. See the specific instructions for installing the drivers on your client platform for any special instructions regarding upgrades.

3 - Setting a client connection label

You can set a client connection label when you connect to a Vertica database. You can also set or return the client connection label using the SET_CLIENT_LABEL and GET_CLIENT_LABEL functions.

Set the client connection label:

=> SELECT SET_CLIENT_LABEL('py_data_load_application');
               SET_CLIENT_LABEL
----------------------------------------------
 client_label set to py_data_load_application
(1 row)

Return the current client connection label:

=> SELECT GET_CLIENT_LABEL();
     GET_CLIENT_LABEL
--------------------------
 py_data_load_application
(1 row)

JDBC

The JDBC Client has a method to set and return the client connection label: getClientInfo() and setClientInfo(). You can use these methods with the SQL Functions GET_CLIENT_LABEL and SET_CLIENT_LABEL.

When you use these two methods, make sure you pass the string value APPLICATIONNAME to both the setter and getter methods.

Use setClientInfo() to create a client label, and use getClientInfo() to return the client label:

import java.sql.*;
import java.util.Properties;

public class ClientLabelJDBC {

    public static void main(String[] args) {
        Properties myProp = new Properties();
        myProp.put("user", "dbadmin");
        myProp.put("password", "");
        myProp.put("loginTimeout", "35");
        Connection conn;
        try {
            conn = DriverManager.getConnection(
                    "jdbc:vertica://docc05.verticacorp.com:5433/doccdb", myProp);
            System.out.println("Connected!");
            conn.setClientInfo("APPLICATIONNAME", "JDBC Client - Data Load");
            System.out.println("New Conn label: " + conn.getClientInfo("APPLICATIONNAME"));
            conn.close();
        } catch (SQLTransientConnectionException connException) {
            // There was a potentially temporary network error
            // Could automatically retry a number of times here, but
            // instead just report error and exit.
            System.out.print("Network connection issue: ");
            System.out.print(connException.getMessage());
            System.out.println(" Try again later!");
            return;
        } catch (SQLInvalidAuthorizationSpecException authException) {
            // Either the username or password was wrong
            System.out.print("Could not log into database: ");
            System.out.print(authException.getMessage());
            System.out.println(" Check the login credentials and try again.");
            return;
        } catch (SQLException e) {
            // Catch-all for other exceptions
            e.printStackTrace();
        }
    }
}

When you run this method, it prints the following result to the standard output:

Connected!
New Conn Label: JDBC Client - Data Load

4 - Additional parameter settings

The following parameters can be set for the Vertica client drivers.

Logging settings

These parameters control how messages between the client and server are logged. None of these settings are required. If they are not set, then the client library does not log any messages. They apply to both ADO.NET and ODBC.

• LogLevel—The severity of messages that are logged between the client and the server. The valid values are:

  • 0—No logging

  • 1—Fatal errors

  • 2—Errors

  • 3—Warnings

  • 4—Info

  • 5—Debug

  • 6—Trace (all messages)

  The value you specify for this setting sets the minimum severity for a message to be logged. For example, setting LogLevel to 3 means that the client driver logs all warnings, errors, and fatal errors.

  Note

  On a Windows client, you have the option of directing ODBC or OLE DB log entries to Event Tracing for Windows (ETW). Once set, ODBC log entries appear in the Windows Event Viewer. See Diverting ODBC log entries to ETW. for ODBC, or Diverting OLE DB log entries to ETW for OLE DB.

• LogPath—The absolute path of a directory to store log files . For example: /var/log/verticaodbc

• LogNamespace—Limits logging to messages generated by certain objects in the client driver.

ODBC-specific settings

The following settings are used only by the Vertica ODBC client driver.

• DriverManagerEncoding—The UTF encoding standard that the driver manager uses. This setting needs to match the encoding the driver manager expects. The available values for this setting are:

  • UTF-8

  • UTF-16 (usually used by unixODBC)

  • UTF-32 (usually used by iODBC)

  See the documentation for your driver manager to find the correct value for this setting.

  Note

  While both UTF-16 and UTF-8 are valid settings for DataDirect, Vertica recommends that you set the DataDirect driver manager encoding to UTF-16.

  If you do not set this parameter, the ODBC driver defaults to the value shown in the following table. If your driver manager uses a different encoding, you must set this value for the ODBC driver to be able to work.

  Client Platform	Default Encoding
  Linux 32-bit	UTF-32
  Linux 64-bit	UTF-32
  Linux Itanium 64-bit	UTF-32
  OS X	UTF-32
  Windows 32-bit	UTF-16
  Windows 64-bit	UTF-16

• ErrorMessagesPath—The absolute path to the parent directory that contains the Vertica client driver's localized error message files. These files are usually stored in the same directory as the Vertica ODBC driver files.

• ODBCInstLib—The absolute path to the file containing the ODBC installer library (ODBCInst). This setting is required if the directory containing this library is not set in the LD_LIBRARY_PATH or LIB_PATH environment variables. The library files for the major driver manager are:

• UnixODBC: libodbcinst.so

• iODBC: libiodbcinst.so (libiodbcinst.2.dylib on OS X)

• DataDirect: libodbcinst.so

ADO.NET-specific settings

This setting applies only to the ADO.NET client driver:

C#PreloadLogging—Tells the Vertica ADO.NET driver to begin logging as soon as possible, before the driver has fully loaded itself. Normally, logging only starts after the driver has fully loaded. Valid values for this setting are:

• 0—Do not start logging before the driver has loaded.

• 1—Start logging as soon as possible.

5 - Using legacy drivers

The Vertica server supports connections from previous versions of the client drivers. For detailed information the compatibility between versions of the Vertica server and Vertica client, see Client driver and server version compatibility.

6 - Modifying the Java CLASSPATH

The CLASSPATH environment variable contains the list of directories where the Java run time looks for library class files. For your Java client code to access Vertica, you need to add the directory where the Vertica JDBC .jar file is located.

Linux and OS X

If you are using the Bash shell, use the export command to define the CLASSPATH variable:

# export CLASSPATH=/opt/vertica/java/lib/vertica-jdbc-x.x.x.jar

If environment variable CLASSPATH is already defined, use the following command to prevent it from being overwritten:

# export CLASSPATH=$CLASSPATH:/opt/vertica/java/lib/vertica-jdbc-x.x.x.jar

If you are using a shell other than Bash, consult its documentation to learn how to set environment variables.

You need to either set the CLASSPATH environment variable for every login session, or insert the command to set the variable into one of your startup scripts (such as ~/.profile or /etc/profile).

Windows

Provide the class paths to the .jar, .zip, or .class files.

C:> SET CLASSPATH=classpath1;classpath2...

For example:

C:> SET CLASSPATH=C:\java\MyClasses\vertica-jdbc-x.x.x.jar

As with the Linux/UNIX settings, this setting only lasts for the current session. To set the CLASSPATH permanently, set an environment variable:

1. On the Windows Control Panel, click System.

2. Click Advanced or Advanced Systems Settings.

3. Click Environment Variables.

4. Under User variables, click New.

5. In the Variable name box, type CLASSPATH.

6. In the Variable value box, type the path to the Vertica JDBC .jar file on your system (for example, C:\Program Files (x86)\Vertica\JDBC\vertica-jdbc-x.x.x.jar)

Specifying the library directory in the Java command

There is an alternative way to tell the Java run time where to find the Vertica JDBC driver other than changing the CLASSPATH environment variable: explicitly add the directory containing the .jar file to the java command line using either the -cp or -classpath argument. For example, on Linux, start your client application using:

# java -classpath /opt/vertica/java/lib/vertica-jdbc-x.x.x.jar myapplication.class

Your Java IDE may also let you add directories to your CLASSPATH, or let you import the Vertica JDBC driver into your project. See your IDE documentation for details.

7 - Installing the client drivers on Linux and UNIX-Like platforms

This topic explains how to install the client drivers on Linux and UNIX-like platforms.

7.1 - Installing driver managers on Linux and other UNIX-like platforms

If your client platform does not already have an ODBC driver manager, you need to install one before you can use the Vertica ODBC client driver. The driver manager provides an interface between your client operating system and the ODBC drivers. See Client drivers support for a list of driver managers that are supported on each of the client platforms.

Driver managers can be downloaded from your operating system specific repository and from the links below.

Vertica does not provide instructions for installing and configuring these third-party binaries. For download and configuration information, see the respective driver manager websites for installation and configuration information:

• UnixODBC: http://www.unixodbc.org/

• iODBC: http://www.iodbc.org

7.2 - Installing the client RPM on red hat and SUSE

For Red Hat Enterprise Linux and SUSE Linux Enterprise Server, you can download and install a client driver RPM that installs both the ODBC and JDBC driver libraries and the vsql client.

To install the client driver RPM package:

1. Open a Web browser and go to Vertica driver downloads page.

2. Find the drivers for your version of Vertica.

3. Download the client package file that matches your client platform's architecture.

   Note

   The 64-bit client driver RPM installs both the 64-bit and 32-bit ODBC driver libraries on non-FIPS compliant systems.

4. If you did not directly download the driver package on the client system, transfer the file to the client.

5. Install the driver package you downloaded. On Windows and Mac, double-click the package file to start the install process. On Linux, if your distribution uses RPm package, install the RPM with the command:

   # rpm -Uvh package_name.rpm
   

   If your Linux distribution does not use RPM packages, expand the .tar.gz file in a convenient location on your system.

Once you have installed the client package, you need to create a DSN (see Creating an ODBC DSN for Linux) and set some additional configuration parameters (see Required ODBC driver configuration settings for Linux and UNIX) to use ODBC. To use JDBC, you need to modify your class path (see Modifying the Java CLASSPATH) before you can use JDBC.

You may also want to add the vsql client to your PATH environment variable so that you do not need to enter its full path to run it. You add it to your path by adding the following to the .profile file in your home directory or the global /etc/profile file:

export PATH=$PATH:/opt/vertica/bin

7.3 - Installing JDBC driver on Linux

The JDBC driver is available for download from the Vertica driver downloads page. There is a single .jar file that works on all platforms and architectures. To download and install the file:

1. Open a Web browser and go to the Vertica driver downloads page.

2. In section that corresponds to your version of Vertica, click the link for the JDBC driver.

3. You need to copy the .jar file you downloaded to a directory in your Java CLASSPATH on every client system with which you want to access Vertica. You can either:

   • Copy the .jar file to its own directory (such as /opt/vertica/java/lib) and then add that directory to your CLASSPATH (recommended). See Modifying the Java CLASSPATH for details.

   • Copy the .jar file to directory that is already in your CLASSPATH (for example, a directory where you have placed other .jar files on which your application depends).

7.4 - Installing ODBC drivers on Linux

Read Driver prerequisites before you proceed.

For Red Hat Enterprise Linux and SUSE Linux Enterprise Server, you can download and install a client RPM that installs both the ODBC and JDBC driver and the vsql client. See Installing the client RPM on red hat and SUSE.

The ODBC driver installation packages are broken down by client platform on the Vertica driver downloads page. The package's filename is named based on its operating system and architecture (for example, vertica_ 11.1.x.xx_odbc_x86_64_linux.tar.gz)

Installation procedure

1. Open a web browser and browse to the Vertica driver downloads page.

2. Locate and download the driver package that matches your Vertica version and platform.

3. If you did not directly download to the client system, transfer the downloaded file to it.

4. Log in to the client system as root.

5. If the directory /opt/vertica/ does not exist, create it:

   # mkdir -p /opt/vertica/
   

6. Copy the downloaded file to the /opt/vertica/ directory. For example:

   # cp vertica_11.1.x.xx_odbc_x86_64_linux.tar.gz /opt/vertica/
   

7. Change to the /opt/vertica/ directory:

   # cd /opt/vertica/
   

8. Uncompress the file you downloaded. For example:

   $ tar vzxf vertica_ 11.1.x.xx_odbc_x86_64_linux.tar.gz

   Two folders are created: one for the include file, and one for the library files. The path of the library file depends on the processor architecture: lib for 32-bit libraries, and lib64 for 64-bit libraries. So, a 64-bit driver client download creates the directories:

   • /opt/vertica/include, which contains the header file

   • /opt/vertica/lib64, which contains the library file

Post driver installation configuration

You must configure the ODBC driver before you can use it. There are two required configuration files:

• The odbc.ini configuration file defines the Data Source Names (DSNs) that tell the ODBC how to access your Vertica databases. See Creating an ODBC Data Source Name for instructions to create this file.

• The vertica.ini configuration file defines some Vertica-specific settings required by the drivers. See Required ODBC driver configuration settings for Linux and UNIX for instructions to create this file.

7.4.1 - Required ODBC driver configuration settings for Linux and UNIX

In addition to DSN settings, Vertica provides additional ODBC client driver configuration parameters. These settings control the following:

• The text encoding used by the driver manager (for example, UTF-8 or UTF-16).

• The location of the directory containing the Vertica ODBC driver's error message files.

• Whether and how the ODBC driver logs messages.

On Linux and UNIX platforms, you must provide these additional settings manually so that the ODBC driver can function properly. To do so, edit the vertica.ini file to supply the necessary additional configuration settings. You specify where the ODBC driver can find the vertica.ini file using an environment variable named VERTICAINI. See Setting ODBC driver settings on Linux and UNIX-Like platforms.

7.4.2 - Setting ODBC driver settings on Linux and UNIX-Like platforms

Driver settings specific to Vertica are stored in a text file named vertica.ini (although you may choose a different file name). On Linux and UNIX platforms, you must edit the vertica.ini file to supply additional configuration settings before the ODBC driver can function properly. You tell the Vertica ODBC driver where to find the vertica.ini file using an environment variable named VERTICAINI.

Required settings

On Linux and UNIX platforms, you must configure two settings in order for the ODBC driver to work correctly:

• ErrorMessagesPath

• ODBCInstLib (unless the driver manager's installation library is in a directory listed in the LD_LIBRARY_PATH or LIB_PATH environment variables).

If your driver manager does not use UTF-8 encoding, you need to set DriverManagerEncoding to the proper encoding.

Create a vertica.ini file

There is no standard location for the vertica.ini file—you can store the file anywhere that it is convenient for you on your client system. One possible location is in the /etc directory if you have multiple users on your client system that need to access it. You can also have a vertica.ini file in each user's home directory so users can alter their own settings. Wherever you store it, be sure users have read access to the file.

The format of the vertica.ini file is similar to the odbc.ini file, with a section followed by parameter definitions. Unlike the odbc.ini file, vertica.ini contains a single section named Driver:

[Driver]

Following the section definition, you add setting definitions, one per line. A setting definition consists of the setting name, followed by an equal sign (=), followed by the value. The value does not need quotes. For example, to set the ODBCInstLib setting, you add a line like this:

ODBCInstLib=/usr/lib64/libodbcinst.so

See Additional parameter settings for a list of the additional settings.

Set the VERTICAINI environment variable

You must set an environment variable named VERTICAINI to the absolute path of the vertica.ini file. The Vertica ODBC driver uses this variable to find the settings.

Where you set this variable depends on whether users on your client system need to have separate vertica.ini files. If you want to have a single, system-wide vertica.ini file, you can add a command to set VERTICAINI in /etc/profile or some other system-wide environment file. For example:

export VERTICAINI=/etc/vertica.ini

If users need individual vertica.ini files, set VERTICAINI in their ~/.profile or similar configuration file. For example:

export VERTICAINI=~/.vertica.ini

Example vertica.ini file

The following example vertica.ini file configures the ODBC driver to:

• use the 64-bit UNIXODBC driver manager.

• get its error messages from the standard Vertica 64-bit ODBC driver installation directory.

• log all warnings and more severe messages to log files stored in the temporary directory.

[Driver]
DriverManagerEncoding=UTF-16
ODBCInstLib=/usr/lib64/libodbcinst.so
ErrorMessagesPath=/opt/vertica
LogLevel=4
LogPath=/tmp

8 - Installing the FIPS client drivers

This topic details how to install the FIPS client drivers for JDBC and ODBC.

8.1 - Installing the FIPS client driver for ODBC and vsql

Vertica offers a FIPS client for FIPS-compatible systems. A FIPS-compatible system is FIPS-enabled and includes the OpenSSL libraries.

The FIPS client supports ODBC and vsql and is offered in 64-bit only.

Prerequisites

Verify that your host system is running a FIPS-compliant operating system that Vertica supports.

The FIPS client installer checks your host system for the value of the sysctl parameter, crypto.fips_enabled. You must set this parameter to 1 (enabled). If your host is not enabled, the client does not install.

For other prerequisites, related specifically to ODBC, see ODBC prerequisites.

Installing the FIPS client

To install the FIPS client driver package:

1. Download the FIPS client package from the Vertica driver downloads page.

2. Log in to the client system as root.

3. Install the RPM package that you downloaded:

   # rpm -Uvh package_name.rpm
   

For ODBC, once you have installed the client package, you need to create a DSN and set some additional configuration parameters. For more information, see:

• Creating an ODBC DSN for Linux
• Required ODBC driver configuration settings for Linux and UNIX

You may also want to add the vsql client to your PATH environment variable so that you do not need to enter its full path to run it. To do so, add the following to the .profile file in your home directory or the global /etc/profile file:

export PATH=$PATH:/opt/vertica/bin

Client searches for OpenSSL libraries

When you launch the client application to connect to the server, the client searches for and loads the OpenSSL libraries libcrypto.so.10 and libssl.so.10 for supported OpenSSL versions:

• The client first checks to see if LD_LIBRARY_PATH is set.

• If the LD_LIBARY_PATH location does not include the libraries, it checks RunPath, either /opt/vertica/lib or within the ODBC or vsql directory structure (../lib).

The following figure depicts the search for the OpenSSL libraries:

8.2 - Installing the FIPS client driver for JDBC

Vertica offers a JDBC client driver that is compliant with the Federal Information Processing Standard (FIPS). Use this JDBC client driver to access systems that are FIPS-compatible. For more information on FIPS in Vertica, see Federal information processing standard.

Implementing FIPS on a JDBC client requires a third-party JRE extension called BouncyCastle, a collection of APIs used for cryptography. Use BouncyCastle APIs with JDK 1.7 and 1.8, and a FIPS-compliant operating system that Vertica supports.

You need to add the FIPS BouncyCastle jar as the JVM JSSE provider, as follows:

1. Download the BouncyCastle FIPS jar file bc-fips-1.0.0.jar from the BouncyCastle download page.

2. Add bc-fips-1.0.0.jar as a JRE library extension:

   <path to jre>/lib/ext/bc-fips-1.0.0.jar
   

3. Add BouncyCastle as an SSL security provider in <path to jre>/lib/security/java.security:

   security.provider.1=org.bouncycastle.jcajce.provider.BouncyCastle FipsProvider
   security.provider.2=com.sun.net.ssl.internal.ssl.Provider BCFIPS
   security.provider.3=sun.security.provider.Sun
   

4. Use the following JVM java -D system property command arguments to set the KeyStore and TrustStore files to BCFIPS:

   export JAVA_OPTS="$JAVA_OPTS -Djavax.net.ssl.keyStoreProvider=BCFIPS export JAVA_OPTS="$JAVA_OPTS -Djavax.net.ssl.trustStoreProvider=BCFIPS
   

   For information on setting the SSL Keystore and Truststore, see Configuring TLS for JDBC clients.

5. Set the default type for the KeyStore implementation to BCFKS in <path to jre>/lib/security/java.security:

   keystore type=BCFKS
   ssl.keystore.type=BCFKS
   

6. On the command line, run the following command from <path to jre>/lib/ext to create the keystore and truststore. Make sure you use the BCFKS type:

   $ <java bin path> keytool -keystore vertica.kafka.keystore.bcfks
   -storetype BCFKS
   -providername BCFIPS
   -providerclass org.bouncycastle.jcajce.provider.BouncyCastleFipsProvider
   -provider org.bouncycastle.jcajce.provider.BouncyCastleFipsProvider 
   -providerpath bc-fips-1.0.0.jar -alias CARoot -import -file (server.crt.der file path)
   

7. Enter the keystore password when prompted. The following message appears:

   "Certificate was added to the keystore"
   

8. Run the Java program with SSL DB:

   1. Copy the vertica.kafka.keystore.bcfks keyStore from <path to jre>/lib/ext/ to the java program folder.

   2. Convert the Vertica server certificate to a form that java understands:

      <java bin path> keytool -keystore verticastore -keypasswd -storepass password -importkeystore -noprompt -alias verticasql -import -file server.crt.der
      

   3. Download the latest vertica JDBC driver from the Vertica download page.

9. After creation of verticastore, keyStore, and download jar, execute the following command to run Java with debugging to test the implementation:

   $ java -Djavax.net.debug=ssl -Djavax.net.ssl.keyStore='vertica.kafka.keystore.bcfks' -Djavax.net.ssl.keyStorePassword='password' -Djavax.net.ssl.trustStore='<path to verticastore>/verticastore' -Djavax.net.ssl.trustStorePassword='password' -cp .:vertica-jdbc-11.1.0-0.jar FIPSTest
   

9 - Installing the client drivers and tools on Windows

This section details how to install the client drivers and tools on Windows.

For connectivity through a JDBC connection, see Installing the JDBC client driver for windows. For all other client drivers and tools, see The Vertica client drivers and tools for windows.

9.1 - Installing the JDBC client driver for windows

To install the Vertica JDBC driver on your Windows client system, download the cross-platform JDBC driver .jar file to your system from the Vertica driver downloads page.

Choose how Java locates the JDBC driver library

For your Java client application to use the Vertica JDBC driver, the Java interpreter must be able to find the driver's library file. Choose one of these methods to tell the Java interpreter where to look for the library:

• Copy the JDBC .jar file you downloaded to the system-wide Java Extensions folder (for example, C:\Program Files\Java\jrex.x.x_x\lib\ext).

• Add the directory containing the JDBC .jar file to the CLASSPATH environment variable (see Modifying the Java CLASSPATH).

• Specify the directory containing the JDBC .jar using the -cp argument in the Java command line you use to start your Java application.

9.2 - The Vertica client drivers and tools for windows

Download the Vertica Client Drivers and Tools for Windows installer from the Vertica driver downloads page. You can run the installer on a 32-bit or 64-bit system.

Components

The Vertica Client Drivers and Tools for Windows installs the following components on your system:

• The ODBC client driver for windows
• The OLE DB client driver for windows
• The vsql client for windows
• The ADO.NET driver for windows
• The Microsoft connectivity pack for windows
• The visual studio plug-in for windows

Read System prerequisites before you proceed.

9.2.1 - System prerequisites

The Vertica Client Drivers and Tools for Windows has basic system prerequisite requirements. The pack also requires that specific Microsoft components be installed for full integration.

For a list of all prerequisites, see Client drivers support in the Supported Platforms document.

Fully update your system

Before you install the Vertica driver package, verify that your system is fully up to date with all Windows updates and patches. See the documentation for your version of Windows for instructions on how to run Windows update. The Vertica client libraries and vsql executable install updated Windows libraries that depend on Windows service packs. Be sure to resolve any issues that block the installation of Windows updates.

If your system is not fully up-to-date, you may receive error messages about missing libraries such as api-ms-win-crt-runtime-l1-1-0.dll when starting vsql.

9.2.1.1 - .NET framework

The .NET framework is not bundled into the Vertica Client Drivers and Tools for Windows. However, during installation, a web installer launches if Microsoft .NET 3.5 SP1 is not detected on your system. You then have the opportunity to download the framework. Also, if your operating system version includes .NET 3.5 SP1, but it is not turned on, the installer turns on the feature.

If you have Visual Studio 2010 or 2012 installed, your system already includes Microsoft .NET Framework 4.0 or 4.5, respectively. You also need Microsoft .NET 3.5 SP1 to use the Vertica Client Drivers and Tools for Windows integration features.

Use the following links to download the appropriate version of .NET framework directly from Microsoft:

• For .NET Framework 3.5 SP1:

  http://www.microsoft.com/en-us/download/details.aspx?id=22

• For .NET Framework 4.0:

  http://www.microsoft.com/en-us/download/details.aspx?id=17851

• For .NET Framework 4.5:

  http://www.microsoft.com/en-us/download/details.aspx?id=17851

9.2.1.2 - Microsoft visual studio

The Vertica Client Drivers and Tools for Windows installer provides a Visual Studio plug-in which allows you to use Vertica as a Visual Studio Data Source for Visual Studio 2008, 2010, 2012, 2013, or 2015. The connection properties for the plug-in are the same as ADO.NET connection properties.

After installing the plug-in, you can use it to access your Vertica database from within Visual Studio. If you do not have the SDK installed, download the SDK specific to your version of Visual Studio.

• For the Microsoft Visual Studio 2008 SDK:

  http://www.microsoft.com/en-us/download/details.aspx?id=508

• For the Microsoft Visual Studio 2008 SP1 SDK:

  http://www.microsoft.com/en-us/download/details.aspx?id=21827

• For the Microsoft Visual Studio 2010 SDK:

  http://www.microsoft.com/en-us/download/details.aspx?id=2680

• For the Microsoft Visual Studio 2010 SP1 SDK:

  http://www.microsoft.com/en-us/download/details.aspx?id=21835

• For the Microsoft Visual Studio 2012 SDK:

  http://www.microsoft.com/en-us/download/details.aspx?id=30668

• For the Microsoft Visual Studio 2013 SDK:

  http://www.microsoft.com/en-us/download/details.aspx?id=40758

If the Microsoft Visual Studio SDK is missing when you begin the installation, a dialog box opens to tell you so. You can choose to ignore this dialog box.

Configuring BIDS or SSDT-BI integration

The Vertica Client Drivers and Tools for Windows installer provides BIDS (Visual Studio 2008) or SSDT-BI (Visual Studio 2010, 2012, 2013, or 2015) integration. To use BIDS or SSDT-BI, follow this process:

1. Install the BIDS or SSDT-BI development tool add-on for Visual Studio.

2. Verify that SQL Server is installed on the same or a different machine.

3. Verify that the SQL Server Shared Features for BIDS or SSDT-BI have been activated.

You can then develop packages using BIDS or SSDT-BI, creating your projects using SQL Server’s SSIS, SSAS, SSRS features. To use these features, you must connect to Vertica through the Vertica ADO.NET driver (for SSIS and SSRS) or the OLE DB driver (for SSAS).

For more information, see Microsoft components.

9.2.1.3 - Microsoft SQL server

Use SQL Server 2012, 2014 or 2016. The Vertica Client Drivers and Tools for Windows installer enables support for the following:

• SQL Server 2012, 2014, and 2016:

  • SQL Server Integration Services (SSIS)

  • SQL Server Reporting Services (SSRS)

  • SQL Server Analysis Services (SSAS)

• SQL Server using Visual Studio 2010, 2012, 2013, and 2015—SQL Server Data Tool - Business Intelligence (SSDT-BI)

  Note

  For SQL Server 2012, you can use either SQL Server 2012 or SQL Server 2012 SP1.

To use the enhanced Vertica .NET support, you must first install SQL Server. Then, you can install the Client Drivers and Tools for Windows. The following components must be installed on the SQL server:

9.2.2 - Download the client drivers and tools for windows

The Vertica driver downloads page has the client drivers for all currently-supported versions of Vertica. The installation package works on both 32-bit and 64-bit versions of Windows.

9.2.3 - Install or upgrade client drivers and tools for windows

You can install or upgrade the Windows client drivers and tools with the Windows installer or from the command line.

Windows installer

As Windows Administrator, double-click the Windows installer to start the installation. Follow the installer prompts as it guides you through each step of the process.

By default, the installer installs the client drivers and tools in C:\Program Files\Vertica Systems. You can change this location during installation.

Command-line

1. As Windows Administrator, open a command-line session.

2. Navigate to the folder that contains the installer.

3. Run this command:

   VerticaSetup.exe -q -install InstallFolder="C:\Program Files\Vertica Systems"
   

The client drivers and tools are silently installed in C:\Program Files\Vertica Systems.

9.2.4 - Post-installation steps for ODBC driver and vsql client

After you install the Vertica Client Drivers and Tools for Windows, there are additional steps you must take for the ODBC driver and vsql client to function correctly.

• ODBC Driver — After installing the ODBC driver, you must create a DSN to be able to connect to your Vertica database. For the procedure, see Creating an ODBC DSN for windows clients.

• vsql Client — The vsql client does not have a shortcut. Before you can start using vsql, you must add the vsql executable to the Windows PATH environment variable. The method for altering the PATH environment variable depends on the version of the Microsoft Windows operating system you are running. To start vsql and show the help list, open a command window, and type vsql -? at the command prompt. See Using vsql for windows users for important details about using vsql in a Windows console.

9.2.5 - Uninstalling, modifying, or repairing the client drivers and tools

To uninstall, modify, or repair the client drivers and tools, run the Client Drivers and Tools for Windows installer.

The installer provides three options:

Silently uninstall the client drivers and tools

1. As a Windows Administrator, open a command-line session, and change directory to the folder that contains the installer.

2. Run the command:

   VerticaSetup.exe -q -uninstall
   

The client drivers and tools are silently uninstalled.

10 - Components of the client drivers and tools on Windows

The following sections describe the components in the Client Drivers and Tools for Windows in more detail:

• The ODBC client driver for windows
• The vsql client for windows
• The Microsoft connectivity pack for windows
• The OLE DB client driver for windows
• The ADO.NET driver for windows
• The visual studio plug-in for windows

10.1 - The ODBC client driver for windows

The Vertica ODBC driver for Windows is installed as part of the Client Drivers and Tools for Windows. See the Client Drivers downloads page.

After installing the ODBC driver

After installing the ODBC driver, you must create a DSN to be able to connect to your Vertica database. For the procedure, see Creating an ODBC DSN for windows clients.

10.1.1 - ODBC driver settings on Windows

ODBC driver settings are automatically configured using the Vertica Client Drivers and Tools installer on Windows. The values for the settings are stored in the Windows registry under the path HKEY_LOCAL_MACHINE\SOFTWARE\Vertica\ODBC\Driver. It is not necessary to configure additional ODBC driver settings on Windows platforms beyond what is automatically configured by the installer. You can, however, set the ODBC driver settings using the Windows ODBC Data Source Configuration window.

See Additional parameter settings for a list of additional settings for the ODBC client driver. See Diverting ODBC log entries to ETW for information on how to send ODBC log entries to Event Tracing for Windows (ETW).

10.1.2 - Diverting ODBC log entries to ETW

On Windows clients, you can direct Vertica to send ODBC log entries to Event Tracing for Windows (ETW). Once set, ODBC log entries appear in the Windows Event Viewer. To use ETW:

• Register the driver as a Windows Event Log provider, and enable the logs.

• Activate ETW by adding a string value to your Windows Registry.

• Understand how Vertica compresses log levels for the Windows Event Viewer.

• Know where to find the logs within Event Viewer.

• Understand the meaning of the Event IDs in your log entries.

Register the ODBC driver as a windows event log provider, and enable the logs

To use ETW logging, you must register the ODBC driver as a Windows Event Log provider. You can choose to register either the 32-bit or 64-bit driver. Once you have registered the driver, you must enable the logs.

1. Open a command prompt window as Administrator, or launch the command prompt with the Run as Administrator option.

   Important

   You must have administrator privileges to successfully complete the next step.

2. Run the command wevtutil im to register either the 32-bit or 64-bit version of the driver.

   1. For the 64-bit ODBC driver, run:

      wevtutil im "c:\Program Files\Vertica Systems\ODBC64\lib\VerticaODBC64.man"
      /resourceFilePath:"c:\Program Files\Vertica Systems\ODBC64\lib\vertica_9.1_odbc_3.5.dll"
      /messageFilePath:"c:\Program Files\Vertica Systems\ODBC64\lib\vertica_9.1_odbc_3.5.dll"
      

   2. For the 32-bit ODBC driver, run:

      wevtutil im "c:\Program Files (x86)\Vertica Systems\ODBC32\lib\VerticaODBC32.man"
      /resourceFilePath:"c:\Program Files (x86)\Vertica Systems\ODBC32\lib\vertica_9.1_odbc_3.5.dll"
      /messageFilePath:"c:\Program Files (x86)\Vertica Systems\ODBC32\lib\vertica_9.1_odbc_3.5.dll"
      

3. Run the command wevtutil sl to enable the logs.

   1. For 64-bit ODBC driver logs, run:

      wevtutil sl VerticaODBC64/e:true
      

   2. For the 32-bit ODBC driver logs, run:

      wevtutil sl VerticaODBC32/e:true
      

   Note

   Should you want to later disable the logs, you can use the same wevtutil sl command, substituting /e:false in place of /e:true when you issue the statement. Alternatively, you can enable or disable logs within the Windows Event Viewer itself.

Add the string value LogType

By default, Vertica does not send ODBC log entries to ETW. To activate ETW, add the string LogType to your Windows registry, and set its value to ETW.

1. Start the registry editor by typing regedit.exe in the Windows Run command box.

2. Navigate to the correct location in the registry.

   HKEY_LOCAL_MACHINE\SOFTWARE\Vertica\ODBC\Driver
   

3. Right-click in the right pane of the Registry Editor window. Select New and then select String Value.

4. Change the name of the string value from New Value #1 to LogType.

5. Double-click the new LogType entry. When prompted for a new value, enter ETW.

6. Exit the registry editor.

ETW is off by default. When ETW is activated, you can subsequently turn it off by clearing the value ETW from the LogType string.

Event viewer log levels

The LogLevel parameter setting is described in the section, Additional parameter settings. The parameter allows you to specify a LogLevel of 0 through 6. Be aware that Vertica compresses the log levels for the Windows Event Viewer. The six levels are compressed to four in Event Viewer.

Examples:

• A LogLevel setting of 5 sends fatal errors, errors, warnings, info and debug log level entries to Event Viewer as Level 4 (Information).

• A LogLevel setting of 6 sends fatal errors, errors, warnings, debug and trace log level entries to Event Viewer as Level 4.

Where to find logs in event viewer

1. Launch the Windows Event Viewer.

2. From Event Viewer (Local), expand Applications and Services Logs.

3. Expand the folder that contains the log you want to review (for example, VerticaODBC64).

4. Select the Vertica ODBC log under the folder. Entries appear in the right pane.

Event log entry: event ID

Once you have chosen an ODBC log in Event Viewer, note the value in the Event ID field.

Each Event Log entry includes one of four Event IDs. An Event ID of 0 is informational (debug, info, and trace events), 1 is an error, 2 is a fatal event, and 3 is a warning.

10.2 - The vsql client for windows

The Vertica vsql client for Windows is installed as part of the Client Drivers and Tools for Windows.

There is no shortcut for the vsql client. Before you can start using vsql, you must add the vsql executable to the Windows PATH environment variable. The method for altering the PATH environment variable depends on the version of the Microsoft Windows operating system you are running. After you have made the change to your PATH environment variable, start a command window and type vsql -? at the command prompt to start vsql and show the help list.

For information about editing the Window PATH environment variable, see How do I set or change the PATH system variable?

For important details about using vsql in a Windows console, see Using vsql for windows users .

10.2.1 - Using vsql for windows users

Font

The default raster font does not work well with the ANSI code page. Set the console font to "Lucida Console."

Console encoding

vsql is built as a "console application." The Windows console windows use a different encoding than the rest of the system, so take care when you use 8-bit characters within vsql. If vsql detects a problematic console code page, it warns you at startup.

To change the console code page, set the code page by entering cmd.exe /c chcp 1252.

Running under cygwin

Verify that your cygwin.bat file does not include the "tty" flag. If the "tty" flag is included in your cywgin.bat file, then banners and prompts are not displayed in vsql.

To verify, enter:

set CYGWIN=binmode tty ntsec

To remove the "tty" flag, enter:

set CYGWIN=binmode ntsec

Additionally, when running under Cygwin, vsql uses Cygwin shell conventions as opposed to Windows console conventions.

Tab completion

Tab completion is a function of the shell, not vsql. Because of this, tab completion does not work the same way in Windows vsql as it does on Linux versions of vsql.

On Windows, instead of using tab-completion, press F7 to pop-up a history window of commands. You can also press F8 after typing a few letters of a command to cycle through commands in the history buffer which begin with the same letters.

10.3 - The Microsoft connectivity pack for windows

The Vertica Microsoft Connectivity Pack for Windows provides a configuration file for you to access Microsoft Business Intelligence tools. The Connectivity Pack is installed as part of the Client Drivers and Tools for Windows.

To learn about which Microsoft components are configured with the Microsoft Connectivity Pack, see Microsoft components.

10.3.1 - Microsoft components

This section describes the Microsoft Business Intelligence components you can use with Microsoft Visual Studio and Microsoft SQL Server. After configuration, you can use these Microsoft components to develop business solutions using your Vertica server.

10.3.1.1 - Microsoft component configuration

The Vertica ADO.NET driver, the Visual Studio plug-in, and the OLE DB driver allow you to integrate your Vertica server with an environment that includes Microsoft components previously installed on your system. Additional tools are also available for integration with Microsoft SQL Server.

The available drivers provide integration with the following Microsoft components:

• Business Intelligence Development Studio (BIDS) for Visual Studio 2008 for use with SQL Server 2012. BIDS is a client-based application used to develop business intelligence solutions based on the Microsoft Visual Studio development environment. It includes additional project types specific to SQL Server Business Intelligence. As a developer, you can use BIDS to develop business solutions.

• SQL Server Data Tool - Business Intelligence (SSDT-BI) for Visual Studio 2008/2010/2012/2013/2015 for use with SQL Server 2012, 2014, and 2016. SSDT-BI replaces BIDS for Visual Studio 2008, 2010, 2012, 2013, and 2015. It serves the same purpose as BIDS, providing a development environment for developing business intelligence solutions.

• SQL Server Analysis Services (SSAS) for SQL Server 2012, 2014, and 2016. Use SSAS for OLAP and data mining, while using Vertica as the source for cube creation.

• SQL Server Integration Services (SSIS) for SQL Server 2012, 2014, and 2016. SSIS provides SQL Server Type Mappings to map data types between Vertica and SQL Server. Use SSIS for data migration, data integration and workflow, and ETL.

The following figure displays the relationship between Microsoft components and Vertica dependencies.

10.3.1.2 - BIDS and SSDT-BI

Business Intelligence Development Studio (BIDS) is available in Microsoft Visual Studio 2008 with additional project types that are specific to SQL Server business intelligence. BIDS is the primary environment that you use to develop business solutions that include Analysis Services, Integration Services, and Reporting Services projects.

SQL Server Data Tool - Business Intelligence (SSDT-BI) replaces BIDS for Visual Studio 2010, 2012, 2013, and 2015. It serves the same purpose as BIDS, providing a development environment for developing business solutions.

Both BIDS and SSDT-BI are client-based applications that include additional project types specific to SQL Server Business Intelligence.

You can use the Visual Studio Shell Integration plug-in to browse a database from within the Visual Studio Server Explorer. This capability allows you to work outside of BIDS or SSDT-BI development to perform tasks, such as listing tables or inserting data. When you use Visual Studio in BIDS or SSDT-BI mode, you can develop business solutions using the data in your Vertica database. For example, you can create cubes or open tables.

Microsoft does not support the following configurations:

• You cannot use Microsoft Visual Studio 2008 with BIDS development to create a SQL Server 2012 Business Intelligence solution.

• You cannot use Microsoft Visual Studio 2010/2012/2013/2015 with SSDT-BI development to create a SQL Server 2008 Business Intelligence solution.

10.3.1.3 - SQL server analysis services (SSAS) support

BIDS or SSDT-BI includes the Analysis Services project for developing online analytical processing (OLAP) for business intelligence applications. This project type includes templates for:

• Cubes

• Dimensions

• Data sources

• Data source views

It also provides the tools for working with these objects.

You can find the OLE DB connection properties in OLE DB connection properties.

10.3.1.4 - SQL server integration services (SSIS) support

BIDS or SSDT-BI includes the Integration Services project for developing ETL solutions. This project type includes templates for:

• Packages

• Data sources

• Data source views

It also provides the tools for working with these objects.

You can find support for using Vertica as a data source and target from both SSIS and the import/export wizard. You must install mapping files specific to Vertica on the Integration Server and BIDS or SSDT-BI workstation to enable this capability. The Vertica Client Drivers and Tools for Windows installs these mapping files as the "SQL Server Type Mappings" component(s) in both 32-bit and 64-bit versions.

10.3.1.5 - SQL server reporting services (SSRS) support

BIDS or SSDT-BI includes Report projects for developing reporting solutions.

You can use Vertica as a data source for Reporting Services. The installer implements various configuration file modifications to enable this capability on both the BIDS or SSDT-BI workstation and the Reporting Services server.

10.3.2 - Compatibility issues and limitations

This section lists compatibility issues and limitations for integrating the Microsoft Connectivity Pack with Microsoft Visual Studio and Microsoft SQL Server.

10.3.2.1 - BIDS and SSDT-BI limitations

BIDS and SSDT-BI are 32-bit development environments for Analysis Services, Integration Services, and Reporting Services projects. They are not designed to run on the Itanium 64-bit architecture and thus are not installed on Itanium servers.

10.3.2.2 - SSAS limitations

• The SSAS Tabular Model is not supported.

• If, after installing the Vertica OLE DB driver, an SSAS cube build fails, restart the SSAS service.

10.3.2.3 - SSIS data type limitations

The following sections cover data type limitations when using SQL Server Integration Services (SSIS).

Time data transfer

When transferring time data, SSIS uses the TimeSpan data type that supports precision greater than six digits. The Vertica ADO.NET driver translates TimeSpan as an Interval data type that supports up to six digits. The Interval type is not converted to the TimeSpan type during transfer. As a result, if the time value has a precision of more than six digits, the data is truncated, not rounded.

For information on ADO.NET data types, refer to ADO.NET data types.

DATE and DATETIME precision

To function without errors, DATE and DATETIME have a range from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.999999.

In SSIS, the DATETIME type (DT_TIMESTAMP) supports only a scale up to three decimal places for seconds. Any decimal places after that are automatically discarded. You can perform derived column transformations only on DATETIME values between January 1, 1753 through December 31, 9999.

Numeric precision

The maximum and minimum decimal allowed is:

• Max: +79,228,162,514,264,337,593,543,950,335

• Min: -79,228,162,514,264,337,593,543,950,335

For example, if the scale is 16, the range of values is:

+/- 7,922,816,251,426.4337593543950335

The valid scale range is any number that is smaller than 29 and greater than 38. Using a scale between 29 and 38 does not generate an error.

See: http://msdn.microsoft.com/en-us/library/system.decimal.maxvalue.aspx

Unsupported floating point values

SQL Server does not support NaN, Infinity, or –Infinity values. These values are supported when you use SSIS to transfer between Vertica instances, but they are not supported with a SQL Server Destination.

String Conversion

The CHAR and VARCHAR data types used in SSIS are DT_WSTR, with a maximum length of 4000 characters.

In SSIS, Vertica strings are converted to Unicode strings in SSIS to handle multi-lingual data. You can convert these strings to ASCII using a Data Conversion Task.

Scale

Whenever you use a scale greater than 38, SSIS replaces it with a value of 4.

Interval conversion

SSIS does not support interval types. It converts them to TIME and strips off the day component. Any package that has interval types greater than a day returns incorrect results.

Data mapping issues with SQL server import and export wizard

When you create an Integrated Services package (SSIS) using the SQL Server Import and Export Wizard, certain data types do not automatically map correctly. A mapping issue can occur when you use the wizard with:

• SQL Server Native OLE DB Provider for SQL Server 2008 or 2012

• SQL Server Native Client 10.0/11.0 Provider for SQL Server 2010/2012

To avoid this issue, manually change the type mappings with BIDS or SSDT-BI.

Data transfer failures

When using an Integrated Services package (SSIS) with the SQL Server OLE DB Provider for SQL Server 2008 or 2012, certain data type transfers can fail when transferring from Vertica to SQL Server. To avoid this issue, use either BIDS or SSDT-BI when transferring data.

Batch insert of VARBINARY/LONG VARBINARY data types

Sometimes, one row of a batch insert of VARBINARY or LONG VARBINARY data types exceeds the data type limit:

• VARBINARY: 65 KB

• LONG VARBINARY: 32 MB

In such cases, all rows are rejected, rather than just the one row whose length exceeds the type limit. The batch insert fails with the message "row(s) are rejected".

To avoid this issue, use a predicate to filter out rows from the source that do not fit into the receiving database.

Boolean queries in SQL server query designer

When issuing a Boolean query in SQL Server Query Designer, you must enclose Boolean column values in quotes. Otherwise, you receive a SQL execution error (for example, someboolean = 'true').

10.3.2.4 - SSRS limitations

Data Connection Wizard Workaround

The SSRS Report Wizard provides a data connection wizard. After you select the wizard and enter all the connection information, the OK button is disabled. You cannot save your work and continue. The workaround is to not use the wizard and to use the following panel instead:

Report Wizard - Query Designer

Vertica uses the Report Wizard's Generic Query Designer. Other data sources use a Graphical Query Designer that supports building queries visually. The Graphical Query Designer is a part of a package called Visual Data Tools (VDT). The Graphical Query Designer works only with Generic OLE DB providers and the built-in providers. You cannot use it with the Vertica Data Provider.

Report Builder

Report Builder is a web-based report design tool. It does not support creating reports using custom data extensions, so you cannot use it with Vertica. When you create a report using Report Builder, existing Vertica data sources appear in the list of available data sources. However, choosing a Vertica data source causes an error.

Schema Name Not Automatically Provided when Mapping Vertica Destination

Currently, when you map a Vertica destination, the schema name is not automatically provided. You must enter it manually or pick it from the drop-down menu as follows:

10.4 - The OLE DB client driver for windows

The Vertica OLE DB driver for Windows is installed as part of the Client Drivers and Tools for Windows. See the Client Drivers downloads page.

The values for the OLE DB driver's settings are stored in the Windows registry under the path HKEY_LOCAL_MACHINE\SOFTWARE\Vertica\OLEDB\Driver.

For information on how the OLE DB driver integrates with Microsoft components previously installed on your system, see Microsoft component configuration.

10.4.1 - OLE DB connection properties

Use the Connection Manager to set the OLE DB connection string properties, which define your connection. You access the Connection Manager from within Visual Studio.

These connection parameters appear on the Connection page.

The All page from the Connection Manager dialog box includes all possible connection string properties for the provider.

The table that follows lists the connection parameters for the All page.

For OLE DB properties information specific to Microsoft, see the Microsoft documentation OLE DB Properties.

10.4.2 - Diverting OLE DB log entries to ETW

On Windows clients, you can direct Vertica to send OLE DB log entries to Event Tracing for Windows (ETW). Once set, OLE DB log entries appear in the Windows Event Viewer. To use ETW:

• Register the driver as a Windows Event Log provider, and enable the logs.

• Activate ETW by adding a string value to your Windows Registry.

• Understand how Vertica compresses log levels for the Windows Event Viewer.

• Know where to find the logs within Event Viewer.

• Understand the meaning of the Event IDs in your log entries.

Register the OLE DB driver as a windows event log provider, and enable the logs

To use ETW logging, you must register the OLE DB driver as a Windows Event Log provider. You can choose to register either the 32-bit or 64-bit driver. Once you have registered the driver, you must enable the logs.

1. Open a command prompt window as Administrator, or launch the command prompt with the Run as Administrator option.

   Important

   You must have administrator privileges to successfully complete the next step.

2. Run the command wevtutil im to register either the 32-bit or 64-bit version of the driver.

   1. For the 64-bit OLE DB driver, run:

      wevtutil im "c:\Program Files\Vertica Systems\OLEDB64\lib\VerticaOLEDB64.man"
      /resourceFilePath:"c:\Program Files\Vertica Systems\OLEDB64\lib\vertica_8.1_oledb.dll"
      /messageFilePath:"c:\Program Files\Vertica Systems\OLEDB64\lib\vertica_8.1_oledb.dll"
      

   2. For the 32-bit OLE DB driver, run:

      wevtutil im "c:\Program Files (x86)\Vertica Systems\OLEDB32\lib\VerticaOLEDB32.man"
      /resourceFilePath:"c:\Program Files (x86)\Vertica Systems\OLEDB32\lib\vertica_8.1_oledb.dll"
      /messageFilePath:"c:\Program Files (x86)\Vertica Systems\OLEDB32\lib\vertica_8.1_oledb.dll"
      

3. Run the command wevtutil sl to enable the logs.

   1. For 64-bit OLE DB driver logs, run:

      wevtutil sl VerticaOLEDB64/e:true
      

   2. For the 32-bit ODBC driver logs, run:

      wevtutil sl VerticaOLEDB32/e:true
      

   Note

   Should you want to later disable the logs, you can use the same wevtutil sl command, substituting /e:false in place of /e:true when you issue the statement. Alternatively, you can enable or disable logs within the Windows Event Viewer itself.

Add the string value LogType

By default, Vertica does not send OLE DB log entries to ETW. To activate ETW, add the string LogType to your Windows registry, and set its value to ETW.

1. Start the registry editor by typing regedit.exe in the Windows Run command box.

2. Navigate to the correct location in the registry.

   HKEY_LOCAL_MACHINE\SOFTWARE\Vertica\OLEDB\Driver
   

3. Right-click in the right pane of the Registry Editor window. Select New and then select String Value.

4. Change the name of the string value from New Value #1 to LogType.

5. Double-click the new LogType entry. When prompted for a new value, enter ETW.

6. Exit the registry editor.

ETW is off by default. When ETW is activated, you can subsequently turn it off by clearing the value ETW from the LogType string.

Event viewer log levels

The LogLevel parameter setting is described in the section, Additional parameter settings. The parameter allows you to specify a LogLevel of 0 through 6. Be aware that Vertica compresses the log levels for the Windows Event Viewer. The six levels are compressed to four in Event Viewer.

Examples:

• A LogLevel setting of 5 sends fatal errors, errors, warnings, info and debug log level entries to Event Viewer as Level 4 (Information).

• A LogLevel setting of 6 sends fatal errors, errors, warnings, debug and trace log level entries to Event Viewer as Level 4.

Where to find logs in event viewer

1. Launch the Windows Event Viewer.

2. From Event Viewer (Local), expand Applications and Services Logs.

3. Expand the folder that contains the log you want to review (for example, VerticaOLEDB64).

4. Select the Vertica OLE DB log under the folder. Entries appear in the right pane.

Event log entry: event ID

Once you have chosen an OLE DB log in Event Viewer, note the value in the Event ID field.

Each Event Log entry includes one of four Event IDs. An Event ID of 0 is informational (debug, info, and trace events), 1 is an error, 2 is a fatal event, and 3 is a warning.

10.5 - The ADO.NET driver for windows

The Vertica ADO.NET driver for Windows is installed as part of the Client Drivers and Tools for Windows.

The ADO.NET driver is installed in the ADO.NET folder of the installation folder. The driver is also installed into the Windows Global Assembly Cache (GAC).

For information on how the ADO.NET driver integrates with Microsoft components previously installed on your system, see Microsoft components.

10.6 - The visual studio plug-in for windows

The Visual Studio plug-in for Windows is installed as part of the Client Drivers and Tools for Windows.

For information on how the Visual Studio plug-in integrates with Microsoft components previously installed on your system, see Microsoft components.

10.6.1 - Visual studio limitations

Visual Studio 2012 May Require Update 3

You may need to install update 3 to Visual Studio 2012 if:

• You launch Server Explorer to view and work with your Vertica server, but the Vertica data source is not visible.

• You create a SSAS cube, connect to Vertica, and find either an empty list of tables or tables not functioning correctly.

This issue does not occur for other versions of Visual Studio supported by Vertica.

Results Viewer Limited to 655 Columns

The Visual Studio results viewer cannot execute a query that includes more than 655 columns. If a table includes more than 655 columns, select specific columns (up to 655 total) rather than selecting all columns.

Manually Refresh Settings for Visual Studio

If, after installing the Visual Studio plug-in, you do not see Vertica listed as a data provider, manually refresh.

To do so, run devenv.exe/setup, which you can find in the Visual Studio installation folder.

SQL Pane Issues

• ALTER TABLE or CREATE TABLE

  You use Visual Studio 2008, 2010, 2012, 2013, or 2015 and issue the ALTER TABLE or CREATE TABLE statement in the SQL pane. However, a message displays telling you that the statement is not supported. To resolve the error, click Continue, and the query executes.

• Queries with Semicolons

  You use Visual Studio 2008, 2010, 2012, 2013, or 2015 and execute a SQL query in the SQL pane. If you include a semicolon (;) with your query, the query executes, but the result returned cannot be edited. To avoid this issue, enter the same query in the SQL pane without the semicolon.

• Quoting Boolean Values

  You use Visual Studio 2008, 2010, 2012, 2013, or 2015 to connect to the Vertica database and execute a SQL query in the SQL pane. When attempting to insert a value into a Boolean column without putting quotes around the value, subsequent execution of the SQL statement returns an error. To work around this issue, include quotes.

Uninstalling Client Drivers and Tools for Windows Error

There is a scenario where an uninstall of the Client Drivers and Tools for Windows package fails with a message that the .NET framework is required. What follows is the scenario that causes this issue.

1. You Install the Client Drivers and Tools for Windows.
2. You then install Visual Studio 2010 or 2012, which includes installation of the .NET framework 4.0 or 4.5.
3. You uninstall the .NET framework using the Windows Control Panel.
4. You then attempt to uninstall the Client Drivers and Tools for Windows. The uninstall fails with the message that .NET framework is required.

Perform the following to correct this issue:

1. Reinstall the .NET framework 4.0 or 4.5 manually, using the Windows Control Panel.
2. Uninstall the Client Drivers and Tools for Windows.

11 - Installing the client drivers on Mac OS X

This section details how to install the client drivers on Mac OS X.

11.1 - Installing the JDBC driver on Mac OS X

To install the Vertica JDBC driver on your Mac OS X client system, download the JDBC driver .jar file from the Vertica driver downloads page.

Ensuring Java can find the JDBC driver

In order for your Java client application to use the Vertica JDBC driver, the Java interpreter needs to be able to find its library file. Choose one of these methods to tell the Java interpreter where to look for the library:

• Copy the JDBC .jar file you downloaded to either the system-wide Java Extensions folder (/Library/Java/Extensions) or your user Java Extensions folder (/Users/username/Library/Java/Extensions).

• Add the directory containing the JDBC .jar file to the CLASSPATH environment variable (see Modifying the Java CLASSPATH).

• Specify the directory containing the JDBC .jar using the -cp argument in the Java command line you use to start your Java command line.

11.2 - Installing the ODBC driver on Mac OS X

You can download the Vertica ODBC driver for Mac OS X as a .pkg file from the Vertica driver downloads page. You can run the installer as a regular Mac OS X installer or silently.

The installer is designed to be used with the standard iODBC Driver Manager included in Mac OS X. While Mac OS X ships with the iODBC Driver Manager already installed, you may choose to download the most recent version of the driver at the iODBC.org website.

By default, the installer installs the driver in the following location: /Library/Vertica/ODBC/lib/libverticaodbc.dylib. The installer also automatically registers a driver named "Vertica" with the iODBC Driver Manager.

To use the unixODBC Driver Manager instead of Apple's iODBC Driver Manager, see the unixODBC.org website.

Before you download the driver

If you installed a previous version of the Vertica ODBC driver for Mac OS X, your system might already have a registered driver named "Vertica." In this case, you must remove or rename the older version of the driver before installing the Vertica ODBC driver .pkg file.

To have multiple versions of the driver installed on your system at the same time, you must rename the currently installed version of the driver to something other than "Vertica."

11.2.1 - Download the driver

Download the Macintosh OS X driver package from the Vertica driver downloads page.

11.2.2 - Install the Mac OS X ODBC driver

As a Mac OS X Administrator, double-click the installer to start the installation. Follow the prompts as the wizard guides you through each step of the process.

11.2.3 - Silently install the Mac OS X ODBC driver

1. Log into the client Mac in one of two ways:

   • As an administrator account, if you are installing the driver for system-wide use

   • As the user who needs to use the Vertica ODBC driver

2. Open a terminal window. In the Finder, click Applications > Utilities > Terminal.

3. Install the .pkg file containing the ODBC driver using the command:

   sudo installer -pkg ~/Downloads/vertica-odbc11.1.xpkg -target /
   

   In the preceding .pkg command, change the path to that of the downloaded file, if:

   • You downloaded the driver .pkg file to a directory other than your Downloads directory.
   • You downloaded the driver using another user account.

11.2.4 - Uninstall the Mac OS X ODBC driver

Uninstalling the Mac OS X ODBC Client-Driver does not remove any existing DSNs associated with the driver.

To uninstall:

1. Open a terminal window.

2. Enter the command:

   sudo /Library/Vertica/ODBC/bin/Uninstall
   

11.2.5 - Upgrade or downgrade the Mac OS X ODBC driver

All installations of the Vertica ODBC driver for Mac OS X are uniquely identified by a package ID and version number. The package ID does not change between versions, but the version number does. If you attempt multiple installations of the same version of the driver, a name collision error occurs. Therefore, multiple installations of the same version of the driver cannot coexist on a single operating system.

• Upgrading—Newly installed versions of the Vertica ODBC driver for Mac OS X automatically upgrade the relevant driver system settings. Any DSNs associated with a previous version of the driver are not affected, except that they begin using the newer version of the driver.

• Downgrading—Run the uninstall script to remove the current version of the Vertica ODBC driver for Mac OS X. Complete this step before installing an older driver version.

11.2.6 - ODBC driver settings on Mac OS X

ODBC driver settings are automatically configured using the Vertica ODBC driver installer on Mac OS X. It is not necessary to configure additional ODBC driver settings on Mac OS X platforms beyond what is automatically configured by the installer. You can, however, set the ODBC driver settings by editing the VERTICAINI environment variable in each user's ~/.MacOSX/environment.plist file. See the Environment Variables entry in the Apple Developer's Library for more information.

See Additional parameter settings for a list of the additional settings.