Set Up a DSN on Linux and Other UNIX¶
Linux does not provide an ODBC driver manager, so you have to obtain one.
This section explains how to install and configure unixODBC.
Install UnixODBC¶
This section explains how to install the package “unixODBC”.
For Linux distributions based on RPM packaging system such as CentOS, execute this:
yum install unixODBC
For the ones based on the Debian packaging system like Ubuntu execute this:
apt-get install unixODBC
If you cannot install it using any packaging system, download it and compile it. To do this, follow these steps:
Download the latest version of the source code from http://www.unixodbc.org/download.html.
Execute the following commands to extract the source code and compile it:
tar -zxf unixODBC*.tar.gz cd unixODBC ./configure.sh make
Execute the following command:
sudo make install
Register the Denodo ODBC Driver in UnixODBC¶
Follow these steps to register the Denodo ODBC driver in the unixODBC driver manager:
Create a new file
denodoODBCDriver.template
with the content below:[DenodoODBCDriver] Description=ODBC driver for Denodo 6.0 Driver=/usr/lib/denodoodbc.so UsageCount=1
Execute the following command to register the Denodo driver in the ODBC Driver Manager:
sudo odbcinst -install -driver -file denodoODBCDriver.template
To list the ODBC drivers registered in the driver manager, execute this:
sudo odbcinst -query -driver
The result should list the new driver: DenodoODBCDriver
.
To uninstall the driver, execute:
sudo odbcinst -uninstall -driver -name DenodoODBCDriver
Configure the DSN in UnixODBC¶
To register a new DSN in unixODBC, follow these steps:
Create a file called
denodoDSN.template
with the content below:
[VDP_acme_DSN] Description = VDP connection Driver = DenodoODBCDriver Servername = <host name> Port = <Virtual DataPort ODBC port. Default is 9996> UserName = <Virtual DataPort user name> Password = <Password> Database = <Virtual DataPort database> Application_name = <name of the application that will use the DSN> Protocol = 7.4 BoolsAsChar = 0 ByteaAsLongVarBinary= 1 ConnSettings = SET QUERYTIMEOUT TO 3600000; SET I18N TO us_pst; /*krbsrvname=HTTP*/ Debug = 0 Commlog = 0 FakeOidIndex = 0 Fetch = 1000 Ksqo = 0 LFConversion = 1 Optimizer = 0 ReadOnly = 0 RowVersioning = 0 ShowOidColumn = 0 ShowSystemTables = 0 # Uncomment the "Sslmode" property if SSL is enabled in the # Sslmode = require UniqueIndex = 0 UpdatableCursors = 0 UseDeclareFetch = 1 UseServerSidePrepare= 0If the name of the database contains non-ASCII characters, they have to be URL-encoded. For example, if the name of the database is “テスト”, set the property
Database
to%E3%83%86%E3%82%B9%E3%83%88
.As the property
UseDeclareFetch
is enabled, the DSN will use DECLARE CURSOR/FETCH to handle SELECT statements. The effect is that the DSN will retrieve the rows of the result set in blocks, instead of retrieving them all at once. TheFetch
property establishes the number of rows of each block. This property is equivalent to the “Fetch size” of the JDBC connections.If you set the properties
Debug
andCommlog
to1
, the driver logs detailed ODBC information in files created in the/tmp
directory. On a production environment, we strongly recommend setting the value of this property to0
because logging all the requests impacts the performance of the driver and the log files may grow to a very large size.In the
ConnSettings
property, you can set the properties of the connection established with Virtual DataPort, by adding the following statements:
SET QUERYTIMEOUT TO <value>
to change the query time out (value in milliseconds).
SET i18n TO <i18n>
to change the i18n of the connection.For example, to set the default timeout of the queries to one hour, set the value of the property
ConnSettings
to the following:ConnSettings=SET QUERYTIMEOUT TO 3600000; SET I18N TO us_pstNote the
;
between each statement.Read Parameters of the ODBC driver and their default value to learn how these properties work, and their default value.
If you have enabled SSL in the Virtual DataPort server to secure the communications, add the following property to this configuration file:
Sslmode=require
Add the following
ConnSettings
property to connect to Virtual DataPort using Kerberos authentication:/*krbsrvname=HTTP*/
Important
This line has to be the last thing on the
ConnSettings
property.If Kerberos authentication is enabled on the Denodo database you are connecting to, the driver will ignore the value of the properties “UserName” and “Password”. Instead, it will obtain a Kerberos ticket from the system cache.
To be able to use Kerberos authentication, the configuration of the DSN has to meet these conditions:
The database that the DSN will connect to is configured with the option “ODBC/ADDO.net authentication type” set to “Kerberos”.
The client has to belong to the Windows domain. The reasons is that the ODBC driver uses the ticket cache of the operating system to obtain “ticket-granting ticket” (TGT).
In the property
Servername
, enter the fully qualified domain name of the Denodo server. That is, if in the Denodo server, in the Kerberos configuration, the field Server principal isHTTP/denodo-prod.subnet1.contoso.com@CONTOSO.COM
, enterdenodo-prod.subnet1.contoso.com
.The value of the property
application_name
should be the name of the application that will use this DSN. We recommend setting this attribute in all the connections to Virtual DataPort because is very useful for logging purposes and debugging problems caused by a particular client.
Execute this to register the new DSN:
odbcinst -install -s -l -f denodoDSN.template
The parameter
-l
registers the DSN as a “system DSN”. “System DSNs” are available to all the users.If you do not have enough privileges to register a “system DSN”, replace
-l
with-h
to register the DSN as a “user DSN” instead. If you do this, execute this command with the same user name that you execute the client application that needs to access to this DSN. The reason is that “user DSNs” are only available to the user that registers them.To list the DSNs registered in the ODBC driver manager, execute this:
odbcinst -query -s
The result should list the new DSN:
denodo_acme_DSN
.To delete a DSN from the driver manager, execute this:
odbcinst -uninstall -s -name denodo_acme_DSN
After setting up the DSN, we recommend reading the section Integration with Third-Party Applications, specially the section Increasing the Performance of the Denodo ODBC Driver.