Replication Process¶

This section explains how, when a server does a change in the catalog (e.g. a user creates a view), the other servers that share the same database become aware of this change.

The catalog of Virtual DataPort has an in-memory cache that stores objects temporarily (this is not the Cache Module that stores data of views obtained from data sources). The goal of this in-memory cache is to be able to access objects (data sources, views…) quicker than if they had to be read from the database every time. When Virtual DataPort starts, this cache is empty and the first time an object is accessed (i.e. used in a query, opened from the administration tool…), the catalog stores this object in this in-memory cache. If later, an object is modified (e.g. the configuration of a data source changes), the catalog updates this cache.

By default, the catalog uses the local database Apache Derby (default option). In this case, the in-memory cache is always updated because only the local server can modify it.

When the catalog uses an external database, it also uses this in-memory cache. However, the catalog has to ensure that the objects retrieved from this cache are up to date. That is because another server could have modified an object since the catalog read it from the database and stored in the cache. To ensure an objects is up to date, the catalog does these steps when another component wants to read an object:

1. If the in-memory cache does not contain the object, the catalog reads it from the external database and stores it in the in-memory cache.

2. If the in-memory cache already contains the object, the catalog executes a query on the external database to validate that this object has not been modified in the database since it was read. If the object is outdated, the catalog reads it again and updates the cache.

Regarding web services, the external database stores the configuration of the web services and their status (i.e. if they are deployed or not). Every minute, Virtual DataPort, if it is configured to use an external database, checks if each web service is marked as deployed or undeployed in the database. If in a server, the status of the web service is different, it deploys it or undeploys it respectively. E.g. if server #1 deploys the web service “customer”, it stores in the database that the status of the web service is deployed. When server #2 checks the status of the web services, it will deploy “customer” if it has not been deployed already.

The same applies to JMS listeners. When you start or stop a JMS listener on one server, in one minute, the other Virtual DataPort servers will start it or stop it respectively.

Databases Supported¶

Virtual DataPort can store its catalog in these databases:

• Amazon Aurora MySQL.

• Amazon Aurora PostgreSQL.

• IBM DB2 10, 11 and higher.

• Microsoft SQL Server 2012, 2014, 2016, 2017, 2019 and higher. Also Azure SQL.

• MySQL 5, 8 and higher and GCP Cloud SQL for MySQL.

• Oracle 11g, Oracle 12c, Oracle 18c, Oracle 19c and higher.

• PostgreSQL 9, 10, 11, 12 and higher and GCP Cloud SQL for PostgreSQL.

To use MySQL, launch the MySQL server with the parameter --max-allowed-packet=500M to ensure MySQL can receive and transfer the resources that Virtual DataPort stores. The documentation of MySQL explains how to set this parameter.

Considerations About This Feature¶

Take this into account before enabling this feature:

• To use this feature, the database of the catalog should be “as close as possible” to the Virtual DataPort servers. That is, the database should be on the same network or at least, on the same data center as the servers. If this condition is met, the overhead of using an external database for the catalog is minimal. Otherwise, there may be some delays in the execution of queries.

• Virtual DataPort manages its internal tables by itself. Making manual modifications on them could cause different types of malfunctioning on the system.

• All the Virtual DataPort servers that share the same external database need to have the same update of the Denodo Platform installed. That is because the metadata stored by a Virtual DataPort server may not be readable by another Virtual DataPort that has an older update. Therefore, having several Virtual DataPort servers with different updates sharing the same database is an unsupported configuration.

• When you install a Denodo Platform update in the Virtual DataPort servers of a cluster that share the same external database, do not execute any queries or do actions that modify the metadata in any of the servers of the cluster until the update process is completed in all the servers. That is, do not create/modify/delete views, data sources…, do not publish web services, etc. until the update is installed on all the servers.

• The communication from Virtual DataPort to the external database has to be stable. If at some point, the communication to the external database is not possible because of for example, a network problem, the execution engine cannot start to process any query until the communication is re-established.

• When Virtual DataPort is starting and detects that the configuration of the catalog changed (e.g. the administrator changed the database to store the catalog), it replaces the file <DENODO_HOME>/conf/vdp/VDBConfiguration.properties with <DENODO_HOME>/setup/vdp/templates/conf/VDBConfiguration.template. This file is only replaced when Virtual DataPort starts after this configuration has changed, not after every restart.

• When you configure the catalog to use an external database, the metadata is not transferred with the possible exception of the JDBC driver required to connect to this database. If this driver is not included with Denodo, when you click Ok in the Metadata database configuration dialog, Virtual DataPort does the following:

  • If the driver is already stored in the new database, it computes the MD5 hash value of the driver stored in the current database (e.g. the local Derby) and the driver stored in the new database. If the MD5 value is different, Virtual DataPort replaces the driver in the new database with the driver in the current database.

  • If the driver is not stored in the new database, it stores it.

