The OPTION keyword denotes options the user can pass with the command. These options are specific for the Data Virtuality Server and not covered by any SQL specification.

Usage

OPTION option (option)*
SQL

Supported Options

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 for 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 an 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 then 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 | 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

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

$PREFER_DWH

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

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

$ALLOW_CARTESIAN

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

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

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

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

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

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
SQL

$ALLOW_XML_TEXT_NODE_WHITESPACE

Examples of am XMLTABLE query with option enabled:

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
*/
SQL