Install the ODBC Driver on Windows¶

Install the ODBC driver in the machine where the client application runs. To do this, follow these steps:

1. Obtain the file denodo-vdp-odbcdriver-windows.zip. To do this:

   1. Copy it from the installation (<DENODO_HOME>/tools/client-drivers/odbc/denodo-vdp-odbcdriver-windows.zip).

   2. Or download it from the ODBC page of the Denodo Community.

      On this page, download the driver for Windows (the name ends up with -win). Make sure you select a version of the package that is not newer than the Denodo server you are going to connect. For example, if your Denodo server has the update 7.0 20181011, do not download the package denodo-vdp-odbcdriver-7.0-update-20190312-win because it is newer.

2. Extract the contents of this file and copy them to the host where the client application runs.

   The following file to install the ODBC driver can be found under the “msi” folder:

   • DenodoODBC_x64.msi.

Set Up a DSN on Windows¶

After installing the Denodo ODBC driver on the host where the client application runs, you need to register a new data source, also known as DSN, that points to the Denodo server.

Follow these steps to do this:

1. Open the ODBC Data Sources applet of the Windows Administrative Tools (Control Panel).

   To open it, press the Windows key and enter Set up ODBC data source.

2. In the tab User DSN, click Add.

   When creating the DSN, do it with the same user account with you run the application that will connect to Denodo. If that is not possible, create a “System DSN”. Only local administrators of the computer can register a system DSN. If an administrator cannot create the DSN, create a user DSN.

   The difference between a “System DSN” and a “User DSN” is that the “User DSN” can only be used by the current user and the “System DSN” can be used by all the users of the system.

3. Select the DenodoODBC Unicode driver (not DenodoODBC ANSI) and click Finish.

4. In the configuration dialog, fill in the following information:

   1. Database: database in Virtual DataPort. E.g. admin. If the name of the database contains non-ASCII characters, they have to be URL-encoded. For example, if the name of the database is “テスト”, enter “%E3%83%86%E3%82%B9%E3%83%88”.

   2. Server and Port: host name and port of the server that runs Virtual DataPort. The default ODBC port is 9996.

   3. User Name and Password: credentials of the Virtual DataPort user.

      Note

      Some tools may ask you for a DSN name and then ask you for an user and password instead of using those defined in the DSN. you may not be able to connect if the password contains the following four characters: %, {, } and +. If so, you may need to escape these characters: % as %25, + as %2B, { as %7B and } as %7D.

      If Kerberos authentication is selected on the Denodo database you are connecting to, the driver will ignore these credentials. Instead, it will obtain a Kerberos ticket from the system cache to use Kerberos authentication. In this case, it is not possible to connect using standard user & password authentication. On the other hand, selecting Kerberos authentication at database level is no longer required to use Kerberos in ODBC connections, you can enable Kerberos authentication on the client configuration on Page 3 of the data source configuration in the DSN.

      Note

      To be able to use Kerberos authentication, the configuration of the DSN has to meet these conditions:

      1. The client has to belong to the Windows domain. The reason is that the ODBC driver requests the Kerberos ticket to the ticket cache.

      2. In the Server field, 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 is HTTP/denodo-prod.subnet1.contoso.com@CONTOSO.COM, enter denodo-prod.subnet1.contoso.com.

   4. If SSL is enabled on the Virtual DataPort server, in the SSL Mode list, select require.

Denodo ODBC driver: configuration dialog¶

5. Click Datasource to open the Advanced options dialog.

   The default options (shown in the screenshot below) are correct, but consider the following:

   • If you want to log all the requests received by this DSN, select CommLog (C:\denodoODBC_xxx.log) and MyLog (C:\mylog_xxx.log).

     Important

     In a production environment, we strongly recommend clearing these check boxes because logging all the requests impacts the performance of the driver and the log file may grow to a very large size.

   • In “Unknown sizes”, select Maximum. See more about what this means in the section Maximum Length of Text Values.

   • As “Use Declare/Fetch” is selected, 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. Chunk Size establishes the number of rows of each block. The “Chunk size” of the DSN is equivalent to the “Fetch size” of the JDBC connections.

Denodo ODBC driver: advanced configuration (Page 1)¶

6. Click Page 2:

   The default options (shown in the screenshot below) are correct.

   In addition, in the area “Level of rollback on errors”, select Transaction.

Denodo ODBC driver: advanced configuration (Page 2)¶

• In the Compression box you can enable compression of the data sent to the driver from Denodo server using Zstandard algorithm.

  It is recommended to activate this setting when the client application and the Denodo server are connected through a WAN. In these cases you can expect significant performance improvements when the client application reads moderate or large data volumes (e.g. 100s of thousands or millions of rows). Although the exact numbers can vary significantly according to several factors, in this type of scenario it’s common to achieve improvements around 25%-50%.

  Notice however that typically compression does not add any performance benefit when the client and server application connect through a LAN. Actually, in that case performance can be worse. That is the reason the feature is disabled by default.