• When you use an external database, to change a configuration property do not modify the file <DENODO_HOME>/conf/vdp/VDBConfiguration.properties. Instead, change the property using the command SET. This will change the configuration property in the server where you execute it and propagate the change to the other servers that share the same database.

• If you have a cluster of Denodo servers, when an application executes the command ENTER SINGLE USER MODE, only the Denodo server that receives this command switches to single user mode; the others continue operating normally. This occurs regardless of if Virtual DataPort stores the metadata on an external database or not.

• When you use an external database, the Apache Derby database embedded with the Denodo Platform cannot be used as cache database.

• When you use Microsoft SQL Server or Azure SQL as external database catalog, by default, Virtual DataPort configures the text columns in the metadata catalog with VARCHAR type with Latin1_General_BIN as collation. If you are using Non-Unicode characters for Virtual DataPort catalog elements it is necessary you to configure those columns with NVARCHAR type. To do so, execute the following before the metadata catalog creation:

  1. Execute this command before configuring Virtual DataPort to store the metadata on an external database:

     SET 'com.denodo.vdb.catalog.storage.sqlserver.textColumnAsNVarchar' = 'true';
     

  2. Configure Virtual DataPort to store the metadata on an external database. If you had already done this process, you have to use an empty catalog/schema so in the tables that will store the metadata, the columns for text values have the data type NVARCHAR.

  3. Restart Virtual DataPort for the changes to be applied.

  4. In Denodo 8.0 versions prior to 8.0u20240306, run this command again to update the current properties after a catalog change:

     SET 'com.denodo.vdb.catalog.storage.sqlserver.textColumnAsNVarchar' = 'true';
     

  5. Restart Virtual DataPort for the latest change to take effect.

  To go back to the default behavior, execute this:

  SET 'com.denodo.vdb.catalog.storage.sqlserver.textColumnAsNVarchar' = NULL;
  

  Then, you have to perform again a catalog change to a clean schema in order to the columns to be created as VARCHAR type.

How to Configure Virtual DataPort to Store the Metadata on an External Database¶

This section explains how to configure the catalog of Virtual DataPort to use an external database.

Before changing the configuration, consider this:

• Virtual DataPort does not transfer metadata from one database to the other (not even from the embedded Apache Derby). Therefore, when you do this process, you have to re-create the metadata you had, including its configuration.

• You will have to enable SSL again.

• The administrator of the database needs to create a user account in this external database that has these privileges:

  • CREATE/DROP/ALTER TABLE. Also for creating INDEXES over those tables.

  • INSERT/DELETE/UPDATE privilege on own tables.

  • LOCK TABLE (only for DB2 and Oracle)

  • CREATE/DROP SEQUENCE (only for Oracle)

  • CREATE/DROP/REPLACE TRIGGER (only for Oracle)

Now follow these steps:

1. Create a catalog/schema in the external database that will store the metadata.

   Consider enabling the high availability features of this database to meet higher uptime requirements.

2. If you already created objects (users, data sources, views…), export all the metadata. That is, click the menu File > Export. In this dialog, select Replace existing elements and select all the check boxes below this option.

3. Click the menu Administration > Metadata database configuration.

External database configuration¶

4. In Database adapter, select the database you are going to use. The default value of Driver class is correct.

   If Driver class is empty is because it is not included in Virtual DataPort and has not been uploaded yet to this server. To upload the driver to this server, close this window and follow these steps:

   1. Click the menu File > Extension management.

   2. In this dialog, click Libraries and then, Import.

   3. In the new dialog, in Resource type, select jdbc.

   4. In Version, select the driver you are going to upload. For example, if you are going to upload the driver of IBM DB2 11, select db2-11.

   5. Click Add and select the file(s). To select more than one file, hold the key Ctrl and select each file.

   6. Click Ok to upload the file(s) to Virtual DataPort.

   7. Go back to the Metadata database configuration dialog.

5. Enter the Database URI, Login and Password.

6. Select Specify custom catalog and schema.

7. Important: click Test configuration to ensure that the connection details, the user name and password are correct; and that this account has privileges to create tables in this database.

8. Click Ok to save.

   The moment you click Ok, Virtual DataPort creates the tables in the external database that will store the catalog but does not start using these tables until you restart.

   If the tables, already exist, it does not re-create them. That is because in the scenario where you configure several Virtual DataPort servers to use an external database, only the first server where you do this change has to create these tables.

9. Restart Virtual DataPort.

   At this point, Virtual DataPort begins reading all the metadata from the new database.

