Changes in Virtual DataPort 7.0

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


CONNECT Privilege

In Denodo 7.0 you have to grant the CONNECT privilege to a user over a database in order to allow this user to access any of the elements of that database. The other privileges over the database or its elements are disregarded if the user/role does not have the privilege CONNECT.

In previous versions, the CONNECT privilege was only checked when establishing the connection.

Because of this change, grant the users/roles the CONNECT privilege over the databases in which they already have other privileges.

Privileges of Users with the Role “serveradmin”

Denodo 6.0 has an option to grant LDAP/Kerberos users that have the role “serveradmin” the permission to grant/revoke privileges to users and roles, including themselves. This was located in tab Privileges of the Server configuration.

In this version this option is not available. Therefore, you need to add these users to the “assignprivileges” group of Active Directory.

Changes in VQL Syntax

The statement ALTER DATABASE no longer admits the parameter I18N.

In the parameter PASSWORD of the CONTEXT it is now mandatory to indicate the password encrypted and add the token ENCRYPTED. For example:

FROM view1
CONTEXT(USERNAME = 'admin', PASSWORD = 'd4GvpKA5BiwoGUFrnH92DNq5TTNKWw58I86PVH2tQIs/q1RH9CkCoJj57NnQUlmvgvvVnBvlaH8NFSDM0x5fWCJiAvyia70oxiUWbToKkHl3ztgH1hZLcQiqkpXT/oYd' ENCRYPTED)

This affects the views that:

  • The data is obtained from a data source of type JDBC, web service, BAPI or multidimensional data sources.

  • And the data source was created with the option “with pass-through session credentials”.

  • And you want to connect to the source with other credentials than the ones you used to connect to the Server.

Changes in Date Types

Denodo 7.0 has new types to represent datetime values.

  • localdate: Represents a date as a year, a month and a day, without taking into account the time zone.

  • time: Represents a time as hour, minute, second and fraction of a second.

  • timestamp: Represents date and time, without taking into account the time zone.

  • timestamptz: Represents date and time with time zone.

  • intervaldaysecond: A duration in terms of days, hours, minutes and seconds.

  • intervalyearmonth: A duration in terms of years and months.

The type date is now deprecated.

The function current_date() (and its equivalent, CURRENT_DATE) now returns a value of type localdate.

The function now() (and its equivalent, CURRENT_TIMESTAMP) now returns a value of type timestamptz.

In Denodo 7.0, Virtual DataPort does not delegate functions with input parameters of type timestamptz to databases that do not support this data type. This is because the result of most functions operating with values of type timestamptz depends on the time zone of the query (i.e. the i18n of the query). For example:

SELECT gethour(TIMESTAMP WITH TIME ZONE '2020-01-01 00:00:00 +00:00')

This return 0 if the query is with the i18n rfc_restful (GMT) and 15 if running with i18n us_pst. As databases without a data type with time zone usually do not have functions to convert between time zones, Virtual DataPort does not delegate these functions to ensure consistency with the expected result of the function.

An exception to this rule is the function now() (and its equivalent CURRENT_TIMESTAMP), which is still delegated to all the sources in 7.0 because:

  1. To keep backward compatibility

  2. Its high impact in existing projects. That is, a lot of queries use this function so it is better to delegate it to all databases even though, there may be scenarios where the result of the query is not correct due to the change in the time zone.

    Take into account that, as the function now() returns a timestamptz value, the constructs that apply a function over the function now() will not be delegated to databases that do not support the type timestamptz.

In Denodo 7.0, the setting “Date type mapping” of the SOAP web services only affects fields of type date (deprecated), not timestamp nor timestamptz nor localdate. One of the effects is that in web services with “Date type mapping” set to “date”, if a field projects the function now(), the response of the web service will now contain a timestamp with time zone, unlike in Denodo 6.0 that only returns a date. To continue returning only a date (without the time component), use current_date() instead of now().

The section data types for dates and timestamps explains in detail these new date types.

We recommend modifying the definition of the views to use the new types because their behavior is more consistent and easier to use. The main reason being that, by default, “date” is a timestamp with time zone.

