Changes in Virtual DataPort 8.0 GA

This section lists the changed introduced in Virtual DataPort 8.0 GA compared to Virtual DataPort 7.0.

Memory

The default configuration of Virtual DataPort now has assigned 4 gigabytes of RAM instead of 1 (parameter -Xmx). The goal is to make the configuration closer to what it should be on a production environment.

The Virtual DataPort of the installation of Solution Manager still only uses 1 gigabyte per default.

Connections Only Use One Port

The JDBC driver, the administration tool and the new Design Studio now use a single port to communicate with Virtual DataPort (9999). In previous versions, the communication requires two ports (9999 and 9997). This change simplifies the configuration of firewalls.

JMX connections still use two ports: 9997 (main port) and 9995 (auxiliary port) but they are different than in Denodo 7.0 (in Denodo 7.0, the JMX connections use 9999 and 9997). If there is a firewall between Virtual DataPort and its monitoring clients, update the configuration of your firewall to allow connections to the ports 9997 and 9995.

JDBC Data Sources

JDBC Data Sources: Location of Drivers

Do not copy JDBC drivers or any other jar files, dll files or any other libraries to the installation. Instead, upload them using the wizard of the menu File > Extensions management of the Administration Tool.

See more about this in the section Importing Extensions of the Administration Guide.

JDBC Data Sources: Some Adapters Have Been Removed

It is no longer supported to connect to the following versions of Microsoft SQL Server, using the jTDS driver: 2014 and 2016.

It is no longer supported to connect to the following versions of Microsoft SQL Server, using the official JDBC driver of Microsoft: 2000 and 2005.

These adapters have been removed because officially, the jTDS driver does not support connecting to newer versions of SQL Server and the Microsoft JDBC driver does not officially support the oldest ones.

Administration Tool

Queries Always Obtain the Execution Trace

The check box Execute with TRACE has been removed from the dialog to query views, and the administration tool always obtains the execution trace from the queries.

In previous versions, if this check box is selected, the administration tool adds the clause TRACE to the query in order to obtain the execution trace of the query. Now, this clause is always added to the queries.

Getting/Setting Configuration Properties

From now on, always use the command SET to change the value of a property instead of modifying the file <DENODO_HOME>/conf/vdp/VDBConfiguration.properties. This is particularly important, when you configured Virtual DataPort to store its configuration and metadata on an external database. With SET, the changes in properties are propagated to the other servers.

For many configuration properties, when you change their value using SET, the change is applied immediately. If you change the value in the configuration file, you always have to restart. In addition, it reduces the amount of users may require to connect to the computer where Denodo is running.

In addition, you can obtain the values of a configuration property using the new stored procedure GET_PARAMETER. This is more convenient than connecting to the computer to obtain the value of a parameter in <DENODO_HOME>/conf/vdp/VDBConfiguration.properties.

SQL Compatibility

This version does not include any change that breaks backward compatibility with any northbound application that executes SQL statements. That is, all the queries that work in version 7.0, work in the new version.

Privileges

WEBCONTAINER SET

Starting with Denodo 8.0, only global administrators can execute the command WEBCONTAINER SET. In Denodo 7.0, users with the privileges “Connect” and “Write” over the database they are connected to can also execute this command.

Roles for the Solution Manager and JMXAdmin

Virtual DataPort no longer defines the following roles:

  • solution_manager_admin

  • solution_manager_promotion

  • solution_manager_promotion_admin

  • solution_manager_promotion_admin_development

  • solution_manager_promotion_admin_production

  • solution_manager_promotion_admin_staging

  • solution_manager_promotion_development

  • solution_manager_promotion_production

  • solution_manager_promotion_staging

In Virtual DataPort 7.0, these roles are created automatically in Virtual DataPort. They were meant to be used to grant the privilege of doing certain tasks on the Solution Manager.

If you were using them for other purposes, you can create them. However, consider that because they are not “default roles”, they now can be deleted.

