Data Source Configuration Properties¶

The source configuration of a JDBC data source represents the capabilities of the underlying database. That is, it stores a list of the SQL operations and functions this database supports, the comparison operators it supports, etc. The Execution Engine uses this information to build the queries it executes in a database, with the goal of pushing down to the data source as much processing as possible, to optimize response times and minimize traffic through the network. Each database adapter defines a default source configuration. The default source configuration is different for each adapter because the capabilities of let us say Oracle, are different than the capabilities of SQL Server.

It is an uncommon that you need to modify the source configuration of a data source since the default options are tailored to the database you are connecting to. However, you can do so with this command:

ALTER DATASOURCE JDBC "<data source name>"
    SOURCECONFIGURATION (
          [ <source configuration property>
        [ , <source configuration property> ]* ]
    );

Note that with this command you are modifying the source configuration of “<data source name>”; not the source configuration of all the data sources that use the same database adapter.

For example:

ALTER DATASOURCE JDBC ds_jdbc_athena_prod
    SOURCECONFIGURATION (
          SUPPORTSORDERBYEXPRESSION = false
        , DELEGATENATURALOUTERJOIN = false
    );

To restore the default value of a property, set it to DEFAULT. For example:

ALTER DATASOURCE JDBC ds_jdbc_athena_prod
    SOURCECONFIGURATION (
        DELEGATEAGGREGATEFUNCTIONS = DEFAULT
    );

The table below lists the properties you can configure in the SOURCECONFIGURATION clause of a JDBC data source.

