Using Option Declarations and Extension Expressions

---

Option declarations provide parameters that modify how DataDirect XQuery processes queries. Extension expressions are syntactical XQuery constructs that modify how DataDirect XQuery processes expressions in a query.

Option Declarations

Option declarations provide parameters that modify how DataDirect XQuery processes queries. They are similar to extension expressions, which modify how DataDirect XQuery processes expressions in a query.

There are three types of option declarations:

Global
Relational
Database-specific

Global Option Declarations

A global option declaration is used as the default for all XML and relational data sources accessed by XQuery queries in your Java application. DataDirect XQuery supports the following global option declarations:

automatic-update
detect-XPST0005
ignore-whitespace
plan-explain
serialize
xml-streaming

Table 12-1. Global Option Declarations  

Option Declaration	Description
automatic-update	{yes | no}. The ddtek:automatic-update option allows XQuery to automatically update data sources that are accessed through doc and collection functions. Relational data sources cannot be updated using automatic update. Data sources affected by XUF update expressions are physically modified at the end of the XQuery execution. See Updating Data Sources for more information.
detect-XPST0005	{yes | no}. Determines whether err:XPST0005 is raised during static analysis. If set to yes (the default), the error message err:XPST0005 is raised if the static data type assigned to an XQuery expression other than the expression () or data(()) is void(). For example, this error is raised if DataDirect XQuery can statically determine in a NameTest that a path expression can never match a node, for example, because the name of an element is misspelled or a specified column does not exist in the table. If set to no, err:XPST0005 is not raised during static analysis and the expression is evaluated at runtime.
ignore-whitespace	{yes | no}. Gives DataDirect XQuery the ability to ignore ignorable whitespace (tabs, linefeeds, carriage returns, and spacebar spaces) when parsing XML documents. If set to no (the default), ignorable whitespace is preserved.
plan-explain	'format=xhtml|xml[,resourceLocation=path]'. Determines whether an execution plan is generated for the queries in your application. The value for path must be /install_dir/planExplain, where install_dir is the installation directory of DataDirect XQuery (for example, /DDXQ3_0/planExplain). When the Plan Explain feature is enabled, DataDirect XQuery generates an execution plan and does not execute the queries. See Generating XQuery Execution Plans for more information.
serialize	Controls the process of serializing the query results into XML, XHTML, or HTML notation as specified by XQuery 1.0: An XML Query Language, W3C Recommendation 23 January 2007 located at: http://www.w3.org/TR/2007/REC-xquery-20070123/ See Serialization Support for the serialization parameters that you can set using this option declaration.
xml-streaming	{yes | no}. Determines whether DataDirect XQuery uses Streaming XML when querying large XML documents. The Streaming XML technique reduces memory consumption. When set to yes, Streaming XML is enabled in the XML Adaptor. The default is yes. See Querying Large XML Documents for details.

Relational Option Declarations

Relational declarations control the processing of XQuery queries for connections to relational databases; they can be used with any relational database supported by DataDirect XQuery.

sql-decimal-cast
sql-extra-checks-trailing-spaces
sql-ignore-trailing-spaces
sql-order-by-on-values
sql-rewrite-algorithm
sql-simple-convert-functions
sql-simple-string-functions
sql-unicode-literals (deprecated)
sql-unicode-strings
sql-varchar-cast

Table 12-2. Relational Option Declarations  

