System configuration considerations - Connect_ETL - 9.13

Connect ETL Vertica Load Guide

Product type
Product family
Connect > Connect (ETL, Sort, AppMod, Big Data)
Product name
Connect ETL
Connect ETL Vertica Load Guide
First publish date

Among the system configuration considerations are the settings and setup required on the local ETL servers.

Local ETL Server

On your local ETL server, do the following:
  1. Install the Vertica ODBC client driver.
  2. Create, configure, and test an ODBC data source name (DSN).

Install the Vertica ODBC Client Driver

For information on installing the Vertica ODBC client driver, see the “ODBC Prerequisites” and the “Installing the Client Drivers” sections in the Vertica Enterprise Programmers Guide.

Create, Configure, and Test an ODBC DSN

A DSN is the logical name that is used by ODBC to refer to the driver information that is required to access data from a data source. DSNs are defined in the odbc.ini file. The ODBC driver manager reads the odbc.ini file to determine how to connect to the Vertica database.

You must create and configure a data source for each Vertica database to which you establish an ODBC connection through Connect ETL:

  • On UNIX/Linux platforms, you manually create the odbc.ini text file and enter DSN connection parameters through a text editor.
  • On Windows platforms, you create the DSN through the Windows ODBC Data Source Administrator dialog and enter and test DSN connection parameter values through the displayed Vertica ODBC Data Source Configuration dialog. After you enter these values through the Windows interface, an odbc.ini file is automatically created.

The following DSN connection parameters are required for inclusion in the odbc.ini file:

  • Description – Additional data source information.
  • Driver – The path and the name of the Vertica ODBC driver library or the name of a driver defined in the odbcinst.ini file.
    Note: Specifying the name of a driver in the odbcinst.ini file is useful when you have multiple DSNs that require frequent update to point to new driver libraries. The odbcinst.ini file contains sections, each of which defines an ODBC driver that can be referenced in the odbc.ini file
  • ServerName – Name of the server where Vertica is installed.
  • Database – Name of the database running on the Vertica server.
  • Port – Port number on which Vertica listens for ODBC connections.

In addition to these required DSN connection parameters, advanced parameters are available. For additional information on setting the DSN parameters, see the “DSN Parameters” and “Setting DSN Parameters” topics in the Vertica Enterprise Programmers Guide.

The following example contains an odbc.ini file and an odbcinst.ini file:

  • The odbc.ini file contains the required VerticaDSN section. The Driver parameter contains the name of the driver defined in odbcinst.ini.
  • The odbcinst.ini file contains the name of the ODBC driver, which is referenced by odbc.ini; a description of the driver; and the location of the driver.

Example – odbc.ini that references driver specified in odbcinst.ini:

Description = Vertica database ssregressionVertica
Driver = Vertica
Host = vm-rhel51x64Vertica
ServerName = vm-rhel51x64Vertica
Database = ssregression
Port = 5433
[ODBC Driver]
Vertica = Installed 

Description = ODBC Driver for Vertica
Driver = /opt/vertica/lib64/

After you create and configure the ODBC DSN, test the connection to the DSN.

For additional information on creating, configuring, and testing an ODBC DSN for UNIX/Linux and Windows platforms, see the “Creating an ODBC DSN for Linux, Solaris, AIX, and HP-UX” and the “Creating an ODBC DSN for Windows Clients” sections and subsections in the Vertica Enterprise Programmers Guide.

For additional information on ODBC connectivity, see the “Defining ODBC data sources” and “Connecting to databases from Connect ETL” topics in the Connect ETL help.

Additional ODBC Client Driver Parameter Considerations

VERTICAINI Environment Variable

When configuring the Vertica ODBC client driver v5.1 or later on UNIX/Linux platforms, you must include the following two required DSN parameters:

  • ODBCInstLib – Required –Absolute path to the file containing the ODBC installer library.
  • ErrorMessagesPath – Required – 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.

In addition, you can include the following optional DSN parameters:

  • DriverManagerEncoding – UTF encoding standard used by the driver manager.
  • LogLevel – Severity level of the messages logged between the client and the server.
  • LogPath – Absolute path of the directory to store log files.

These additional parameters are defined in the Driver section of either the odbc.ini file or in another text file, which is typically the vertica.ini file.

Consider the following example of a Driver section in an odbc.ini or vertica.ini file that contains the additional required parameters:

The location of the odbc.ini file or vertica.ini file containing these settings must be defined in the following environment variable: VERTICAINI. The VERTICAINI environment variable must be set to the absolute path of the odbc.ini file or vertica.ini file.

Consider the following examples:

VERTICAINI environment variable that is set to the absolute path where a system-wide vertica.ini file is located:
export VERTICAINI=/etc/vertica.ini
VERTICAINI environment variable that is set to a user’s .profile, which points to the location of an individual user’s vertica.ini file:
export VERTICAINI=~/.vertica.ini

For additional information on setting the additional ODBC parameters for UNIX/Linux platforms, see the “Additional ODBC Driver Configuration Settings” topic in the Vertica Enterprise Programmers Guide.