Date-time values are displayed with the following format, regardless of the language settings of the computer where the Administration Tool runs:

Formatting of date-time values in the Administration Tool

Data Type



yyyy-MM-dd (<year>-<month>-<day>)

For example: “2018-12-31”.


yyyy-MM-dd HH:mm:ss.S (<date> <time>)

For example, “2018-12-31 23:58:05.15673”


yyyy-MM-dd HH:mm:ss.S XXX (<date> <time> <time zone>)

For example, “2018-12-31 23:59:05.15673 -08:00”


HH:mm:ss.S (<hour>:<minute>:<second>.<millisecond>)

For example: “23:15:05.15673”

date (deprecated) with subtype DATE

yyyy-MM-dd (<year>-<month>-<day>)

For example: “2018-12-31”.

date (deprecated) with subtype TIMESTAMP or subtype TIMESTAMP WITH TZ

yyyy-MM-dd HH:mm:ss.S XXX (<date> <time> <time zone>)

For example, “2018-12-31 23:59:05.15673 -08:00”

date (deprecated) with subtype TIME

HH:mm:ss.S (<hour>:<minute>:<second>.<millisecond>)

For example: “23:15:05.15673”

The tool also uses these formats when storing the results of a query to a file.

If you want a query to display a datetime value with a different format, use the function FORMATDATE.

Search Fields that Use the Type Date

During the process of exporting the VQL from a Denodo 6.0 installation, we recommend setting the following properties:

SET 'com.denodo.exportMigrationCompatibility80' = 'true';
SET 'com.denodo.vdb.catalog.exportMigrationCompatibility.migrateDateTypes' = 'true';

As explained in that section, with these properties, the Denodo server 6.0 analyzes the date fields to see if they can be exported to one of the new datetime types.

You can also use the procedure MIGRATE_DATE_TYPES in Denodo 7.0 at any time to migrate the types automatically at any time.

This section explains how to search what views still have fields of type date and how to change them.

Step #1

Execute these VQL statements on the new Denodo 7.0 installation:

-- Materialized table that returns information about base views, interface views
-- and materialized tables, but not derived views.
SELECT database_name
WHERE view_type IN (0, 2, 3)
CONTEXT('queryTimeout' = '120000');

-- Materialized table that returns information about the columns of
-- type "date".
CREATE MATERIALIZED TABLE view_date_column_summary AS
SELECT database_name
WHERE column_vdp_type = 'date' CONTEXT('queryTimeout' = '120000');

Step #2

Execute the following query. It returns the fields of type “date” of the base views, interface views and materialized views. We do not include derived views yet because in many of them, the type is inherited from an underlying view.

SELECT v.database_name
FROM view_summary v
INNER JOIN view_date_column_summary c ON v.database_name = c.database_name
    AND = c.view_name CONTEXT('queryTimeout' = '120000');

Step #3

For each field of the result, we recommend changing its type to one of the new data types:

  • For base views, select the type that matches the type of the column in the table of the underlying database.

  • For interface views, modify the definition of the field.

  • For materialized views, recreate them with one of the new types instead of “date”.

Step #4

Execute the following query. It returns the fields of type “date”, including derived views.

SELECT database_name
WHERE column_vdp_type = 'date' CONTEXT('queryTimeout' = '120000');

Step #5

The type of a field of a derived view is still date if:

  1. The field is defined as such by one of its underlying views.

  2. Or if the field is the result of an expression that returns a date. For example, if a field is the result of the expression TO_DATE, the type of the field is date. In this case, you should use one of the new functions instead (TO_LOCALDATE, TO_TIME, TO_TIMESTAMP or TO_TIMESTAMPTZ depending on the type of the input field).

Substracting “Date” Expressions Now Returns a Long

The result of substracting two expressions of type date (<expression 1> - <expression 2>) is the number of days between <expression 1> and <expression 2>. The result is a positive number if <expression 1> is a more recent datetime than <expression 2>. Otherwise, the result is a negative number.

In Denodo 6.0, this number is of type int. In Denodo 7.0, this value is of type long. On very rare cases, this change may cause a query to fail. If it does, modify the view or the query.