Option Declaration	Description
sql-decimal-cast	precision,scale. Determines precision and scale used for xs:decimal(). If you do not specify a precision and scale, the following default values are used: DB2: 30, 15 Informix: 32, 15 MySQL: 64, 30 Oracle: No default Microsoft SQL Server: 38,19 PostgreSQL: No default Sybase: 38, 19 You can override the default by using this option declaration.
sql-extra-checks-trailing-spaces	{yes | no}. When set to yes, the SQL generated for the XQuery distinct-values functions does not ignore trailing spaces. The default is no.
sql-ignore-trailing-spaces	{yes | no}. When set to yes, the generated SQL clauses for string comparison ignore trailing spaces. The default is no.
sql-order-by-on-values	{yes | no | noSubquery}. When set to yes, expressions on which to sort are not explicitly added to the Select list of generated SQL Select statements. When set to no, values or expressions on which to sort are always added to the Select list, which typically decreases performance, but is required by some databases. When set to noSubquery, the behavior is equivalent to yes except when the expression on which to sort is a subquery. In this case, the value noSubquery behaves as if no is specified. The default value is database dependent: DB2: no Informix: noSubquery MySQL: yes Oracle: yes Microsoft SQL Server: yes PostgreSQL: yes Sybase: noSubquery See Using an Order By Clause for an example.
sql-rewrite-algorithm	{nested-loop | merge-join | outer-join | sorted-outer-union}. Specifies the SQL generation algorithm used by DataDirect XQuery when accessing a relational data source. See Using DataDirect XQuery SQL Generation Algorithms for descriptions of these algorithms and information about choosing the appropriate one. The default is merge-join.
sql-simple-convert-functions	{yes | no}. When set to yes, the generated convert functions do not consider all semantic differences between XQuery and SQL casts and do, in some cases, return incorrectly formatted string casts of numeric values—for example, leading zeroes in casts of decimal to string with DB2. The default is no.
sql-simple-string-functions	{yes | no}. When set to yes, the generated SQL string functions do not consider semantic differences between XQuery and SQL functions with regard to empty string or empty sequence (null) arguments. The default is no.
sql-unicode-literals DEPRECATED	This option declaration is recognized for backward compatibility, but we recommend that you use the sql-unicode-strings option declaration.
sql-unicode-strings	{yes | no}. Determines whether XQuery literals are translated to SQL literals escaped with the Alternate National Character Set escape character N. If set to yes, XQuery literals are translated to SQL literals escaped with the Alternate National Character Set escape character N. Set the value of this option declaration to yes when a SQL literal contains characters that cannot be translated to the code page of your database. Then, execute the query using this option declaration. This setting is useful when the database table has columns that can contain Unicode information (for example, nvarchar columns). If set to no (the default), XQuery literals are not translated. NOTE: Some databases are significantly slower than others when Unicode comparisons are performed.
sql-varchar-cast	precision. When multiple XQuery expressions are translated to equivalent SQL statements, a cast to varchar is required. This value defines the varchar precision. If you do not specify a precision, the following default values are used: DB2: 4088 Informix: 254 MySQL: 255 Oracle: 4000 Microsoft SQL Server: 8000 PostgreSQL: No default Sybase: 8000 You can override the default by using this option declaration.

Database-Specific Option Declarations

Database-specific option declarations control the processing of XQuery queries for a specific database. Connection-specific option declarations are:

sql-ora10-use-binary-float-double
sql-rewrite-exists-into-count
sql-sybase-temptable-index
sql-sybase-use-derived-tables

Table 12-3. Database-Specific Option Declarations  

Option Declaration	Description
sql-ora10-use-binary-float-double Connection-specific	{yes | no}. For Oracle 10g and higher. When set to yes, the BINARY_FLOAT and BINARY_DOUBLE data types are converted from XML floats and doubles. The default is no.
sql-rewrite-exists-into-count	{yes | no | inCase}. For DB2 only. The different DB2 databases impose limitations on the usage of the SQL EXISTS subclause. This option specifies whether to change an EXISTS subclause into a count() > 0 subclause. When set to no, EXISTS subclauses are not rewritten. When set to yes (the default for DB2 for z/OS), EXISTS subclauses are always rewritten. When set to inCase (the default for DB2 for iSeries), EXISTS subclauses in conditional expressions are rewritten. Typically, you should not change the default setting, but some XQuery expressions executed against DB2 for z/OS and DB2 for iSeries perform better when this option is set to no. In addition, if sql-order-by-on-values is set to noSubquery for DB2 for Linux/UNIX/Windows, you will get the best performance for the broadest set of queries if you set this option to inCase.
sql-sybase-temptable-index Connection-specific	{yes | no}. For Sybase only. Determines whether an index is created on the temporary table that is used when joining XML and relational data. When set to yes, DataDirect XQuery creates this index. Setting this option to yes has a positive impact on performance when hundreds of values are extracted from XML documents and joined with relational data. The default is no.
sql-sybase-use-derived-tables Connection-specific	{yes | no}. For Sybase only. Determines whether DataDirect XQuery generates SQL statements that use derived tables. Many XQuery expressions require the use of derived tables. However, derived tables are not fully supported in Sybase, and some valid SQL statements that use derived tables fail when executed against a Sybase server. Therefore, DataDirect XQuery does not generate SQL statements that use derived tables by default; instead, it creates temporary views, which can adversely affect performance. For some XQuery expressions, the generated SQL that uses derived tables does not fail and returns correct results. To use derived tables, set this option declaration to yes. When using derived tables, performance is improved.

