This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Creating an ODBC DSN for Linux

You define DSN on Linux and other UNIX-like platforms in a text file.

You define DSN on Linux and other UNIX-like platforms in a text file. Your client's driver manager reads this file to determine how to connect to your Vertica database. The driver manager usually looks for the DSN definitions in two places:

  • /etc/odbc.ini

  • ~/.odbc.ini (a file named .odbc.ini in the user's home directory)

Users must be able to read the odbc.ini file in order to use it to connect to the database. If you use a global odbc.ini file, consider creating a UNIX group with read access to the file. Then, add the users who need to use the DSN to this group.

The structure of these files is the same—only their location differs. If both files are present, the ~/.odbc.ini file usually overrides the system-wide /etc/odbc.ini file.

odbc.ini file structure

The odbc.ini is a text file that contains two types of lines:

  • Section definitions, which are text strings enclosed in square brackets.

  • Parameter definitions, which contain a parameter name, an equals sign (=), and then the parameter's value.

The first section of the file is always named [ODBC Data Sources], and contains a list of all the DSNs that the odbc.ini file defines. The parameters in this section are the names of the DSNs, which appear as section definitions later in the file. The value is a text description of the DSN and has no function. For example, an odbc.ini file that defines a single DSN named Vertica DSN could have this ODBC Data Sources section:

[ODBC Data Sources]
VerticaDSN = "vmartdb"

Appearing after the ODBC data sources section are sections that define each DSN. The name of a DSN section must match one of the names defined in the ODBC Data Sources section.

Configuring the odbc.ini file:

To create or edit the DSN definition file:

  1. Using the text editor of your choice, open odbc.ini or ~/.odbc.ini.

  2. Create an ODBC Data Sources section and define a parameter:

    • Whose name is the name of the DSN you want to create

    • Whose value is a description of the DSN

    For example, to create a DSN named VMart, you would enter:

    [ODBC Data Sources]
VMart = "VMart database on Vertica"

  3. Create a section whose name matches the DSN name you defined in step 2. In this section, you add parameters that define the DSN's settings. The most commonly-defined parameters are:

    • Description – Additional information about the data source.

    • Driver – The location and designation of the Vertica ODBC driver, or the name of a driver defined in the odbcinst.ini file (see below). For future compatibility, use the name of the symbolic link in the library directory, rather than the library file:

      • ( /opt/vertica/lib, on 32-bit clients

      • /opt/vertica/lib64, on 64-bit clients

      For example, the symbolic link for the 64-bit ODBC driver library is:

      /opt/vertica/lib64/libverticaodbc.so

      The symbolic link always points to the most up-to-date version of the Vertica client ODBC library. Use this link so that you do not need to update all of your DSNs when you update your client drivers.

    • Database – The name of the database running on the server. This example uses vmartdb for the vmartdb.

    • ServerName — The name of the server where Vertica is installed. Use localhost if Vertica is installed on the same machine.

      You can provide an IPv4 address, IPv6 address, or host name.

      In mixed IPv4/IPv6 networks, the DNS server configuration determines which IP version address is sent first. Use the PreferredAddressFamily option to force the connection to use either IPv4 or IPv6.

    • UID — Either the database superuser (same name as database administrator account) or a user that the superuser has created and granted privileges. This example uses the user name dbadmin.

    • PWD —The password for the specified user name. This example leaves the password field blank.

    • Port — The port number on which Vertica listens for ODBC connections. For example, 5433.

    • ConnSettings — Can contain SQL commands separated by a semicolon. These commands can be run immediately after connecting to the server.

    • SSLKeyFile — The file path and name of the client's private key. This file can reside anywhere on the system.

    • SSLCertFile —The file path and name of the client's public certificate. This file can reside anywhere on the system.

    • Locale — The default locale used for the session. By default, the locale for the database is: en_US@collation=binary (English as in the United States of America). Specify the locale as an ICU Locale. See the ICU User Guide (http://userguide.icu-project.org/locale) for a complete list of parameters that can be used to specify a locale.

    • PreferredAddressFamily:

      The IP version to use if the client and server have both IPv4 and IPv6 addresses and you have provided a host name, one of the following:

      • ipv4: Connect to the server using IPv4.

      • ipv6: Connect to the server using IPv6.

      • none: Use the IP address provided by the DNS server.

For example:

[VMart]
Description = Vmart Database
Driver = /opt/vertica/lib64/libverticaodbc.so
Database = vmartdb
Servername = host01
UID = dbadmin
PWD =
Port = 5433
ConnSettings =
AutoCommit = 0
SSLKeyFile = /home/dbadmin/client.key
SSLCertFile = /home/dbadmin/client.crt
Locale = en_US@collation=binary

See ODBC DSN connection properties for a complete list of parameters including Vertica-specific ones.

Using an odbcinst.ini file

Instead of giving the path of the ODBC driver library in your DSN definitions, you can use the name of a driver defined in the odbcinst.ini file. This method is useful method if you have many DSNs and often need to update them to point to new driver libraries. It also allows you to set some additional ODBC parameters, such as the threading model.

Just as in the odbc.ini file, odbcinst.ini has sections. Each section defines an ODBC driver that can be referenced in the odbc.ini files.

In a section, you can define the following parameters:

  • Description— Additional information about the data source.

  • Driver— The location and designation of the Vertica ODBC driver, such as /opt/vertica/lib64/libverticaodbc.so

For example:

[Vertica]
Description = Vertica ODBC Driver
Driver = /opt/vertica/lib64/libverticaodbc.so

Then, in your odbc.ini file, use the name of the section you created in the odbcinst.ini file that describes the driver you want to use. For example:

[VMart]
Description = Vertica Vmart database
Driver = Vertica

If you are using the unixODBC driver manager, you should also add an ODBC section to override its standard threading settings. By default, unixODBC serializes all SQL calls through ODBC, which prevents multiple parallel loads. To change this default behavior, add the following to your odbcinst.ini file:

[ODBC]
Threading = 1

Configuring additional ODBC settings

On Linux and UNIX systems, you need to configure some additional driver settings before you can use your DSN. See ODBC driver settings for details.

1 - Testing an ODBC DSN using isql

The unixODBC driver manager includes a utility named isql, which is a simple ODBC command-line client.

The unixODBC driver manager includes a utility named isql, which is a simple ODBC command-line client. It lets you to connect to a DSN to send commands and receive results, similarly to vsql.

To use isql to test a DSN connection:

  1. Run the following command:

    $ isql –v DSNname

    Where DSNname is the name of the DSN you created.

    A connection message and a SQL prompt display. If they do not, you could have a configuration problem or you could be using the wrong user name or password.

  2. Try a simple SQL statement. For example:

    SQL> SELECT table_name FROM tables;

    The isql tool returns the results of your SQL statement.