Properties of the SOURCECONFIGURATION clause of data sources¶
Property in SOURCECONFIGURATION Clause	Meaning
ALLOWFORUPDATE	Allow for UPDATE clause. If `true`, the execution engine delegates the clause `SELECT... FOR UPDATE`.
ALLOWLITERALASPARAMETER	Allow literal as parameter. If `true`, the execution engine passes the literals as parameters of the prepared statement created to execute the view. If `false`, the execution engine generates the queries with the literals in them.
DELEGATEAGGREGATEFUNCTIONS	Delegate aggregate functions list. Comma-separated list of aggregation functions the execution engine delegates to this database.
DELEGATEALLOPERATORS	Delegate all operators. If `true`, it indicates the execution engine delegates all operators.
DELEGATEANALYTICFUNCTIONSLIST	Delegate window functions list. List of window functions that can be delegated.
DELEGATEARRAYLITERAL	Delegate array literal. If `true`, the execution engine includes array compound constants in the queries if necessary.
DELEGATE_BINARY_ORDERBY_COLLATION	Delegate binary ORDER BY collation
DELEGATECOMPOUNDFIELDPROJECTION	Delegate compound field projection. If `true`, the execution engine delegates projections on compound fields.
DELEGATECOMPOUNDINDEXEDFIELDPROJECTION	Delegate compound indexed field projection. If `true`, the execution engine delegates projections including array indexes on compound fields.
DELEGATEEXTENDEDUNIONALL	Delegate EXTENDED UNION ALL. If `true`, the execution engine delegates the operator UNION ALL between tables with different schemas.
DELEGATEFETCH	Delegate FETCH clause. If `true`, the execution engine delegates the `FETCH` clause to the source.
DELEGATEGROUPBY	Delegate GROUP BY clause. If `true`, the execution engine delegates the `GROUP BY` clause.
DELEGATEHAVING	Delegate HAVING clause. If `true`, the execution engine delegates the `HAVING` clause of the queries.
DELEGATEINFORNONLITERALS	Delegate in operator when includes non-literal arguments. If `true`, the execution engine delegates the `IN` operator when there are non-literal arguments (functions, field names…) on the right side of the operator. Default value: `true`.
DELEGATEINNERJOIN	Delegate INNER JOIN. If `true`, the execution engine delegates the `INNER JOIN` operator.
DELEGATEINTERSECTION	Delegate INTERSECT. If `true`, the execution engine delegates the `INTERSECT` operator.
DELEGATEINVALIDNUMBERLITERALSASNULL	Delegate invalid number literals as NULL. If `true`, the execution engine checks that the operands of the conditions delegated to the database have compatible types. For example, let us say that we have a view `V1` with an `int` field `f1` and that, at the source, the type of this field is `NUMERIC`. If the property is `false`, the execution engine will delegate the query `SELECT * FROM v1 where f1 = '4c'` to the database and probably fail. If the property is set to `true`, the execution engine will detect that `f1` and `'4c'` are incompatible and will delegate `NULL` instead: `SELECT * FROM v1 where f1 = NULL` This enhancement only works in conditions with the operators `=`, `<>`, `<`, `>`, `<=`, `>=`, `in` and `between`.
DELEGATEJOIN	Delegate JOIN. If `true`, the execution engine delegates the `JOIN` operator.
DELEGATELEFTFUNCTION	Delegate left function. If `true`, the execution engine delegates conditions with functions on the left side of the condition.
DELEGATELEFTLITERAL	Delegate left literal. If `true`, the execution engine delegates conditions with constants on the left side of the condition.
DELEGATELITERALEXPRESSION	Delegate literal expression. If `true`, the execution engine delegates literal expressions.
DELEGATEMINUS	Delegate MINUS. If `true`, the execution engine delegates the `MINUS` operator.
DELEGATEMIXEDAGGREGATEEXPRESSION	Delegate mixed literal expression. If `true`, the aggregation expressions delegated to the database can include scalar functions, literals and fields. If `false`, the aggregation function will only include fields but not expressions.
DELEGATENATURALOUTERJOIN	Delegate natural OUTER JOIN. If `true`, the execution engine delegates the natural `OUTER JOIN` operator.
DELEGATENOTCONDITION	Delegate NOT condition. If `true`, the execution engine delegates `NOT` conditions.
DELEGATEOFFSET	Delegate OFFSET clause. If `true`, the execution engine delegates `OR` conditions.
DELEGATE_OFFSET_RESTRICTION	Delegate OFFSET clause restriction. It indicates if this database has any limitations regarding the use of the clause `OFFSET` in the queries. The possible values of this property are: NONE: the database does not have any restriction regarding `OFFSET`. FETCH: the database only supports `OFFSET` if the query also has `FETCH`. ORDER_BY: the database only supports `OFFSET` if the query also has `ORDER BY`. FETCH_ORDER_BY: the database only supports `OFFSET` if the query also has the `FETCH` and `ORDER BY` clauses. NO_ORDER_BY: the database only supports `OFFSET` if the query does not have `ORDER BY`. FETCH_NO_ORDER_BY: the database only supports `OFFSET` if the query also has `FETCH` but it does not have `ORDER BY`. If a user executes a query that involves delegating a query to this database and these restrictions are not being met, the Execution Engine will not delegate the clause `OFFSET` to the database and it will be performed by the Execution Engine. This property is ignored if Delegate OFFSET clause is set to no.
DELEGATEOPERATORSLIST	Delegate operators list. List of operators that can be delegated. The default list has the following operators: `=`, `<>`, `<`, `<=`, `>`, `>=`, `between`, `exists`, `in`, `is false`, `is null`, `is not null`, `is true`, `like` and `notin`.
DELEGATEORCONDITION	Delegate OR condition
DELEGATEORDERBY	Delegate ORDER BY. If `true`, the execution engine delegates the `ORDER BY` clause.
DELEGATE_ORDERBY_COLLATION_MODIFIER	Delegate ORDER BY collation modifier. `true`, the execution engine can indicate a collation modifier in queries delegated to this database. More details below, in ORDER BY Properties of the Source Configuration.
DELEGATEPROJECTION	Delegate projection. If `true`, the execution engine delegates projections.
DELEGATEPROJECTSUBQUERY	Delegate SELECT subquery. If `true`, the execution engine delegates subqueries in the `SELECT` clause.
DELEGATEREGISTERLITERAL	Delegate register literal. If `true`, the execution engine delegates register constants.
DELEGATERIGHTFIELD	Delegate right field. If `true`, the execution engine delegates conditions with fields on the right side of the condition.
DELEGATERIGHTFUNCTION	Delegate right function. If `true`, the execution engine delegates conditions with functions on the right side of the condition.
DELEGATERIGHTLITERAL	Delegate right literal. If `true`, the execution engine delegates conditions with constants on the right side of the condition.
DELEGATESCALARFUNCTIONS	Delegate scalar functions list. List of scalar functions that the execution engine delegates to the database.
DELEGATESELECTDISTINCT	Delegate SELECT DISTINCT. If `true`, the execution engine delegates the modifier `DISTINCT` of the clause `SELECT`.
DELEGATESELECTION	Delegate selection. If `true`, the execution engine delegates conditions to this source.
DELEGATESUBQUERY	Delegate subquery. If `true`, the execution engine delegates queries with subqueries in them.
DELEGATEUNION	Delegate UNION. If `true`, the execution engine delegates the operator `UNION`.
DELEGATEUNIONALL	Delegate UNION ALL. If `true`, the execution engine delegates the operator `UNION ALL`.
DELEGATEUNIONDISCTINCT	Delegate UNION DISTINCT. If `true`, the execution engine delegates the ANSI operator `UNION`.
DELEGATEWHERESUBQUERY	Delegate WHERE subquery. If `true`, the execution engine delegates subqueries in the `WHERE` clause.
NESTEDJOINWITHBLOCKSSTRATEGY	Nested join optimization syntax. This property controls some aspects of how the execution engine delegates a query when the data of the view in the right side of a nested join is obtained from this data source. See more about this in the section Nested Join.
SUPPORTSAGGREGATEFUNCTIONSOPTIONS	Supports modifier in aggregate function. If `true`, the execution engine delegates the modifiers `DISTINCT` and `ALL` in aggregate functions.
SUPPORTSBATCHINSERT	Supports batch inserts. If `true`, the execution engine inserts rows in batches when moving data from another data source into this one. See more about Data Movement in the section Automatic Simplification of Queries. This property does not affect `INSERT` requests statements to this data source because they are not executed in batches.
SUPPORTSBRANCHOUTERJOIN	Supports branch OUTER JOIN. If `true`, the execution engine delegates the operators `LEFT OUTER JOIN` and `RIGHT OUTER JOIN`.
SUPPORTSCLOBINBATCH	Supports CLOBs in batch inserts. If `true`, the execution engine executes `INSERT` statements in batch, when the type of one of the values is `CLOB`. If `false`, the execution engine executes `INSERT` statements one by one and in the same transaction, when the type of one of the values is `CLOB`. Virtual DataPort inserts rows in batches when moving data from another data source into this one. See more about Data Movement in the section Automatic Simplification of Queries. This property does not affect `INSERT` requests sent to this data source because they are not executed in batches. This property is ignored when Supports batch inserts is `false`.
SUPPORTSCOMPARISONDIFFERENTTYPES	Supports comparisons between different data types. If `true`, the execution engine delegates comparisons between expressions that have different data types.
SUPPORTSEQOUTERJOINOPERATOR	Supports Eq OUTER JOIN. If `true`, the execution engine delegates the SQL operator `OUTER JOIN`.
SUPPORTSEVALUATELITERALFUNCTIONS	Supports functions with only literal parameters. If `true`, the execution engine only delegates functions when all of their arguments are literals.
SUPPORTSEXPLICITCROSSJOIN	Supports explicit CROSS JOIN. If `true`, the execution engine delegates the operator `CROSS JOIN` to this source.
SUPPORTSFULLEQOUTERJOIN	Supports full Eq OUTER JOIN. If `true`, the execution engine delegates the Full Equality `OUTER JOIN` operator.
SUPPORTSFULLNOTEQOUTERJOIN	Supports full NotEq OUTER JOIN. If `true`, the execution engine delegates the Full Not Equality `OUTER JOIN` operator.
SUPPORTSFUSINGINUSINGANDNATURALJOIN	Supports fusing in USING and natural JOIN. If `true`, it means that this source merges the same fields when running a natural `JOIN` or a `JOIN` with the `USING` clause.
SUPPORTSGROUPBYLITERALASPARAMETER	Supports GROUP BY literals as parameters. If `true`, the execution engine may delegate the literals of the `GROUP BY` clause as parameters of the prepared statement created to execute the query. Otherwise, the Server generates the query with literals.
SUPPORTSJOINONCONDITION	Supports JOIN ON Condition. If `true`, the execution engine delegates the clause `JOIN...ON`.
SUPPORTSJOINPARENTHESIS	Supports parenthesis in n-joins. If `true`, the execution engine delegates n-joins grouped with parenthesis.
SUPPORTSNATURALJOIN	Supports NATURAL JOIN. If `true`, the execution engine delegates the natural `JOIN` clause.
SUPPORTS_ORDERBY_BINARY_COLLATION	Supports binary ORDER BY collation. If `true`, the execution engine assumes that this database executes the operation `ORDER BY` using a binary collation. See section ORDER BY Properties of the Source Configuration for more details about this.
SUPPORTSORDERBYEXPRESSION	Supports ORDER BY expressions. If `true`, the execution engine delegates queries with expressions in the `ORDER BY` clause.
SUPPORTSRIGHTDEEPJOIN	Supports right deep n-joins. If `true`, the execution engine may delegate queries with right deep n-joins with all the `ON` conditions at the end. If `false`, the query will have a subquery for each of the n-joins of the query sent by the client to Virtual DataPort.
SUPPORTSPREPAREDSTATEMENT	Supports prepared statements (only available for JDBC data sources that use the database adapter Generic). If `true`, the execution engine executes the queries using a prepared statement. If `false`, it executes the queries with regular statements. Default value: `true`.
SUPPORTSUSINGJOIN	Supports USING JOIN. If yes, it indicates that the `USING JOIN` clause can be delegated to the source.