The Virtual DataPort installed with the Solution Manager continues to have these roles. However, they are meant to be managed from the new user interface of the Solution Manager, not using the administration tool of Virtual DataPort.


The role “jmxadmin” is now deprecated. See more about this in the section Role JMXAdmin of the list of Features Deprecated in Virtual DataPort 8.0.

Column Privileges, Row Restrictions and Custom Policies Are Always Propagated

In Denodo 7.0, by default, the Execution Engine only applies column privileges, row restrictions and custom policies granted over a view to a user/role, when this view is directly referenced from the statement.

Denodo 8.0 always enforces the column/row restrictions and custom policies regardless of if the view is directly or indirectly involved in the statement. This behavior is already available in 7.0 but it is disabled by default.

For example, let us say you define a column restriction for the role “developer” over the view “employee”. This restriction forbids this role to project the column “salary”.

If a user with this role executes the following query, the query will fail in Denodo 7.0 and 8.0:

SELECT ename, salary
FROM employee

However, if another user created the following view:

CREATE VIEW employee_dept1 AS
  SELECT ename, salary
  FROM employee
  WHERE deptno = 1
in Denodo 7.0, the users with the role “developer” that also have the privilege EXECUTE over the whole database or over this new view, they will be able to query this view (i.e. obtaining the field “salary”), even though this role has a column restriction defined over “employee”.

In Denodo 8.0, this query will fail for a lack of privileges.


To restore the behavior of Denodo 7.0, follow these steps:

  1. Log in to the administration tool with an administrator account.

  2. Execute this from the VQL Shell:

    SET 'com.denodo.vdb.catalog.user.User.enableCheckViewRestrictionAlways' = 'false';
    

    The change is applied immediately, you do not need to restart.

  3. If you want to restore the old behavior but only on certain databases, execute this:

    SET 'com.denodo.vdb.catalog.user.User.checkViewRestrictionAlways' = 'true';
    
    ALTER DATABASE <database> CHECK_VIEW_RESTRICTIONS DIRECT_QUERIES_ONLY;
    

    The change is applied immediately, you do not need to restart.

    If in the future, you want this database to always enforce these restrictions, execute this:

    ALTER DATABASE <database> DEFAULT;
    

    With DEFAULT, the database follows the behavior specified by the property com.denodo.vdb.catalog.user.User.checkViewRestrictionAlways.

This affect queries (SELECT) and INSERT, UPDATE and DELETE statements

Web Services

XML Representation of Array-Type Values

In Denodo 7.0, by default, Denodo REST web services simplified the XML representation of array-type fields that only have one sub-field by returning only the subfield, without the enclosing array.

Note

The information on this subsection is related to the XML representation, not the HTML nor the JSON representations.

For instance, consider a web service that publishes a view with this schema:

Schema with nested arrays

Schema with nested arrays

In 7.0, the service would represent the data with this XML document:

Simplified XML representation of an element with nested array values
<test_view>
   <f_n>
      <f1>10</f1>
      <f2>20</f2>
   </f_n>
   <f_n>
      <f1>10</f1>
      <f2>20</f2>
   </f_n>
   <f_n>
      <f1>10</f1>
      <f2>20</f2>
   </f_n>
   <f_n>
      <f1>10</f1>
      <f2>20</f2>
   </f_n>
</test_view>

Note that the array “f_n_n” is not in the result, only the register “f_n”.


In Denodo 8.0, the service of the example outputs this XML by default:

XML representation of an element with nested array values
<test_view>
   <f_n_n>
      <f_n>
         <f1>10</f1>
         <f2>20</f2>
      </f_n>
      <f_n>
         <f1>10</f1>
         <f2>20</f2>
      </f_n>
   </f_n_n>
   <f_n_n>
      <f_n>
         <f1>10</f1>
         <f2>20</f2>
      </f_n>
      <f_n>
         <f1>10</f1>
         <f2>20</f2>
      </f_n>
   </f_n_n>
