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.

JDBC Data Sources with Kerberos Authentication¶

In previous versions of the Denodo Platform, to override the default Kerberos configuration of the system, you have to create the file <DENODO_HOME_7_0>/jre/lib/security/krb5.conf with the appropriate information.

In Denodo 8.0, you have to create this file on a different location: <DENODO_HOME>/jre/conf/security/krb5.conf. Note that, when you place this file on a folder of the Java Runtime Environment, the file is called “krb5.conf” - not “krb5.ini” - regardless of whether you are on Windows or Linux.

The section Locating the krb5.conf Configuration File of the documentation of Java 11 explains how Java applications locate the krb5.conf file.

This change affects mainly to JDBC data sources that require Kerberos authentication, in Virtual DataPort installations that do not have Kerberos enabled. In this scenario, often you have to create a krb5 file. However, it affects any component of the Denodo Platform if you do not specify the location of the krb5 file.

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.

UNION and UNION ALL¶

In Denodo 8.0, the operators UNION and UNION ALL behave as defined in the SQL standard. That is, the result of the operator UNION does not return duplicated rows; if there are duplicated rows, the UNION removes them. The goal of this change is to be more compliant with the SQL standard. To keep the duplicate rows, use UNION ALL instead.

See more about how these operators work in the page UNION Clause of the VQL Guide. Pay attention to the performance implications of using UNION vs UNION ALL (UNION ALL performs much better).

Consider this:

• This change may affect the result of queries that use the operator UNION.

• In previous version of Denodo, when you create a “Union view” using the Administration Tool, it is created with the operator “UNION ALL”, not “UNION”. So in that case, as these views already use the operator “UNION ALL”, the result will not be affected.

• In previous versions of Denodo, UNION returns the same results as UNION ALL (it does not remove duplicate rows). In the previous version of Denodo (Denodo 7.0), you can configure the UNION operator to behave as it is defined in the SQL standard but this is disabled by default.

ORDER BY Without Projecting the Field¶

The ORDER BY clause of the queries can now include a column of a view without projecting it.

Removal of Leading and Trailing Spaces of Identifiers¶

The spaces at the beginning and end of an identifier are removed automatically. For example, if you execute this statement:

CREATE VIEW " customer_australia " AS SELECT * FROM customer WHERE country = 'Australia';

The name of the view will be “customer_australia” (without the spaces at the beginning and end). This was already added in Denodo 7.0u20200310.

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 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

The Privilege WRITE no Longer Implies INSERT, UPDATE and DELETE¶

In Denodo 7.0, when you grant the privilege WRITE to users and roles, they automatically gain the privileges INSERT, UPDATE and DELETE. In Denodo 8.0, granting the privilege WRITE no longer grants INSERT, UPDATE and DELETE; you need to grant these explicitly.

When you upgrade from Denodo 7.0 to 8.0 following the steps of the Upgrade Guide, the VQL you obtain from 7.0 assigns the privilege INSERT, UDPATE and DELETE to the users that have the privilege WRITE. That way, the user accounts and roles keep having the same privileges.

The page User and Access Rights in Virtual DataPort lists the actions that the users with the Write privelege can do.

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.

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

Schema with nested arrays¶

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

<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:

<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.

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

{
   "errors":[
      {
         "message":"The field 'somefield' does not exist."
      }
   ]
}

{
   "__errors__":[
      {
         "message":"The field 'somefield' does not exist."
      }
   ]
}

<?xml version="1.0" encoding="UTF-8"?>
<error xmlns="http://www.denodo.com/restful">
   <message>The field 'somefield' does not exist.</message>
</error>

<?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:

{
   "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"
      }
   ]
}

{
   "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 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 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:9443/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).