USER MANUALS


Servers Configuration

Configure the Virtual DataPort Servers

In the “Servers Configuration” tab you configure the Virtual DataPort servers that are listed in the login dialog.

If you add/modify a server, its URL has to contain a database, which will be used to authenticate the users.

From this section you can add, edit and delete the servers.

Servers Configuration: adding a new server

Servers Configuration: adding a new server

Note

The saved queries are stored per user and Virtual DataPort server. The servers are identified by an internal identifier, so if a server is edited, it will maintain the same queries associated to it. However, if the server is removed, the queries are removed too.

Configure the Connection Settings to the Virtual DataPort Servers

You can also configure the following global parameters of the connections created to execute queries against the Virtual DataPort Servers:

  • Query timeout: maximum time (in milliseconds) that the query will wait for the statement to be completed. If not indicated (or the value 0 is received), then it waits until execution is complete (by default 900000).

  • Chunk timeout: maximum time (in milliseconds) that the query will wait until it arrives a new chunk of results. Where this time is exceeded, Virtual DataPort returns the results extracted up to that moment. If not specified (or the value 0 is received), Virtual DataPort returns all the results together at the end of the statement run (by default 90000).

  • Chunk size: number of results that make up a chunk of results. When Virtual DataPort Tool obtains this number of results, it will return them to the Data Catalog, even though the Chunk Timeout has not been reached (by default 1000).

Servers Configuration: connection settings to the Virtual DataPort servers

Servers Configuration: connection settings to the Virtual DataPort servers

Configure the Database Used by Data Catalog

The Denodo Data Catalog embeds a database (Apache Derby) where it stores the settings and metadata. This database runs locally and does not accept external connections.

You can configure the Data Catalog to use an external database instead. This is necessary if you want to build a cluster of Data Catalog servers. In this case, all the Data Catalog servers of the cluster have to point to the same database (see the appendix Installing the Data Catalog on a Cluster: Sharing the Same Settings Across All the Nodes).

To do this, you need to provide this information:

  • Database. Select the database you want to use to store the metadata. You can choose among these ones:

    • Derby Embedded

    • MySQL 5.6, 5.7 and 8.0

    • Oracle 12c

    • PostgreSQL 9.5

    • SQL Server 2014

    • Amazon Aurora for MySQL 5.7 and PostgreSQL 9.5

    • Azure SQL Server

  • Driver Class. Name of the Java class of the JDBC adapter to be used. This field is automatically filled depending on the selected database.

  • URI. Database access URI. This field is automatically filled with a template depending on the selected database.

  • Authentication. Authentication method for accessing the external database. Select one of these methods:

    • Login and Password. Use the provided Username (optional) and Password (optional).

    • Kerberos with password. Use Kerberos authentication, with the provided Username and Password (from the Active Directory account).

    • Kerberos with keytab. Use Kerberos authentication, with the provided Username (in this case, the Service Principal Name - SPN -) and Keytab file (no password needed).

    • Kerberos with Windows User. Use Single Sign-On (SSO) with Kerberos, doing pass-through with the user that launched the server (no user name nor password required).

    Note

    Kerberos authentication options are only available when the database is Oracle or SQLServer.

  • Max Active Connections (optional). Maximum number of active connections that can be managed by the pool at the same time (zero means no limit).

  • Max Idle Connections (optional). Maximum number of active connections that can remain idle in the (zero implies no limit).

  • Max Wait Time (milliseconds) (optional). Maximum amount of time to wait for an idle object when the pool is exhausted (-1 means no limit).

  • Ping Query (optional). SQL query used by the pool to verify the status of the connections that are cached. The query should be simple, and the table in question should exist. For instance: select * from dual().

Note

  • When the selected database is Derby Embedded, the fields Driver Class, URI, Username and Password are not editable.

  • When the selected database is MySQL 5.6 and above, make sure that MySQL - or the database in MySQL you are going to use - is configured with the options “Default Charset = utf8” and “Collation = utf8_unicode_ci”.

Note

As for the recommended size for the external database, it is quite difficult to give an exact number, since it is very variable depending on the specific metadata and the number of users accessing to Data Catalog. As a recommendation, reserve 5GB of space for the database (in most cases, it should be enough with less space, but we recommend a high value to avoid data corruption in case of lack of space).

Prior to configure these settings, copy the database driver/s to the directory <DENODO_HOME>/lib/data-catalog-extensions or in the directory <TOMCAT>/lib if you are using an external Apache Tomcat to deploy the Data Catalog. Then restart the Data Catalog.

As a summary of the needed steps:

  1. Export all your current metadata first, so that you can later import it in the new configured database. This is highly recommended if you want to keep your current metadata in the new database.

  2. Copy the database driver/s to the directory <DENODO_HOME>/lib/data-catalog-extensions

    To use Oracle 12, only copy the files ojdbc6.jar and orai18n.jar from <DENODO_HOME>/lib/extensions/jdbc-drivers/oracle-12c to <DENODO_HOME>/lib/data-catalog-extensions.

    To use Amazon Aurora for PostgreSQL 9.5, only copy the file postgresql-42.2.5 from <DENODO_HOME>/lib/extensions/jdbc-drivers/postgresql-10 to <DENODO_HOME>/lib/data-catalog-extensions.

    To use Azure SQL Server, only copy the file: mssql-jdbc-7.2.2.jre8.jar from <DENODO_HOME>/lib/extensions/jdbc-drivers/mssql-jdbc-7.x to <DENODO_HOME>/lib/data-catalog-extensions.

    To use Azure SQL Server via Active Directory copy the files:

    1. mssql-jdbc-7.2.2.jre8.jar, adal4j-1.6.3.jar, gson-2.8.0.jar and oauth2-oidc-sdk-5.64.4.jar from <DENODO_HOME>/lib/extensions/jdbc-drivers/mssql-jdbc-7.x to <DENODO_HOME>/lib/data-catalog-extensions.

    2. accessors-smart.jar, json-smart.jar, javax.mail.jar and nimbus-jose-jwt.jar from <DENODO_HOME>/lib/contrib to <DENODO_HOME>/lib/data-catalog-extensions.

  3. Restart the server

    1. Execute the script

      <DENODO_HOME>/bin/webcontainer_shutdown
      
    2. Open the Virtual DataPort administration tool and log in as an administrator user.

    3. Open the VQL Shell and execute

      WEBCONTAINER STATUS
      

      In the first line of the result, you should see Web Container: stopped (if not, wait for half a minute and try again).

    4. From the VQL Shell, execute:

      WEBCONTAINER START
      

      This will start the web container, including all the Denodo SOAP and REST web services.

  4. Start the Denodo web administration tools you use and the Data Catalog as usual.

  5. Launch the Data Catalog and configure the database settings through its form.

    Servers Configuration: configure the internal database

    Servers Configuration: configure the internal database

    When saving, the Data Catalog checks that it can reach this database. You will be prompted to create the necessary tables if they do not exist yet.

    If you click Yes, they will be created automatically

    To execute the script, you can specify a different user from the one configured in the form. This is useful if the configured one does not have the required privileges to create the tables.

    Alternatively, you can create them manually using the scripts (.sql) of the directory <DENODO_HOME>/resources/apache-tomcat/webapps/denodo-data-catalog/WEB-INF/classes.

    Note

    In the case you are configuring clustering, you would only have to manually create the tables once.

  6. Changes will be applied after restarting the server.

  7. If you exported the metadata in step 1, import it in the new database.

Add feedback