</test_view>

To restore the behavior of the version 7.0, follow these steps:

  1. Execute the following command:

    SET 'com.denodo.wsgenerator.restws.xmlArraySimpleOutput' = 'true';
    
  2. Redeploy all the REST web services. The changes to this property are applied to a web service when you redeploy the web service.

XML And JSON Representation of Errors Has Changed

In Denodo 7.0, by default, Denodo REST web services had different error formats in the XML and JSON representations.

In Denodo 8.0, by default, Denodo REST web services share a common format for errors in the XML and JSON representations.

Note

The information on this subsection is related to the XML and JSON representations, not the HTML representations.

When there are errors before a web service has returned any results:

JSON representation of an error response without partial results in 7.0
{
   "errors":[
      {
         "message":"The field 'somefield' does not exist."
      }
   ]
}
JSON representation of an error response without partial results in 8.0
{
   "__errors__":[
      {
         "message":"The field 'somefield' does not exist."
      }
   ]
}
XML representation of an error response without partial results in 7.0
<?xml version="1.0" encoding="UTF-8"?>
<error xmlns="http://www.denodo.com/restful">
   <message>The field 'somefield' does not exist.</message>
</error>
XML representation of an error response without partial results in 8.0
<?xml version="1.0" encoding="UTF-8"?>
<__errors__ xmlns="http://www.denodo.com/restful">
   <error message="The field 'somefield' does not exist." />
</__errors__>

When there are errors after a web service has begun returning results:

JSON representation of an error response with partial results in 7.0
{
   "name":"v41271",
   "elements":[
      {
         "iinc_id":1,
      },
[...]
   ],
   "__errors__":[
      {
         "code":50001,
         "message":"Error executing query. Total time 1.041 seconds.\n\nQUERY [PROJECTION] [ERROR] \nV41271 [PROJECTION] [ERROR] \nV41271 [UNION] [ERROR] \nV41271 [VIRTUAL] [ERROR] \nV41271 [JDBC WRAPPER] [ERROR] \nV41271 [JDBC ROUTE] [CONNECTION_ERROR]  Unexpected error creating a connection: ORA-01017: invalid username/password; logon denied\n Received exception with message 'ORA-01017: invalid username/password; logon denied'"
      },
[...]
      {
         "code":11,
         "message":"V41271 [JDBC ROUTE]  [CONNECTION_ERROR] Unexpected error creating a connection: ORA-01017: invalid username/password; logon denied"
      }
   ]
}
JSON representation of an error response with partial results in 8.0
{
   "name":"v41271",
   "elements":[
      {
         "iinc_id":1,
      },
[...]
   ],
   "__errors__":[
      {
         "code":50001,
         "message":"Error executing query. Total time 1.041 seconds.\n\nQUERY [PROJECTION] [ERROR] \nV41271 [PROJECTION] [ERROR] \nV41271 [UNION] [ERROR] \nV41271 [VIRTUAL] [ERROR] \nV41271 [JDBC WRAPPER] [ERROR] \nV41271 [JDBC ROUTE] [CONNECTION_ERROR]  Unexpected error creating a connection: ORA-01017: invalid username/password; logon denied\n Received exception with message 'ORA-01017: invalid username/password; logon denied'"
      },
[...]
      {
         "code":11,
         "message":"V41271 [JDBC ROUTE]  [CONNECTION_ERROR] Unexpected error creating a connection: ORA-01017: invalid username/password; logon denied"
      }
   ]
}
XML representation of an error response with partial results in 7.0
<?xml version="1.0" encoding="UTF-8"?>
<denodo:view xmlns:denodo="http://www.denodo.com/restful" xmlns="http://www.denodo.com/restful/admin/views/v41271" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="v41271">
   <v41271>
      <iinc_id>1</iinc_id>
   </v41271>
