Skip to main content
Skip table of contents

OPTION Clause

The OPTION keyword denotes options the user can pass 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


OptionUsage Description
MAKEDEP MAKEDEP table [(,table)*]Specifies source tables that should be made dependent in the join
MAKENOTDEPMAKENOTDEP 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: FALSE
$NOFAIL$NOFAIL [FALSE | TRUE] 

Prevents a procedure from throwing an error in case of failure. 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, 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 | INTERNAL | IMPLICIT | EXPLICIT | ALWAYS)
  • NEVER: never do cartesian product;
  • INTERNAL (default value): never do cartesian product except for special procedural joins;
  • EXPLICIT: only allow if explicitly present in SQL query;
  • IMPLICIT: only allow 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 don't 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

Examples

$NOFAIL

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

$PREFER_DWH

In this example, the join is performed within the same schema (data source):

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

$ALLOW_CARTESIAN

1. Examples with a query with an explicit cross join:

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

2. Example of a query with an internal cross join:

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

3. 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 am 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
*/
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.