ORDER BY Properties of the Source Configuration¶

To perform ORDER BY operations over fields of type “text”, Virtual DataPort uses a “binary” collation to compare the text values of the result set and sort them. Binary collations compare strings using the Unicode value of each character.

When Virtual DataPort pushes down an ORDER BY to a database to be able to perform a merge join, the database has to perform the ORDER BY using a binary collation as well. The reason is that the Execution Engine expects the rows to be sorted using a binary collation. If they were sorted with a different collation, the results may be incorrect when the join conditions involve fields of type text.

The properties “Delegate ORDER BY collation modifier” and “Delegate binary ORDER BY collation” control how Virtual DataPort pushes down the ORDER BY clause to databases.

Note

This section explains how the following properties affect the behavior of Virtual DataPort. However, very rarely you will need to modify their default value.

Supports binary ORDER BY collation (SUPPORTS_ORDERBY_BINARY_COLLATION): the default value is yes for databases that meet one of the following conditions, when executing an ORDER BY over fields of type text:
1. By default, they use a binary collation to sort the data.
2. Or, they support forcing a binary collation to perform the ORDER BY.
When the Execution Engine selects a method to execute a join whose conditions involve fields of type text, it selects the method merge if the property “Supports binary ORDER BY collation” is yes in all the sources involved in the query. In that case, the Execution Engine adds the clause ORDER BY to the query pushed down to the databases.