• In the Automatic reconnect box you can define how the driver will behave before executing a query when a connection is lost.

  1. Retry attempts: number of reconnection retries when the the driver identifies that the connection is not in a correct state(0 means that the feature is disabled). Default value: 1.

  2. Interval (sec): number of seconds between reconnection attempts. Default value: 10.

• In the TCP KEEPALIVE setting box you can enable/disable the TCP keep alive feature. This is useful to prevent network agents like proxies or load balancers to close pooled connections at ODBC clients:

  1. disable: select this to disable this feature.

  2. idle time: after this number of seconds of inactivity, the driver sends a TCP keepalive message to Virtual DataPort. Default value: 60.

  3. interval: number of seconds after which the driver retransmits a TCP keepalive message that has not been acknowledged by Virtual DataPort. Default value: 5.

• In the Connect settings box you can change the properties of the ODBC connection established with Virtual DataPort, by entering the following:

  1. SET QUERYTIMEOUT TO <value> to change the query time out (value in milliseconds).

  2. SET i18n TO <i18n> to change the i18n of the connection.

  3. SET FORCE_DECIMAL_PROPERTIES TO true to force size and scale from decimal values to match the metadata information.

     For example, to set the default timeout of the queries to one hour, add the following:

     SET QUERYTIMEOUT TO 3600000;
     SET I18N TO us_pst;
     

     Note the ; between each statement.

     The following table describes these properties and lists its default values:

7. Click Page 3 and do the following:

   1. Set the option The use of LIBPQ library to Yes.

   2. In the User agent box, enter the name of the application that will use this DSN. We recommend setting this field in all the DSNs to Virtual DataPort because is very useful for logging.

   3. In the fields about Infrastructure it is possible to include information about the cloud infrastructure, like provider and region. To know more about cloud infrastructure’s configuration, see the section Setting Cloud Infrastructure Properties.

   4. Select Use Kerberos to enable Kerberos authentication.

   5. Usually, the default value of Krbsrvname is correct. If you enabled Kerberos authentication, this value has to match the “service class” of the Service Principal Name of the Server. For example, if in the field Server Principal of the Kerberos settings of the Server, you have HTTP/denodo1.contoso.com@CONTOSO.COM, the value of Krbsrvname has to be HTTP.

Denodo ODBC driver: advanced configuration (Page 3)¶

6. To establish the connection using OAuth authentication instead of user and password, select Enable OAuth 2.0 authentication and depending on the OAuth authentication flow you will need to provide some of the following parameter values:

Currently, the ODBC driver supports three OAuth authentication flows and different parameters must be set for each one of them, be sure to select the appropriate ones for your scenario.

1. Resource owner credentials flow (ROPC). When the client application opens a connection, the driver will request an OAuth token to the Identity Provider and will use that token to establish the connection.

   • User and password.

   • Token Endpoint.

   • Client ID.

   • Client Secret.

   • Scope.

   • Extra parameters (Optional).

   • Use id_token.

   • Check certificates.

2. Refresh token flow. The driver will use the provided access token to establish the connection. If the token is expired the driver will request a new OAuth token to the Identity Provider using the refresh token.

   • Token Endpoint.

   • Client ID.

   • Client Secret.

   • Scope.

   • Extra parameters (Optional).

   • Access token.

   • Refresh token.

   • Credentials cache (Optional).

   • Use id_token.

   • Check certificates.

3. Authorization code flow. The driver will request an access token to the Identity Provider using a web browser. It is highly recommended to set a credentials cache file to avoid open unnecessary tabs in the web browser when the client application implements some kind of connection pool.

   • Authorization Endpoint.

   • Token Endpoint.

   • Client ID.

   • Client Secret.

   • Scope.

   • Extra parameters (Optional).

   • Credentials cache (Optional).

   • Use id_token.

   • Check certificates.

   Before using OAuth to connect to Virtual DataPort, you have to enable OAuth in Virtual DataPort. Otherwise, the connections with OAuth will fail.

8. Click Ok to close the “Advanced Options” dialog.

9. Click Test to test the connection to Virtual DataPort.

10. Click Ok.

The DSN is now configured and ready to be used.

After setting up the DSN, we recommend reading the section Integration with Third-Party Applications.

If there is an error during the creation of the DSN, try logging in using an account with administrative privileges.

Install Multiple Versions of the ODBC Driver on Windows¶

Currently, the DenodoODBC .msi installers do not allow having multiple versions of the DenodoODBC ODBC driver in the same Windows machine. But the DenodoODBC ODBC driver for Windows does include a set of scripts located at denodo-vdp-odbcdriver-windows.zip\scripts\ so multiple versions can be used at the same time. Follow the README at the aforementioned path for further instructions.