See more about this in the section Arithmetic Operators for Datetime and Interval Values of the VQL Guide.

Backward Compatibility Properties

This section explains how to restore the behavior of previous versions of Denodo, regarding the values of type “date”. Although they are available, we recommend not using them and instead, switch to the new datetime types.

  • The execution engine may implicitly convert a text value into a datetime value. In previous versions, the result of this conversion is a value of type “date”. In Denodo 7.0, literals may be converted to one of the new datetime types, not to “date”.

    To keep the behavior of previous versions, execute the following from the VQL Shell:

    SET 'com.denodo.vdb.parser.datetime.literals.transformToOldToDateFunction' = 'true';
  • In Denodo 7.0, custom functions that in its Java source code have an input parameter or return an object of the Java class java.util.Calendar, now return a “timestamptz” value. In previous versions, they return a “date”.

    To keep the behavior of previous versions, execute the following from the VQL Shell:

    SET 'com.denodo.vdb.compatibility.datetime.custom.mapCalendarToDateType' = 'true'
  • Temporary tables use the new datetime types. In previous versions they use “date”. To keep the behavior of previous versions, execute the following from the VQL Shell:

    SET 'com.denodo.vdb.parser.datetime.createsqltable.mapDateTimeToOldDateType' = 'true'

Changes in Stored Procedures


Now when you call this stored procedure and the target has a dependency of the type storedprocedure, it returns the value Predefined Storedprocedure when this procedure is a predefined one.


Now you can call this stored procedure to get information related to Web Services.


These procedures now differentiate materialized tables from base views. In previous versions, these procedures considered materialized views as base views.

In the result of these procedures, the value of the field subtype is now materialized and the value of view_type is now 3.

Changes in Cost Optimization

As a result of some internal changes in the calculation of the cost optimizations, some query plans involving data movements and other operations can change from the previous versions.

Subtype of DECIMAL Fields

When a new base view with a DECIMAL field is created, we are going to try to use the best subtype that fits the field size, INTEGER, BIGINT OR DECIMAL.

To restore the behavior of previous versions, execute this from the VQL Shell:

SET 'com.denodo.vdb.admin.model.wrapper.convertExactDecimaltoIntegerSubtype' = 'false'

JDBC Data Sources


Starting with Denodo 7.0 GA, right after a JDBC data source opens a connection to Oracle, it executes the following command on that connection:


By doing this, the pattern of the datetime values does not depend on the configuration of Oracle. Note that if in a previous version of Denodo you created a base view from a SQL query, the conditions of this query over datetime fields may not have the right format and the query will fail. In that case, modify the query so the datetime value follows the pattern “YYYY-MM-DD” (<year>-<month>-<day>). E.g. hire_date >= DATE '2018-02-03'.

Apache Hive

The Apache Hive driver is no longer distributed.

The adapters for Apache Hive 0.7, 0.10, 0.11 and 0.12 have been removed. If you were using any of these, use the adapter for Apache Hive 0.13 instead.


If you are connecting to Cloudera’s or Hortonworks’ Hive, use the adapter specific to them. Do not use the generic Apache Hive adapters.

Impala JDBC Drivers

Due to license restrictions, the JDBC driver of Impala is no longer distributed.

DenodoConnect Component: Salesforce Wrapper

Starting with Denodo 7.0, Denodo has a native data source to Users that retrieve data from Salesforce are advised to stop using the DenodoConnect Component and use the new data source because the DenodoConnect component will not be actively maintained.

Denodo JDBC Driver Compiled with Java 8

Starting with Denodo 7.0, the Denodo JDBC driver is compatible with Java 8, 9, 10 and 11. The client applications that use the Denodo JDBC driver and that run with earlier versions of Java need to be updated.

The driver of Denodo 6.0 is compatible with Java 7 and Java 8; the driver of Denodo 5.5, with Java 6.

You cannot use the JDBC driver of earlier versions of Denodo to connect to Denodo 8.0 nor use the JDBC driver of version 8.0 to connect to earlier versions.