If the property “Supports binary ORDER BY collation” is no in at least one data source involved in the query, the Execution Engine does not select the merge method to perform the join. The reason is that Virtual DataPort needs to obtain the data from the database sorted with a binary collation.

If the default value of this property is no, do not set it to yes. If the default value is no, it means that the source is not capable of sorting the data using a binary collation.
Delegate ORDER BY collation modifier (DELEGATE_ORDERBY_COLLATION_MODIFIER): if yes, the ORDER BY clause is pushed down with a collation modifier. If no, it is pushed down without any modifier.

For instance, by default, the clause ORDER BY <field of type text> is pushed down to Oracle with the modifier NLSSORT. E.g.,
```
SELECT ...
FROM ...
ORDER BY NLSSORT( <field of type text>, 'NLS_SORT = binary') ASC
```
If this property is no, ORDER BY is pushed down without this modifier. E.g.,
```
SELECT ...
FROM ...
ORDER BY column1 ASC, column2 ASC
```
If the default value of this property is yes, setting it to no may lead merge joins that obtain data from this source to return incorrect results. The reason is that the merge join algorithm expects the input data to be sorted with a binary collation.

Only set this property to no if the collation modifier is hurting the performance of the query and the collation used by the database sorts the data in the same way as the collation that Virtual DataPort tries to use when this property is yes.

Do not set this property to yes if its default value no. If the default value is no, it means that the source is not capable of sorting the data using a binary collation.
Delegate binary ORDER BY collation (DELEGATE_BINARY_ORDERBY_COLLATION): for JDBC data sources that have a default value for this property, you can change it. However, the default collation set for each adapter performs a binary collation, so you should not modify it.

If this property does not have a default value, setting a value for this property does not have any effect.

The default value of these three properties is different depending on the database adapter of the JDBC data source.

These properties only affect queries with an ORDER BY of text fields. When sorting by other types of values, they are not important because there are not different ways of sorting long or int values for example.

USER MANUALS

Data Source Configuration Properties¶

ORDER BY Properties of the Source Configuration¶