Enabling the Support for ODBC Sources¶

Virtual DataPort can connect to Microsoft Access and other ODBC sources. However, this feature is disabled by default. This feature relies on the JDBC-ODBC bridge of Java, which has been unsupported for a long time.

If you have verified that the database you want to connect to does not have a JDBC driver, the first time you create an ODBC data source, you have to do this first:

1. Connect to the computer where Denodo is installed and log in with the user account you use to start the Virtual DataPort server.

2. Execute the script <DENODO_HOME>/setup/vdp/odbc-bridge/enableODBCBridge.

   In the future, if you want to disable this, execute <DENODO_HOME>/setup/vdp/odbc-bridge/disableODBCBridge.

3. If Virtual DataPort is running on Windows, you do not have to do anything else to enable this feature. Stop here; you can now create the ODBC data source (see below Creating an ODBC Data Source).

   If Virtual DataPort is running on Linux, continue with these steps.

   On Linux, this feature is disabled by default because, depending on the configuration of the host, Virtual DataPort could load the wrong library to connect to ODBC sources. If the wrong library is loaded, Virtual DataPort crashes. To avoid that a user creates an ODBC source that leads the Virtual DataPort server to crash, ODBC sources are disabled by default.

4. In this host, install the ODBC driver manager unixODBC. To verify if unixODBC is already installed, execute the following commands. These check if the command line utilities odbcinst and isql are already installed:

   which isql
   
   which odbcinst
   

   If both commands return the path to a file, go to step #3. If unixODBC is not installed, do this:

   1. For Linux distributions that use the RPM packaging system (e.g. Red Hat, CentOS, etc.), execute this:

      sudo yum install unixODBC
      

      For the ones based on Debian like Ubuntu execute this:

      sudo apt-get install unixODBC
      

5. Execute this:

   odbcinst -j
   

   This is to verify that unixODBC is installed correctly:

6. Check that these files exist:

   /usr/local/lib64/libodbc.so
   /usr/local/lib64/libodbcinst.so
   

   Or,

   /usr/local/lib/libodbc.so
   /usr/local/lib/libodbcinst.so
   

   The path to these files may change depending on the Linux/Unix distribution.

7. Edit the file ~/.bash_profile and add the following at the end.

   export LD_PRELOAD=/usr/local/lib/libodbc.so:/usr/local/lib/libodbcinst.so:$LD_PRELOAD
   

   With this change in the value of the variable LD_PRELOAD, you make sure that Virtual DataPort loads the files libodbc.so and libodbcinst.so provided by unixODBC and not the ones provided by other libraries.

   Note

   If the two files listed above are in lib64 and not in lib, change the line above accordingly.

8. Logout and login again from this user account. Do this to apply the changes done in .bash_profile.

9. If the Virtual DataPort server was started, stop it.

10. Start Virtual DataPort, open Design Studio and log in with an administrator account. Then, execute this command on the VQL Shell:

    -- This command enables the support for ODBC sources on Linux.
    
    SET 'com.denodo.vdb.ODBCDataSource.enable' = 'true';
    

11. To check that the configuration has been updated correctly, log out from Design Studio, log in again and do this:

    1. Create an ODBC data source.

    2. Create an ODBC base view.

    3. Query this base view.

Enabling the Support for ODBC Sources When Using an External JRE¶

Virtual DataPort provides access to ODBC data sources. If you configured this installation to use an external Java Runtime Environment (JRE) instead of the one included in the Denodo Platform, do the following to be able to connect to ODBC sources:

1. If the Denodo server runs on Windows, copy the file <DENODO_HOME>\dll\vdp\jdbc-odbc\x64\JdbcOdbc.dll to the folder <EXTERNAL_JRE_HOME>\bin.

2. If the Denodo server runs on Linux, copy the file <DENODO_HOME>/dll/vdp/jdbc-odbc/x64/libJdbcOdbc.so to the folder <EXTERNAL_JRE_HOME>/lib/amd64.

Creating an ODBC Data Source¶

To create a new ODBC data source, right-click on a database of the Server Explorer and click New > Data source > ODBC.

The Tool will display the dialog to create the data source.

“Connection” tab

ODBC data source: “Connection” tab¶

