Set Up a DSN on Windows¶

Make sure that you have logged in using an account with administrative privileges.

Follow these steps:

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

   Important

   If you have installed and want to use the 32-bit ODBC driver in a 64 bits Windows, instead of opening this applet, run %SystemRoot%\SysWOW64\odbcad32.exe This command opens the dialog to configure 32 bits DSNs.

2. Open the System DSN tab and click Add.

   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.

   If you create a “User DSN”, do so with the same user name you run the application that will connect to Denodo.

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.

      If Kerberos authentication is enabled 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.

      Note

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

      1. The Virtual DataPort database that the DSN connects to is configured with the option “ODBC/ADDO.net authentication type” set to “Kerberos”. Databases configured with this option will refuse the authentication with login/password.

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

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

   • Clear the check box Updatable cursors.

   • Clear the check box Server side prepare.

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

Denodo ODBC driver: advanced configuration (Page 2)¶

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

     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. Usually, the default value of Krbsrvname is correct. If you enabled Kerberos authentication on the database you are connecting to, 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.

      If you are using an old version of the Denodo ODBC driver or you are using the PostgreSQL driver, the DSN will not have this field. If that is the case and you want to use Kerberos authentication, add the following to the box Connect settings of Page 2:

      /*krbsrvname=HTTP*/

      Important

      This line has to be the last line on the “Connect settings” box.

Denodo ODBC driver: advanced configuration (Page 3)¶

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

9. Click on 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, specially the section Increasing the Performance of the Denodo ODBC Driver.