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

Return to the regular view of this page.

OLE DB client driver

The OLE DB client driver is an interface for Microsoft Analysis Services (SSAS) and C# client applications to interact with your Vertica database.

The OLE DB client driver is an interface for C# client applications to interact with your Vertica database.

1 - Installing the OLE DB client driver

To install the Vertica OLE DB client driver:.

To install the Vertica OLE DB client driver:

  1. Download the Windows client driver installer. For details on the drivers included in this installer, see Windows client driver installer.

  2. Run the installer and follow the prompts to install the drivers.

  3. Reboot your system.

After installing the OLE DB client driver, you can configure ETW logging.

For a list of connection properties, see OLE DB connection properties.

1.1 - OLE DB connection properties

Use the Connection Manager to set the OLE DB connection string properties, which define your connection.

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.

Parameters Action
Provider Select the native OLE DB provider for the connection.
OLE DB Provider Indicates Vertica OLE DB Provider.
Server or file name Enter the server or file name.
Location Not supported.
Use Windows NT Integrated Security Not supported.
Use a specific user name and password

Enter a user name and password.

Connect with No Password:

Select the Blank password check box.

Save and Encrypt Password:

Select Allow saving password.

Initial Catalog The name of the database running on the server.

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.

Parameters Action
Extended Properties Not supported.
Locale Identifier

Indicates the Locale ID.

Default: 0

Mode

Specifies access permissions.

Default: 0

Connect Timeout

Not supported.

Default: 0

General Timeout Not supported.
File Name Not supported.
OLE DB Services Specifies which OLE DB services to enable or disable.
Password

Specifies the password for the user ID.

For no password, insert an empty string.

Persist Security Info

A security measure. When False, security sensitive-information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Default: true

User ID The database username.
Data Source

The host name or IP address of any active node in a Vertica cluster.

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.

Initial Catalog The name of the database running on the server.
Provider

The name of the OLE DB Provider to use when connecting to the Data Source.

Default: VerticaOLEDB.1

BackupServerNode

A designated host name or IP address to use if the ServerName host is unavailable. Enter as a string.

Connection attempts continue until successful or until the list of server nodes is exhausted.

Valid values: Comma-separated list of servers optionally followed by a colon and port number. For example:

server1:5033,server2:5034

ConnectionLoadBalance

A Boolean value that determines whether the connection can be redirected to a host in the database other than the ServerNode.

This parameter affects the connection only if load balancing is set to a value other than NONE. When the node differs from the node that the client is connected to, the client disconnects and reconnects to the targeted node. See About Native Connection Load Balancing in the Administration Guide.

Default: false

ConnSettings

SQL commands that the driver should execute immediately after connecting to the server. Use to configure the connection, such as setting a schema search path.

Reserved symbol:';' To set multiple parameters in this field use '%3B' for ','.

Spaces: Use '+'.

ConvertSquareBracketIdentifiers

Controls whether square-bracket query identifiers are converted to a double quote identifier for compatibility when making queries to a Vertica database.

Default: false

DirectBatchInsert Deprecated, always set to true.
KerberosHostName Provides the instance or host name portion of the Vertica Kerberos principal; for example: verticaosEXAMPLE.COM
KerberosServiceName Provides the service name portion of the Vertica Kerberos principal; for example: vertichost@EXAMPLE.COM
Label Sets a label for the connection on the server. This value appears in the session_id column of system table SESSIONS .
LogLevel Specifies the amount of information included in the log. Leave this field blank or set to 0 unless otherwise instructed by Vertica Customer Support.
LogPath The path for the log file.
Port

The port number on which Vertica listens for OLE DB connections.

Default: port 5433

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.

SSLCertFile The absolute path of the client's public certificate file. This file can reside anywhere on the system.
SSLKeyFile The absolute path to the client's private key file. This file can reside anywhere on the system.
SSLMode

Controls whether the connection to the database uses SSL encryption, one of the following:

  • require: Requires the server to use SSL. If the server cannot provide an encrypted channel, the connection fails.

  • prefer: Prefers that the server use SSL. If the server does not offer an encrypted channel, the client requests one. The first attempt is made with SSL. If that attempt fails, the second attempt is over a clear channel.

  • disable: Never connects to the server using SSL. Typically, you use this setting for troubleshooting.

Default: prefer

1.2 - Configuring OLE DB logs

The following parameters control how the OLE DB client driver logs messages between the client and server.

The following parameters control how the OLE DB client driver logs messages between the client and server. To set them, edit the keys in the Windows Registry under HKEY_LOCAL_MACHINE\SOFTWARE\Vertica\OLEDB\Driver:

  • 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.

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

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.

Registering the OLE DB driver as a windows event log provider

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.

  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
      

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, in the registry, to: HKEY_LOCAL_MACHINE\SOFTWARE\Vertica\OLEDB\Driver.

  3. Right-click in the right pane of the Registry Editor window.

  4. Select New, then select String Value.

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

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

  7. 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.

LogLevel in the windows event viewer

While LogLevel ranges from 0 through 6, this range is compressed for the Windows Event Viewer to a range of 0 through 3.

Vertica LogLevel Setting Vertica LogLevel Description Log level sent to the Windows Event Viewer Log level displayed by the Windows Event Viewer
0 (No logging) 0 (No logging)
1 Fatal Errors 1 Critical
2 Errors 2 Error
3 Warnings 3 Warning
4 Info 4 Information
5 Debug 4
6 Trace (all messages) 4

The following examples show how LogLevel is converted when displayed in the Windows Event Viewer.

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

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

Finding logs in the 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 ODBC log under the folder. Entries appear in the right pane.

  5. Note the value in the Event ID field. Each Event Log entry includes one of four Event IDs:

    • 0: Informational (debug, info, and trace events)

    • 1: Error

    • 2: Fatal event

    • 3: Warning