• Name. Name of the new data source.

• Database adapter. Adapter used to connect to the database. Virtual DataPort provides adapters to connect to Database Management Systems (DBMS). Each adapter knows the capabilities supported by the database: what operators and functions the database has, what clauses it supports, etc.

• Connection type. There are two types of ODBC connections:

  • DSN (Data Source Name). Use one of the existing DSNs of the system where Virtual DataPort server is installed.

  • Direct. Access to a file stored in the local file system of the Server, using one of the ODBC drivers registered on the Server. The path can use interpolation variables (see section Paths and Other Values with Interpolation Variables).

• Login. User login to access the external database.

• Password. User password to access the external database.

• Pass-through session credentials. If selected, when a client queries a view that uses this data source, Virtual DataPort will use the credentials of the user to connect to the database, instead of the values of the fields “Login” and “Password”.

  The section Importing JDBC Sources explains this option in more detail.

  Warning

  Users should be careful when enabling the cache for views that involve data sources with pass-through credentials enabled. The appendix Considerations When Configuring Data Sources with Pass-Through Credentials explains the issues that may arise.

• Transaction isolation. Isolation level of the transactions and queries executed in the database of this data source. The section Importing JDBC Sources explains the different isolation levels in more detail.

  If you select Database default, the Server uses the default isolation level of the database to execute queries.

• Click DSN Properties Configuration to add properties to the connection URI.

• Click Test Connection to check that the Server can connect to this database.

• Open the Connections Pool Configuration dialog to configure several parameters of the connections pool that Virtual DataPort will use to optimize access to the external database:

  • Initial Size: number of connections with which the pool is to be initialized. A number of connections are established and created in “idle” state, ready to be used.

  • Maximum number of active connections: maximum number of active connections to this database the pool will open. Once this limit is reached, the next request that involves sending a query to this database will have to wait until another query finishes.

    • Enter -1 to remove the limit of maximum number of active connections. There will not be a limit on the number of connections that the pool creates.

    • Enter 0 to disable the pool of connections. Therefore, for every query Virtual DataPort sends to the database, it will open a new connection to this database. Once the query finishes, it will close this connection.

  • Ping Query: SQL query used by the pool to verify the status of the connections. The query should consume as little resources in the database as possible, and the table in question should exist

  • Test Connection: if this property is checked and there is a specified ping query, each connection retrieved from the connection pool will be validated by executing the ping query.

“Read & Write” tab

ODBC data source: “Read & Write” tab¶

• Fetch size: it gives the ODBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed.

• Ignore trailing spaces: if selected, the Server removes the space characters at the end of text type values of the results returned by this data source’s views.

“Source Configuration” tab

This tab is only visible when editing a data source, not when creating a new one.

The section Data Source Configuration Properties explains the properties that can be changed in this dialog.

“Metadata” tab

In the Metadata tab, you can set the folder where the data source will be stored and provide a description.

When editing the data source, you can also change its owner by clicking .

Once the ODBC source has been imported, the process of creating base views is the same as for JDBC data sources (see section Creating Base Views from a JDBC Data Source).

The Excel ODBC driver provided by Microsoft Windows has some limitations:

• It cannot delete data from Excel spreadsheets. It can only query them and append more data.

• The Excel driver of Microsoft Excel 2003 or earlier, can execute only one query simultaneously. This can be a problem if we are combining data from the same Excel file.

  The newer versions of the Excel driver do not have this limitation.

Creating Base Views over an ODBC Data Source¶

After creating an ODBC data source, you have to create base views over it. You can later query these base views or combine its data with data from other views.

Schemas, tables and views of an ODBC source¶

To create an ODBC base view, follow these steps:

1. On the Server Explorer, double-click the ODBC data source to open it and then, click Create base view.

2. Select the check boxes beside the tables that you want to create base views from.

3. Click Create selected.

After this, each new base view will be listed in the Server Explorer in two places:

1. In the folder that the view has been created in.

2. As a child node of the data source that the base view belongs to. This node cannot be moved to other folders. It is added to the tree to provide an easy way to see the base views created from a data source.

As the process of creating ODBC base views is very similar to creating JDBC base views, see the section Creating Base Views from a JDBC Data Source for more information about this process.