Data Source Name (DSN) Connection Properties
The following tables list the connection properties you can set in the DSNs for use with Vertica's ODBC driver.
Required Connection Properties
These connection properties are the minimum required to create a functioning DSN.
NOTE: If you use a host name (Servername) whose DNS entry resolves to multiple IP addresses, the client attempts to connect to the first IP address returned by the DNS. If a connection cannot be made to the first address, the client attempts to connect to the second, then the third, continuing until it either connects successfully or runs out of addresses.
Property | Description | Default Value |
---|---|---|
Driver |
The file path and name of the driver used. |
none |
Database |
The name of the database running on the server. |
none |
Servername |
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 You can also use the aliases "server" and "host" for this property. |
none |
UID |
The database username. |
none |
Optional Properties
These are basic properties that are optional.
Property | Description | Default Value |
---|---|---|
Port |
The port number on which Vertica listens for ODBC connections. |
5433 |
PWD |
The password for the specified user name. You may insert an empty string to leave this property blank.
|
none (login only succeeds if the user does not have a password set) |
PreferredAddressFamily |
The IP version to use if the client and server have both IPv4 and IPv6 addresses and you have provided a host name. Valid values are:
|
none |
Protocol |
Specifies the front-end protocol that the ODBC client driver uses to communicate with a previous Vertica server version. For the 8.1.x ODBC client driver to connect to Vertica server version 7.1, specify Note: The default protocol version, 3.6, applies to Vertica server versions 7.2, 8.0, and 8.1.x. You do not need to specify the protocol property when connecting from an 8.1.x ODBC client driver to server versions 7.2, 8.0, and 8.1.x. |
3.6 |
Advanced Settings
Property | Description | Default |
---|---|---|
AutoCommit |
A Boolean value that controls whether the driver automatically commits transactions after executing a DML statement. |
|
BackupServerNode |
A string containing the host name or IP address that client libraries can try to connect to if the host specified in ServerName is unreachable. 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. |
none |
ConnectionLoadBalance |
A Boolean value that indicates whether the connection can be redirected to a host in the database other than the ServerNode. This affects the connection only if the load balancing. is set to something other than "none". When the node differs from the node the client is connected to, the client disconnects and reconnects to the targeted node. See About Native Connection Load Balancing in the Administration Guide. |
false
|
ConnSettings |
A string containing SQL commands that the driver should execute immediately after connecting to the server. You can use this property to configure the connection, such as setting a schema search path. Reserved symbol: In the connection string ';' is a reserved symbol. To set multiple properties as part of ConnSettings properties, use '%3B' for ','. Also use '+' for spaces. |
none |
ConvertSquareBracketIdentifiers | Controls whether square-bracket query identifiers are converted to a double quote identifier for compatibility when making queries to a Vertica database. | false
|
DirectBatchInsert |
A Boolean value that controls where data inserted through the connection is stored. When set to true, Vertica directly inserts data into ROS containers. Otherwise, it stores data using AUTO mode. |
|
DriverStringConversions |
Controls whether the ODBC driver performs type conversions on strings sent between the ODBC driver and the database. Possible values are:
|
|
Locale |
The locale used for the session. Specify the locale as an ICU Locale. See: The ICU User Guide (http://userguide.icu-project.org/locale) for a complete list of properties that can be used to specify a locale. |
|
PromptOnNoPassword |
[Windows only] Controls whether users are prompted to enter a password, if none is supplied by the connection string or DSN used to connect to Vertica. See Prompting Windows Users for Passwords. |
|
ReadOnly |
A true or false value that controls whether the connection can read data only from Vertica. |
|
ResultBufferSize |
Size of memory buffer for the large result sets in streaming mode. A value of 0 means ResultBufferSize is turned off. This property was previously called MaxMemoryCache |
131072 (128KB) |
TransactionIsolation |
Sets the transaction isolation for the connection. Valid values are:
See Changing Transaction Isolation Levels in the Administrator's Guide for an explanation of transaction isolation. |
|
Identification
Property | Description | Default | Standard/Vertica |
---|---|---|---|
Description |
Description for the DSN entry. Required? No Insert an empty string to leave the description empty. |
none |
Standard |
Label / SessionLabel |
Sets a label for the connection on the server. This value appears in the session_id column of the V_MONITOR.SESSIONS system table. Label and SessionLabel are synonyms and can be used interchangeably. |
none |
Vertica |
Encryption
Property | Description | Default | Standard/Vertica |
---|---|---|---|
SSLMode |
Controls whether the connection to the database uses SSL encryption. Valid values follow. For descriptions of these values, refer to Configuring SSL for ODBC Clients.
|
|
Vertica |
SSLCertFile | The absolute path of the client's public certificate file. This file can reside anywhere on the system. | none | Vertica |
SSLKeyFile | The absolute path to the client's private key file. This file can reside anywhere on the system. | none | Vertica |
Third-Party Compatibility
Property | Description | Default | Standard/Vertica |
---|---|---|---|
Specifies how character column types are reported when the driver is in Unicode mode. When set to false, the ODBC driver reports the data type of character columns as WCHAR. If you set ColumnsAsChar to true, the driver identifies character column as CHAR. You typically use this setting for compatibility with some third-party clients, such as Informatica. |
|
Vertica |
|
ThreePartNaming |
A Boolean value that controls how catalog names are interpreted by the driver. When this value is false, the driver reports that catalog names are not supported. When catalog names are not supported, they cannot be used as a filter in database metadata API calls. In this case, the driver returns NULL as the catalog name in all driver metadata results. When this value is true, catalog names can be used as a filter in database metadata API calls. In this case, the driver returns the database name as the catalog name in metadata results. Some third-party applications assume a certain catalog behavior and do not work properly with the default values. Enable this option if your client software expects to get the catalog name from the database metadata and use it as part of a three-part name reference. |
|
Vertica |
EnforceBatchInsertNullConstraints |
Prevents NULL values from being loaded into columns with a NOT NULL constraint during batch inserts. When this value is set to true, batch inserts roll back when NULL values are inserted in to columns with NOT NULL constraints. When this value is set to false, batch insert behavior is unchanged. Open Text Corporation recommends only using this property with SAP Data Services as it could negatively impact database performance. |
false
|
Vertica |
Kerberos Connection Properties
Use the following properties for client authentication using Kerberos.
Property | Description | Default | Standard/Vertica |
---|---|---|---|
KerberosServiceName |
Provides the service name portion of the Vertica Kerberos principal; for example: |
vertica |
Vertica |
KerberosHostname |
Provides the instance or host name portion of the Vertica Kerberos principal; for example: |
Value specified in the servername connection string property |
Vertica |