10. Log in to Virtual DataPort using the default user account. That is, user name is admin and password, admin. This is because when you switch the database of the catalog, the metadata is not transferred to the new database so there are no users created.

11. If in step 2, you exported the metadata, import the VQL file.

12. If in the past, you had removed the user “admin”, delete it as well. To check if you had removed the user “admin”, check if the VQL file contains the statement “CREATE OR REPLACE USER admin”.

13. If you enabled TLS on the server, repeat the process. That is, execute the script <DENODO_HOME>/bin/denodo_tls_configurator. See more about this in the section Denodo SSL/TLS Configurator Script of the Installation Guide.

14. If you are building a cluster of Virtual DataPort servers that will all share the same database, do this:

    1. In this first installation, click the menu Administration > Metadata database configuration.

    2. In this dialog, click Export settings and enter “metadata_database_configuration.properties”. This will generate a file with the settings of this dialog.

    3. Copy this file to all the computers that will be part of the cluster.

    4. Stop the Virtual DataPort server of all these computers.

    5. On each computer of the cluster, open a command line and execute this command:

       On Linux¶

       cd <DENODO_HOME>
       ./bin/regenerateMetadata.sh -f "metadata_database_configuration.properties"
       

       On Windows¶

       cd <DENODO_HOME>
       .\bin\regenerateMetadata.bat -f "metadata_database_configuration.properties"
       

regenerateMetadata -f <input-file> [--externalDriver <path-to-external-driver>] [-reset] [-y]

regenerateMetadata --adapter <adapter-name>
                   --version <adapter-version>
                   --driver <driver-classname>
                   [--driverProperties <driver-properties>]
                   --classPath <driver-classpath>
                   [--externalDriver <path-to-external-driver>]
                   --databaseUri <uri>
                   --user <user>
                   --password <password>
                   [--useKerberos]
                   [--useKeyTab]
                   [--keyTabFile <path-to-keytab-file>]
                   [--useKrbCache]
                   [--krbCacheFile <path-to-krb-cache-file>]
                   [--catalog <catalog>]
                   [--schema <schema>]
                   [--initialSize <pool-initial-size>]
                   [--maxActive <pool-max-active>]
                   [--testConnections]
                   [--pingQuery <pool-validation-query>]
                   [--reset]
                   [--yes]

regenerateMetadata --file configuration-file.properties

regenerateMetadata --file configuration-file.properties --reset

regenerateMetadata --adapter postgresql
                   --version 12
                   --classPath "postgresql-12"
                   --databaseUri "jdbc:postgresql://psql-db:5432/vdpcatalog"
                   --driver "org.postgresql.Driver"
                   --user "denodo_user"
                   --password "encrypted:passwordEncrypted"
                   --schema "denodo_metadata"
                   --testConnections
                   --pingQuery "SELECT 1"
                   --driverProperties "{\"prop1\":\"value1\", \"prop2\":\"value2\"}"

regenerateMetadata --adapter mysql
                   --version 5
                   --classPath "mysql-5"
                   --externalDriver "/my-drivers/mysql5"
                   --databaseUri "jdbc:mysql://mysql-db:3306/vdpcatalog"
                   --driver "com.mysql.jdbc.Driver"
                   --user "denodo_user"
                   --password "encrypted:passwordEncrypted"
                   --schema "denodo_metadata"
                   --testConnections
                   --pingQuery "SELECT 1"
                   --driverProperties "{\"prop1\":\"value1\", \"prop2\":\"value2\"}"

regenerateMetadata --adapter oracle
                   --version 19c
                   --classPath "oracle-19c"
                   --databaseUri "jdbc:oracle:thin:@oracle-db:1521/orcl"
                   --driver "oracle.jdbc.OracleDriver"
                   --user "denodo_user"
                   --password ""
                   --useKerberos
                   --useKeyTab
                   --keyTabFile /my_files/krb.keytab
                   --schema "denodo_metadata"
                   --testConnections
                   --pingQuery "SELECT COUNT(*) FROM SYS.DUAL"
                   --driverProperties "{\"prop1\":\"value1\", \"prop2\":\"value2\"}"

regenerateMetadata --adapter oracle
                   --version 19c
                   --classPath "oracle-19c"
                   --databaseUri "jdbc:oracle:thin:@oracle-db:1521/orcl"
                   --driver "oracle.jdbc.OracleDriver"
                   --user ""
                   --password ""
                   --useKerberos
                   --useKrbCache
                   --krbCacheFile /my_files/krbCacheFile
                   --schema "denodo_metadata"
                   --testConnections
                   --pingQuery "SELECT COUNT(*) FROM SYS.DUAL"
                   --driverProperties "{\"prop1\":\"value1\", \"prop2\":\"value2\"}"