OPTION Clause

You are looking at an older version of the documentation. The latest version is found here.

The OPTION keyword denotes options which can be passed with the command. These options are specific to the Data Virtuality Server and not covered by any SQL specification.

Usage

SQL

OPTION option (option)*

Supported Options

To view the full table, click the expand button in its top right corner

Option	Usage	Description
`MAKEDEP`	`MAKEDEP table [(,table)*]`	Specifies source tables that should be made dependent in the join
`MAKENOTDEP`	`MAKENOTDEP table [(,table)*]`	Prevents a dependent join from being used
`NOCACHE`	`NOCACHE [table (,table)*]`	Prevents cache from being used for all tables or the given tables. All tables specified in the `OPTION` clause should be fully qualified
`$NOOPT`	`$NOOPT [FALSE \| TRUE]`	If `TRUE` (or if the modifier is omitted), no recommended optimizations are created for queries. Already created optimizations will still be used. Default is `FALSE`
`$NOFAIL`	`$NOFAIL [FALSE \| TRUE]`	Prevents a procedure from throwing an error in case of failure. The error will be silently ignored. Processed only when applied to: `createCopyOverSQLJob()` `createCopyOverSourceTableJob()` `createGatherStatisticsJob()` `createOptimizationJob()` `createincrementaloptimizationjob()` `createSQLJob()` `setCurrentDwh()` `setRemark()` The Data Virtuality Exporter adds "`OPTION $NOFAIL`" for exported calls to: `setRemark()`
`$PREFER_DWH`	`$PREFER_DWH (FORCE \| ALWAYS \| CONDITIONAL \| NEVER)`	`FORCE` (default value): analytical storage optimizations are applied in any case. If optimization is enabled, but no materialized tables can be used for it (e.g. materialized table not found in the analytical storage), an exception is thrown; `ALWAYS`: analytical storage optimizations are applied whenever possible. If optimization is enabled and the corresponding materialized table is available in the analytical storage, such a materialization will be used; if the materialized table is not available, the query is automatically redirected to the source; if both source and materialized tables are not available then an exception is thrown; `CONDITIONAL`: the join queries directed to the same data source will be calculated by the original data source system, even if some of the tables (`tableA` or `tableB` or both) are already pre-materialized in the analytical storage (original data is preferred to analytical storage to speed up the join); `NEVER`: optimizations will not be applied under any circumstances Please note that if the join is already materialized, it is served from the analytical storage, regardless of the option value
`$ALLOW_CARTESIAN`	`$ALLOW_CARTESIAN (NEVER \| IMPLICIT \| ALWAYS)`	`NEVER`: never do cartesian product `IMPLICIT` (default value): only allow cartesian if implicitly generated by the query processor; the query may not contain explicit joins, but in some cases for complex queries, when a pair of `ACCESS` nodes do not have common `SELECT` criteria, the query processor may generate a cross join `ALWAYS`: always allow cartesian
`$ALLOW_XML_TEXT_NODE_WHITESPACE`	`$ALLOW_XML_TEXT_NODE_WHITESPACE [FALSE \| TRUE]`	XMLQuery, XMLTable streaming mode specific option. Allows handling elements that contain only whitespaces. Elements that contain only whitespaces are handled as empty if this option is not enabled

INTERNAL and EXPLICIT values of the $ALLOW_CARTESIAN option have been removed in v2.4.15

IMPLICIT is the new default value of the $ALLOW_CARTESIAN option since v2.4.15

Examples

$NOFAIL

SQL

CALL "SYSADMIN.setRemark"("name" => 'views.doesnotexist', "remark" => 'this is a new comment') OPTION $NOFAIL;;

$PREFER_DWH

Example for join within the same schema (data source):

SQL

SELECT <sth.> FROM schemaA.tableA <some_join> schemaA.tableB ON <some_condition> ...

$ALLOW_CARTESIAN

Example of queries with an explicit cross join (works when the $ALLOW_CARTESIAN option is set to ALWAYS):

SQL

SELECT * FROM SchemaA.TableA, SchemaB.TableB
SELECT * FROM SchemaA.TableA CROSS JOIN SchemaB.TableB

Example of a query which results in an implicit cross join:

SQL

SELECT * FROM (EXEC file.getFiles('1.csv')) f, TEXTTABLE(TO_CHARS(f.file,'ISO-8859-15') COLUMNS ...)

Example of a query which results in an implicit cross join:

SQL

SELECT 	CAST((SUM((CASE WHEN (("order_position"."state" = 512) OR ("order_position"."state" = 1024)) THEN 1 ELSE 0 END)) - SUM((CASE WHEN ("order_position"."state" = 512) THEN 1 ELSE 0 END))) AS FLOAT) AS "usr_Retourenquote_St_ck_Kopie_qk"
FROM "test_dvcore301_1"."public.order_position" "order_position"
INNER JOIN "test_dvcore301_2"."public.customer_order" "customer_order" ON ("order_position"."order_id" = "customer_order"."id")
INNER JOIN "test_dvcore301_1"."public.address" "address" ON ("customer_order"."shipping_address_id" = "address"."id")
INNER JOIN "test_dvcore301_2"."public.supplier_article" "supplier_article" ON ("order_position"."supplier_article_id" = "supplier_article"."id")
INNER JOIN "test_dvcore301_2"."public.article" "article" ON ("order_position"."article_id" = "article"."id")
INNER JOIN "test_dvcore301_2"."public.article_details" "article_details" ON ("order_position"."supplier_article_id" = "article_details"."id")
INNER JOIN "test_dvcore301_2"."public.principal" "principal" ON ("customer_order"."customer_id" = "principal"."id")
INNER JOIN "test_dvcore301_2"."public.principal" "stylist" ON ("customer_order"."stylelist_id" = "stylist"."id")
WHERE ((("article_details"."category" IS NULL) OR (("article_details"."category" >= '') AND
("article_details"."category" <= 'Some Category'))) AND (("article_details"."season" IS NULL)
OR (("article_details"."season" >= 'Season') AND ("article_details"."season" <= 'Season2')))
AND (("customer_order"."date_shipped" >= {ts '2012-02-02 18:00:00'}) AND ("customer_order"."date_shipped" <= {ts '2013-02-14 17:27:08.222000'}))) HAVING (COUNT(1) > 0)
OPTION $NOOPT

$ALLOW_XML_TEXT_NODE_WHITESPACE

Examples of an XMLTABLE query with the option enabled:

SQL

SELECT
   xt.NAME,
   xt.SURNAME,
   LENGTH(xt.SURNAME) AS SURNAME_LENGTH
FROM XMLTABLE(
       '/root/band_members/band_member'
       PASSING XMLPARSE(
       DOCUMENT
           '<root>
               <band_members>
                   <band_member>
                       <name>George</name>
                       <surname> </surname>
                   </band_member>
                   <band_member>
                       <name>Ringo</name>
                       <surname></surname>
                   </band_member>
               </band_members>
           </root>'
   )
   COLUMNS
       NAME STRING PATH 'name',
       SURNAME STRING PATH 'surname'
) xt
OPTION $ALLOW_XML_TEXT_NODE_WHITESPACE;;
-- returns:
/*
NAME	SURNAME	SURNAME_LENGTH
George	 	1
Ringo		0
*/

Previous versions of the Data Virtuality Server accepted the PLANONLY, DEBUG, and SHOWPLAN option arguments. These are no longer accepted in the OPTION clause.

In addition to providing an option for an SQL statement, it is possible to set it globally. Please see the Controlling Data Virtuality Server Behaviour chapter for more details.