Specifying an Option Declaration

You can specify an option declaration using either of the following methods:

Using the prolog of the XQuery query. The syntax for specifying an option declaration in a query is:

						  declare option ddtek:name_of_option_declaration 
"value"; 
 

For example:

						  declare option ddtek:sql-unicode-strings "yes"; 
 

NOTE: If the option declarations you specify in imported modules conflict with the option declarations you specify in the query or another imported module, DataDirect XQuery raises an error.

Using the properties of the DDXQDataSource and DDXQJDBCConnection class. To specify an option declaration as global, use the Options property of the DDXQDataSource class. To specify an option declaration as connection-specific, use the JdbcOptions property of the DDXQDataSource class or the Options property of the DDXQJDBCConnection class. See DDXQDataSource and DDXQJDBCConnection Properties.

In the following example, we specify detect-XPST0005=no as a global option declaration. It disables the XPST0005 error, which is raised during static analysis under certain conditions (as explained in Table 12-1). In addition, the example specifies sql-decimal-cast=20,15 as a connection-specific option declaration. It instructs DataDirect XQuery to process a query with a specific precision and scale for xs:decimal() values.

DDXQDataSource ds = new DDXQDataSource(); 
ds.setJdbcUrl("jdbc:xquery:sqlserver://server1:1433;databaseName=stocks"); 
ds.setOptions("detect-XPST0005=no"); 
ds.setJdbcOptions("sql-decimal-cast=20,15"); 
XQConnection conn = ds.getConnection("myuserid","mypswd"); 
XQExpression xqExpression = conn.createExpression(); 
FileReader fileReader = new FileReader("xquery_file.xq"); 
XQSequence xqSequence = xqExpression.executeQuery(fileReader); 

Using Extension Expressions

Extension expressions provide parameters that modify how DataDirect XQuery processes expressions in a query. You can specify extension expressions only in the body of a query. The only extension expression supported by DataDirect XQuery is evaluate-in-memory (as defined in Table 12-4).

The syntax for specifying an extension expression in a query is:

(# ddtek:name_of_extension_expression #) 

For example:

(# ddtek:evaluate-in-memory #) 

Suppose a user wants to perform data analysis using the following query, which accesses the historical database table and returns the ratio of the value of adjustedclose to the value of actualclose for a particular date.

for $h in collection('historical')/historical 
where $h/ticker = 'AMZN' 
return 
   <historical> 
   {$h/datetraded} 
   {$h/adjustedclose div $h/actualclose} 
   </historical> 

Suppose that actualclose is 0 for one or multiple dates because of a data entry error. In XQuery, division by 0 raises an error for decimal and integer data types, but not for float and double data types. The user can avoid an error by casting the ratio to a double data type and performing the division in memory by specifying the evaluate-in-memory extension expression for the division expression as shown in the following query:

for $h in collection('historical')/historical
where $h/ticker = 'AMZN' 
return 
   <historical>
   {$h/datetraded}
   {(# ddtek:evaluate-in-memory #)  
   {xs:double($h/adjustedclose) div $h/actualclose}}
   </historical> 

Table 12-4 provides a description of the evaluate-in-memory extension expression.

Table 12-4. Extension Expressions  

Extension Expressions

Description

evaluate-in-memory

Specifies an expression that is evaluated in memory as XQuery, ensuring that it will not be translated to SQL for evaluation in a relational data source. Use this extension expression for the following reasons: To ensure that DataDirect XQuery uses strictly conforming XQuery behavior when processing data from relational data sources. For relational data sources, DataDirect XQuery sometimes uses compensation to allow expressions to be evaluated efficiently (as described in Understanding Compensation). When strict conformance to the XQuery specification is more important than efficient data handling, use this extension expression. To provide XQuery functionality not typically provided for relational data sources. For example, use this extension expression to perform path expressions on XML stored in the database. This setting ensures the maximum XQuery conformance, but can significantly degrade performance depending on how it is used. For example, if used in a where clause of a FLWOR expression, it can force all rows of a database table to be evaluated in memory, which degrades performance. The expression used for evaluation in memory cannot contain the following functions: fn:collection, fn:doc and fn:doc-available.