Contains, Containsand, Containsor and Iscontained Operators¶

The following operators have been removed:

• contains (documentation of Denodo 6.0)

• containsand (documentation of Denodo 7.0)

• containsor (documentation of Denodo 7.0)

• iscontained (documentation of Denodo 7.0)

These operators do not belong to the SQL standard. Therefore, tools that generate SQL automatically (e.g. Business Intelligence tools) never use these operators.

The operator contains could only be used in SQL queries that were pushed down to Google Search Appliance (GSA), Denodo Aracne or custom wrappers that were developed specifically to support this operator. The API of custom wrappers still have the classes, methods and constants related to this operator. Therefore, you can load your existing extensions without modifying them nor having to recompile the Java code of the extensions. However, the execution engine will not execute this operator.

The operators containsand, containsor and iscontained can be replaced with the function POSITION and the operator IN. Examples:

containsand

Replace this:

field1 containsand ('AZ', 'CA')

with this:

POSITION(UPPER('AZ') IN UPPER(field1)) > 0 AND POSITION(UPPER('CA') IN UPPER(field1)) > 0

containsor

Replace this:

field1 containsor ('AZ', 'CA')

with this:

POSITION(UPPER('AZ') IN UPPER(field1)) > 0 OR POSITION(UPPER('CA') IN UPPER(field1)) > 0

iscontained

Replace this:

field1 iscontained ('Cali', 'California');

with this:

POSITION(UPPER(field1) IN UPPER('Cali')) > 0 AND POSITION(UPPER(field1) IN UPPER('California')) > 0

These examples apply the function UPPER to the input parameters of POSITION because the operators containsand, containsor and iscontained were case-insensitive.

Parameter QUERYPLAN of the CONTEXT Clause of SELECT Statements¶

In the Execution plan (Options button) of the derived views, you can no longer choose the join method (hash, merge, nested…) nor the join order of the join views that form the view you are editing. In this new version, the Execution plan dialog is only used to configure the data movements of the view.

The parameter QUERYPLAN of the CONTEXT clause of SELECT statements has also been removed.

These two features have been removed because setting the execution plan of a query manually is difficult and usually led to choosing a worse plan than what the cost optimizer choose. Instead, enable the Cost-Based Optimizer and gather the statistics of the views involved in the query. That way, the optimizer will choose an efficient plan.

JDBC Data Sources¶

The following parameters of the statement CREATE JDBC DATA SOURCE are no longer allowed:

• INITIALCAPACITYPS

• MAXSLEEPINGPS

These parameters controlled the pool of prepared statements (not the regular pool of connections used by queries).

These parameters have been replaced with MAXOPENPREPAREDSTATEMENTS.

JDBC Data Sources: Adapters¶

The following JDBC adapters have been removed:

• Connection to Microsoft SQL Server 2000 with the Microsoft JDBC driver

• Connection to Microsoft SQL Server 2005 with the Microsoft JDBC driver

These adapters have been removed because the Microsoft JDBC driver does not officially support these versions of the database. Instead, users have to use the jTDS adapter for these versions.

Option “Internationalize query results”¶

The check box Internationalize query results of the dialog Admin tool preferences has been removed. The main effect is that now, the Administration Tool always displays the values of type decimal and datetime with the same format, regardless of the i18n of the query or the i18n of the Virtual DataPort server. That is, even if the i18n used defines the comma (“,”) as the decimal separator, the Administration Tool will always use “.” as decimal separator.

Note that this option was a local setting of the Administration Tool - not the Virtual DataPort server - and it only affected how the Administration Tool displayed some types of values in the results of the queries. This option never controlled how other applications that execute queries in Virtual DataPort display date-time or decimal values but occasionally, users would think so.

In Denodo 7.0, this option only controls how the Administration Tool displays decimal values in the results of the queries (i.e. what symbol for decimal point to use: “,” or “.” depending on the i18n selected). In Denodo 6.0 and earlier, this option also controls how the Administration Tool displays date-time values in the results of the queries.

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

Option “Execute with trace”¶

The option “Execute with trace” is no longer available in the Administration Tool. Now, the Administration Tool always obtains the execution trace of the queries by adding the “TRACE” clause at the end of the SQL statement.

Removed “Query Capabilities” Panel¶

In the Options section of the view - tab Search methods, the information about query capabilities has been removed. Instead, this tab lists the mandatory fields of the view.

In base views, the information provided in this panel led users into think they could control what columns were mandatory. This was only possible for some base views.

The Search method panel now shows what fields are mandatory when querying a base views. Some types of base views like base views that query REST APIs sometimes have mandatory fields; these are still displayed. To create views with mandatory parameters, create a selection view with view parameters.

Removed Swapping Settings for Views and Databases¶

You can no longer use the Administration Tool to change the following settings of views and databases:

• Maximum size in memory of intermediate results

• Enable / disable swapping

• Maximum size in memory of each node

They have been removed from these panels:

• The tab Memory of the advanced options of the views (the tab has been removed).

• The Memory usage dialog of the configuration of the databases.

These options have been removed to avoid users from changing them. Often, these settings were misused in a way that led to queries running slower. These options can still be modified with VQL commands.

You can change these settings for the entire Virtual DataPort server (menu Administration > Server configuration, tab Memory usage).