USER MANUALS

Preparing the Connection to Databases

For each the databases from which you plan to obtain data, you need to do this:

  1. Check if the Denodo Platform includes the JDBC driver to connect to this database.

    The appendix Supported JDBC Data Sources of the Administration Guide lists the databases supported and if its driver is included or not.

    If the driver is not included, once you obtain it, copy it to the folder <DENODO_HOME>/lib-external/jdbc-drivers/database adapter - version, in the host where the Denodo server runs. For example, if you are going to connect to Teradata 15, copy the driver to <DENODO_HOME>/lib-external/jdbc-drivers/teradata-15.

  2. Obtain a service account with at least READ privileges over the tables and views that you want to query. This user account will be used at least, while importing these elements into Virtual DataPort.

  3. If this database is going to be a target of a Data Movement, this account also requires the privilege to create and delete tables, and execute INSERT, UPDATE and DELETE statements on these tables.

Cache Database

Virtual DataPort includes a Cache Engine that can store a copy of the data retrieved from the data sources, in a JDBC database. Its main goal is to increase the performance of queries.

We recommend creating a catalog or schema in the database specifically for the Cache Engine of Denodo. The Cache Engine creates many tables (one for each view of Denodo where the cache is enabled). Therefore, it is useful for these tables to be isolated from the rest of the elements of that database.

This new catalog or schema has to be created with these options:

  • Support for multi-byte characters (e.g. UTF-8). This will allow to store data that contains multi-byte characters (e.g. Japanese characters). Also, to be able to enable cache for a view whose name or fields has multi-byte characters.

  • Binary collation. In a database management system, the collation specifies how the database compares and sorts character strings. One of the main effects of the collation is the order of the results in queries that have an ORDER BY with columns that are character strings. For example, the collation determines whether or not uppercase letters and lowercase letters are treated as the same, sensitivity to accents (e.g. is “A” the same as “Á”), etc.

    With binary collations, the data is ordered according to the numeric value of each byte of the character strings.

Important

Validate with the administrator of this database that the collation of this catalog/schema is binary.

The Execution Engine of Virtual DataPort expects the data to be sorted following the rules of a binary collation. If the collation is not binary, queries that execute JOIN operations of data obtained from the cache database and from another database may return incorrect results.

Grant the following privileges to the user account that Denodo will use to connect to this database:

  • Privileges to create and drop tables on this schema.

  • Privileges to execute SELECT, INSERT, UPDATE and DELETE statements on these tables.


When you enable the Cache Engine for the first time, the default database to store the cache data is an Apache Derby database that is embedded with Denodo.

Important

We advise against using this embedded Apache Derby database. This database is provided to show case the Cache Engine and doing small projects. Instead, use an external database, especially on production environments.

See more about this in the section Cache Module of the Administration Guide. It explains how the cache module works and lists the databases that Virtual DataPort can use to store cached data.

Add feedback