[...]
   <__errors__>
      <error xmlns="http://www.denodo.com/restful">
         <message code="50001">Error executing query. Total time 1.043 seconds. QUERY [PROJECTION] [ERROR] V41271 [PROJECTION] [ERROR] V41271 [UNION] [ERROR] V41271 [VIRTUAL] [ERROR] V41271 [JDBC WRAPPER] [ERROR] V41271 [JDBC ROUTE] [CONNECTION_ERROR] Unexpected error creating a connection: ORA-01017: invalid username/password; logon denied Received exception with message 'ORA-01017: invalid username/password; logon denied'</message>
[...]
         <message code="11">V41271 [JDBC ROUTE] [CONNECTION_ERROR] Unexpected error creating a connection: ORA-01017: invalid username/password; logon denied</message>
      </error>
   </__errors__>
</denodo:view>
XML representation of an error response with partial results in 8.0
<?xml version="1.0" encoding="UTF-8"?>
<denodo:view xmlns:denodo="http://www.denodo.com/restful" xmlns="http://www.denodo.com/restful/admin/views/v41271" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="v41271">
   <v41271>
      <iinc_id>1</iinc_id>
   </v41271>
[...]
   <__errors__>
      <error code="50001" message="Error executing query. Total time 1.045 seconds. QUERY [PROJECTION] [ERROR] V41271 [PROJECTION] [ERROR] V41271 [UNION] [ERROR] V41271 [VIRTUAL] [ERROR] V41271 [JDBC WRAPPER] [ERROR] V41271 [JDBC ROUTE] [CONNECTION_ERROR] Unexpected error creating a connection: ORA-01017: invalid username/password; logon denied Received exception with message 'ORA-01017: invalid username/password; logon denied'" />
[...]
      <error code="11" message="V41271 [JDBC ROUTE] [CONNECTION_ERROR] Unexpected error creating a connection: ORA-01017: invalid username/password; logon denied" />
   </__errors__>
</denodo:view>

To restore the behavior of the version 7.0, follow these steps:

  1. Execute the following command:

    SET 'com.denodo.wsgenerator.rest.consolidatedErrorsSchema' = 'false';
    
  2. Redeploy all the REST web services. The changes to this property are applied to a web service when you redeploy the web service.

RESTful Web Service: Parameter $select Allows Expressions

This section is in regards of the RESTful web service of Denodo (i.e. https://denodo-server.acme.com:9090/denodo-restfulws), not the REST web services.

In previous versions, the value of the input parameter “$select” can only be a list of fields separated by comma but the service can be configured to allow expressions as well.

In version 8.0, the parameter “$select” processes expressions by default. For example, you can invoke this:

https://denodo-server.acme.com:9443/denodo-restfulws/customer360/views/customer?$select=concat(last_name, ', ' , first_name) AS full_name, upper(state)

(for clarity, this URL is not escaped)

To restore the behavior of previous versions, follow these steps:

  1. Log in to Virtual DataPort with an administrator account and execute this:

    WEBCONTAINER STOP
    

    You can use the Administration Tool or the Design Studio.

  2. Edit the file <DENODO_HOME>/resources/apache-tomcat/webapps/denodo-restfulws/WEB-INF/other_settings.xml and search for the entry processFunctionsInSelectParameter; change its value to false.

  3. Execute this command:

    WEBCONTAINER START
    

This change only affects the global RESTful web service. You can control this feature for each REST web service with the option Process functions in $select parameter (tab Advanced of the configuration of the service).

Cache Module

Presto

In Denodo 8.0, Virtual DataPort transfers data into Presto using non-managed (external) Hive tables. By using external tables, the URI configured to upload the data files can be different than the location of the schema used for caching or perform data movements. Make sure the configuration property of Presto hive.non-managed-table-writes-enabled is true; otherwise, the process of storing data in Presto will fail. To restore the behavior of Denodo 7.0 (i.e. not using external tables), execute this:

SET 'com.denodo.vdb.util.tablemanagement.sql.PrestoTableManager.useExternalTables'='false';

You do not need to restart to apply this change.