4.9 Native Semantics

Some of the advanced SQL constructs that are supported by Oracle database may not be supported in the same manner by the DRDA database.

In this case, the Oracle database compensates for the missing or incompatible functionality by post-processing the DRDA database data with Oracle database functionality

See Also:

Oracle Database SQL Construct Processing for more information

This feature provides maximum transparency, but may impact performance. In addition, new versions of a particular DRDA database may implement previously unsupported functions or capabilities, or they may change the supported semantics as to make them more compatible with Oracle database functions.

Some of DRDA servers also provide support for user-defined functions. The user may choose to implement Oracle database functions natively in the DRDA database. This enables the DRDA server to pass the function to the underlying database implementation (for example, DB2). Native Semantics provides a method of enabling specific capabilities to be processed natively by the DRDA server.

Native Semantics Considerations

Various considerations must be taken into account when enabling the Native Semantic feature of a particular function because Native Semantics has advantages and disadvantages, which are typically a trade-off between transparency and performance.

• One such consideration is the transparency of data coercions. Oracle database provides coercion (implicit data conversion) for many SQL functions. This means that if the supplied value for a particular function is not correct, then Oracle database will coerce the value (change it to the correct value type) before processing it. However, with the Native Semantic feature enabled, the value, exactly as provided, will be passed to the DRDA server for processing. In many cases, the DRDA server will not be able to coerce the value to the correct type and will generate an error.
• Another consideration involves the compatibility of parameters to a particular SQL function. For instance, Oracle database implementation of SUBSTR allows negative values for the string index, whereas most DRDA server implementations of SUBSTR do not allow negative values for the string index. However, if the application is implemented to invoke SUBSTR in a manner that is compatible with the DRDA server, then the function will behave the same in either Oracle database or the DRDA server.
• Another consideration is that the processing of a function at the DRDA server may not be desirable due to resource constraints in that environment.

Refer to the HS_FDS_CAPABILITY for details on enabling or disabling these capabilities. Refer to the Oracle Database SQL Language Reference for Oracle database format of the following capabilities.

• SQL Functions That Can Be Enabled
  This topic lists the SQL functions that can be enabled.
• SQL Functions That Can Be Disabled
  This topic describes SQL functions that can be disabled.
• SQL Set Operators and Clauses
  The WHERE and HAVING clauses are compatible for all versions of the DRDA server.

4.9.1 SQL Functions That Can Be Enabled

This topic lists the SQL functions that can be enabled.

The following list contains SQL functions that are disabled (OFF) by default. They can be enabled (turned ON) as an option:

ABS	ACOS
ASCII	ASIN
ATAN	ATAN2
BITAND	CAST
CEIL	CHR
CONVERT	COS
COSH	COUNTCOL
DECODE	DUMP
EXP	FLOOR
GREATEST	HEXOTRAW
INITCAP	INSTR
INSTRB	LEAST
LENGTH	LENGTHB
LN	LOG
LOWER	LPAD
LTRIM	MOD
NLS_INITCAP	NLS_UPPER
NLS_LOWER	NLSSORT
NVL2	POWER
RAWTOHEX	REPLACE
REVERSE	ROUND
RPAD	RTRIM
SIGN	SIN
SINH	SQRT
STDDEV	SUBSTR
SUBSTRB	TAN
TANH	TO_NUMBER
TRANSLATE	TRIM
TRUNC	UPPER
VARIANCE	VSIZE

4.9.2 SQL Functions That Can Be Disabled

This topic describes SQL functions that can be disabled.

The following SQL functions are enabled (ON) by default:

• GROUPBY
• HAVING
• ORDERBY
• WHERE

ORDERBY controls sort order, which may differ at various sort locations. For example, with ORDERBY ON, a DB2 sort would be based on Extended Binary Coded Decimal Interchange Code (EBCDIC) sorting order, whereas with ORDERBY OFF, an Oracle database sort would be based on ASCII sorting order.

The other three functions, GROUPBY, HAVING, and WHERE, can take additional processing time. If you need to minimize the use of expensive resources, then you should choose the settings of these functions so that the processing is performed with cheaper resource. The above listed functions can also be disabled.

4.9.3 SQL Set Operators and Clauses

The WHERE and HAVING clauses are compatible for all versions of the DRDA server.

This means that these clauses are passed unchanged to the DRDA server for processing. Whether clauses GROUP BY and ORDER BY are passed to the DRDA server, or compensated by Oracle database, is determined by the Native Semantics Parameters (see the previous section).

The set operators UNION and UNION ALL are compatible for all versions of the DRDA server, meaning that they are passed unchanged to the DRDA server for processing. The set operators INTERSECT and MINUS are compensated on all versions of the DRDA server except DB2/UDB. For DB2/UDB, INTERSECT is compatible and MINUS is translated to EXCEPT.