This appendix provides the details of Oracle Data Service Integrator core support and base support for relational data, and includes these topics:
Each section that follows includes information about:
The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for IBM DB2/NT 8.
Table G-1 lists supported data type mappings for IBM DB2/NT 8.
CLOB1
|
|
DECIMAL(p,s)2 (NUMERIC)
|
|
LONG VARCHAR1
|
|
TIME3
|
xs:time4
|
TIMESTAMP5
|
xs:dateTime4
|
xs:string4
|
|
1Pushed down in project list only. 2Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point). 3Accurate to 1 second. 4Values converted to local time zone (timezone information removed) due to TIME and TIMESTAMP limitations. See “Date and Time Data Type Differences: Timezones and Time Precision” on page 3-6 for more information. 5Precision limited to milliseconds. |
Table G-2 lists functions and operators that are pushed down to IBM DB2/NT8 RDBMSs. See
“fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like()
function.
+, -, *, div, idiv1
|
|
mod2
|
|
Numeric comparisons1
|
|
String comparisons3
|
|
empty, exists, subsequence7
|
1All numeric types. 2xs:integer (and subtypes) only. 3Arguments must have SQL data type CHAR or VARCHAR. 4If second and third arguments are types xs:double or xs:float, they cannot be parameters. 5Second argument must be a constant or a parameter. 6Argument must be SQL data type CHAR or VARCHAR. 7Both two- and three-argument variants supported. |
Table G-3 lists supported cast operations.
Table G-4 lists common query patterns that can be pushed down. See also “Common Query Patterns”.
The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Microsoft SQL Server 2000.
Table G-5 lists supported data type mappings for Microsoft SQL Server 2000.
DATETIME1
|
xs:dateTime2
|
DECIMAL(p,s)3 (NUMERIC)
|
|
NTEXT4
|
|
SMALLDATETIME5
|
|
TEXT4
|
|
1Fractional-second-precision up to 3 digits (milliseconds). No timezone. 2Values converted to local time zone (timezone information removed) and fractional seconds truncated to milliseconds due to DATETIME limitations. See “Date and Time Data Type Differences: Timezones and Time Precision” on page 3-6 for more information. 3Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point). 4Pushed down in project list only. 5Accuracy of 1 minute. |
Additionally, the following XQuery data types can be passed as parameters or returned by pushed functions:
Table G-6 lists functions and operators that are pushed down to Microsoft SQL Server 2000. (See
“fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like()
function.)
+, -, *, div, idiv1
|
|
mod2
|
|
Numeric comparisons1
|
|
String comparisons3
|
|
year-from-dateTime, year-from-date, years-from-duration, month-from-dateTime, month-from-date, months-from-duration, day-from-dateTime, day-from-date, days-from-duration, hours-from-dateTime, hours-from-duration, minutes-from-dateTime, minutes-from-duration, seconds-from-dateTime, seconds-from-duration, fn-bea:date-from-dateTime
|
|
op:add-yearMonthDurations, op:add-dayTimeDurations, op:subtract-yearMonthDurations, op:subtract-dayTimeDurations, op:multiply-yearMonthDuration, op:multiply-dayTimeDuration, op:divide-yearMonthDuration, op:divide-dayTimeDuration, subtract-dateTimes-yielding-yearMonthDuration, subtract-dateTimes-yielding-dayTimeDuration, op:add-yearMonthDuration-to-dateTime, op:add-dayTimeDuration-to-dateTime, op:subtract-yearMonthDuration-from-dateTime, op:subtract-dayTimeDuration-from-dateTime, subtract-dates-yielding-yearMonthDuration, subtract-dates-yielding-dayTimeDuration, op:add-yearMonthDuration-to-date, op:add-dayTimeDuration-to-date, op:subtract-yearMonthDuration-from-date, op:subtract-dayTimeDuration-from-date
|
|
empty, exists, subsequence6
|
1For all numeric types 2For xs:integer and its subtypes only. 3Arguments must be of SQL data type CHAR, NCHAR, VARCHAR, or NVARCHAR. 4Both the 2-argument and 3-argument versions of function supported. 5Second argument must be SQL data type CHAR, NCHAR, VARCHAR, or NVARCHAR. 6Only the three-argument variant of fn:subsequence is supported, with the additionl requirement that the $startingLoc must be 1 (constant) and $length must be xs:integer type. |
Table G-7 lists supported cast operations.
xs:double1
|
|
1Source SQL type must be CHAR, NCHAR, VARCHAR, or NVARCHAR. |
Table G-8 lists common query patterns that can be pushed down. (See “Common Query Patterns” for details.)
The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Oracle 8.1.x (Oracle 8i).
Table G-9 lists supported data type mappings for Oracle 8.1.x (Oracle 8i).
CLOB1
|
|
DATE2
|
|
LONG1
|
|
NCLOB1
|
|
NUMBER(p,s)3
|
|
1Pushed down in project list only. 2Does not support fractional seconds. 3Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point). |
Additionally, the following XQuery data types can be passed as parameters or returned by pushed functions:
Table G-10 lists functions and operators that are pushed down. See
“fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like()
function.
Numeric arithmetic1
|
|
Numeric comparisons1
|
|
String comparisons2
|
|
op:add-yearMonthDurations, op:subtract-yearMonthDurations, op:multiply-yearMonthDuration, op:divide-yearMonthDuration, subtract-dateTimes-yielding-yearMonthDuration, op:add-yearMonthDuration-to-dateTime, op:subtract-yearMonthDuration-from-dateTime, subtract-dates-yielding-yearMonthDuration, op:add-yearMonthDuration-to-date, op:subtract-yearMonthDuration-from-date
|
|
empty, exists, subsequence6
|
1For all numeric types. 2Arguments must be of SQL data type CHAR, NCHAR, NVARCHAR2, or VARCHAR2. 3Empty input (NULL) handling deviates from XQuery semantics—returns empty sequence (instead of empty string). 4Argument must be data type CHAR, NCHAR, NVARCHAR2, or VARCHAR2. 5Second argument must be data type CHAR, NCHAR, NVARCHAR2, or VARCHAR2. 6Both two- and three-argument variants of fn:subsequence() are supported without restriction. |
Table G-11 lists supported cast operations.
xs:decimal1
|
|
xs:integer1
|
|
xs:float1
|
|
xs:double1
|
|
1Source data type must be CHAR, NCHAR, NVARCHAR2, or VARCHAR2. |
Table G-12 lists common query patterns that can be pushed down. See “Common Query Patterns” for details.
The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Oracle 9.x (Oracle 9i) and Oracle 10.x (Oracle 10g). Note that Oracle treats empty strings as NULLs, which deviates from XQuery semantics and may lead to unexpected results for expressions that are pushed down.
Table G-13 lists supported data type mappings for Oracle 9.x and 10.x.
CLOB1
|
|
xs:dateTime2
|
|
LONG1
|
|
NCLOB1
|
|
xs:dateTime3
|
|
1Pushed down in project list only. 2When SDO stores xs:dateTime value in Oracle DATE type, it is converted to local time zone and fractional seconds are truncated due to DATE limitations. See “Date and Time Data Type Differences: Timezones and Time Precision” on page 3-6 for more information. 3XQuery engine maps XQuery xs:dateTime to either TIMESTAMP or TIMESTAMP WITH TIMEZONE data type, depending on presence of timezone information. Storing xs:dateTime using SDO may result in loss of precision for fractional seconds, depending on the SQL type definition. |
Additionally, these XQuery data types can be passed as parameters or returned by pushed functions:
Table G-14 lists functions and operators that are pushed down to Oracle 9.x and 10.x. See
“fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like()
function.
Numeric arithmetic1
|
|
Numeric comparisons1
|
|
String comparisons2
|
|
year-from-dateTime, year-from-date, years-from-duration, month-from-dateTime, month-from-date, months-from-duration, day-from-dateTime, day-from-date, days-from-duration, hours-from-dateTime, hours-from-duration, minutes-from-dateTime, minutes-from-duration, seconds-from-dateTime, seconds-from-duration, fn-bea:date-from-dateTime
|
|
op:add-yearMonthDurations, op:add-dayTimeDurations, op:subtract-yearMonthDurations, op:subtract-dayTimeDurations, op:multiply-yearMonthDuration, op:multiply-dayTimeDuration, op:divide-yearMonthDuration, op:divide-dayTimeDuration, subtract-dateTimes-yielding-yearMonthDuration, subtract-dateTimes-yielding-dayTimeDuration, op:add-yearMonthDuration-to-dateTime, op:add-dayTimeDuration-to-dateTime, op:subtract-yearMonthDuration-from-dateTime, op:subtract-dayTimeDuration-from-dateTime, subtract-dates-yielding-yearMonthDuration, subtract-dates-yielding-dayTimeDuration, op:add-yearMonthDuration-to-date, op:add-dayTimeDuration-to-date, op:subtract-yearMonthDuration-from-date, op:subtract-dayTimeDuration-from-date
|
|
empty, exists, subsequence6
|
1For all numeric types 2Arguments must be of SQL type (N)CHAR or (N)VARCHAR2 3Empty input (NULL) handling deviates from XQuery semantics—returns empty sequence (instead of empty string). 4Argument must be CHAR, CLOB, NCHAR, NVARCHAR2, or VARCHAR2 data type. 5Second argument must be CHAR, NCHAR, NVARCHAR2, or VARCHAR2 data type. 6Both two- and three-argument variants of fn:subsequence() are supported without restriction. |
Table G-15 lists cast operations that can be pushed down.
xs:decimal1
|
|
xs:dateTime2
|
1Source SQL type must be CHAR, NCHAR, VARCHAR2, or NVARCHAR2. 2Source SQL type must be DATE or TIMESTAMP to achieve this mapping. |
Table G-16 lists common query patterns that can be pushed down. (See “Common Query Patterns” for details.)
The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Sybase 12.5.2 (and higher).
As you read through the tables in this section, be aware that Sybase deviates from XQuery semantics (which ignores empty strings) and treats empty strings as a single-space string.
Table G-17 lists supported data type mappings for Sybase 12.5.2.
DATETIME1
|
xs:dateTime2
|
DECIMAL(p,s)3 (NUMERIC)
|
|
SMALLDATETIME4
|
|
TEXT5
|
|
1Supports fractional seconds up to 3 digits (milliseconds) precision; no timezone information. 2Values converted to local time zone (timezone information removed) and fractional seconds truncated to milliseconds due to DATETIME limitations. See “Date and Time Data Type Differences: Timezones and Time Precision” on page 3-6 for more information. 3Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point). 4Accurate to 1 minute. 5Expressions returning text are pushed down in the project list only. |
Additionally, the following data types can be passed as parameters or returned by pushed functions:
See “Datetime arithmetic” in Table for details.
Table G-18 lists functions and operators that are pushed down to base RDBMSs. (See
“fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like( )
function.)
+, -, *, div 1
|
|
idiv2
|
|
mod3
|
|
Numeric comparisons1
|
|
String comparisons4
|
|
year-from-dateTime, year-from-date, years-from-duration, month-from-dateTime, month-from-date, months-from-duration, day-from-dateTime, day-from-date, days-from-duration, hours-from-dateTime, hours-from-time, hours-from-duration, minutes-from-dateTime, minutes-from-time, minutes-from-duration, seconds-from-dateTime, seconds-from-time, seconds-from-duration, fn-bea:date-from-dateTime, fn-bea:time-from-dateTime
|
|
op:add-yearMonthDurations, op:subtract-yearMonthDurations, op:multiply-yearMonthDuration, op:divide-yearMonthDuration, op:add-dayTimeDurations, op:subtract-dayTimeDurations, op:multiply-dayTimeDuration, op:divide-dayTimeDuration, op:add-yearMonthDuration-to-dateTime, op:add-yearMonthDuration-to-date, op:subtract-yearMonthDuration-from-dateTime, op:subtract-yearMonthDuration-from-date, op:add-dayTimeDuration-to-dateTime, op:add-dayTimeDuration-to-date, op:subtract-dayTimeDuration-from-dateTime, op:subtract-dayTimeDuration-from-date, fn:subtract-dateTimes-yielding-yearMonthDuration, fn:subtract-dates-yielding-yearMonthDuration, fn:subtract-dateTimes-yielding-dayTimeDuration, fn:subtract-dates-yielding-dayTimeDuration
|
|
1All numeric types (+, -, *, div operators are pushed down for all numeric types). 2xs:decimal (and subtypes) only 3xs:integer (and subtypes) only 4Arguments must be SQL data type CHAR, NCHAR, NVARCHAR, or VARCHAR. 5Each argument must be SQL data type CHAR, NCHAR, NVARCHAR, or VARCHAR. 6Second argument must be constant or SQL parameter. |
The Table G-19 lists supported cast operations.
xs:double1
|
|
1Source SQL type must be (N)CHAR or (N)VARCHAR
|
Table G-20 lists common query patterns that can be pushed down. See “Common Query Patterns” for details.
The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for PointBase 5.1.
Table G-21 lists supported data type mappings for PointBase 5.1.
DECIMAL(p,s)1 (NUMERIC)
|
|
1Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point). |
Table G-22 lists functions and operators that are pushed down to PointBase. See
“fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like()
function.
Numeric arithmetic1
|
|
Numeric comparisons1
|
|
String comparisons2
|
|
1All numeric types 2CHAR or VARCHAR SQL data types only for arguments 3Second argument must be constant or parameter. |
Table G-19 lists supported cast operations.
xs:decimal1
|
|
xs:double1
|
|
xs:float1
|
|
xs:integer1
|
|
xs:long1
|
|
xs:int1
|
|
xs:short1
|
|
1Source SQL data type must be CHAR or VARCHAR |
Table G-24 lists common query patterns that can be pushed down. (See “Common Query Patterns” for details.)
The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Teradata V2R5 (and higher).
Table G-25 lists supported data type mappings for Teradata V2R5.
Table G-26 lists functions and operators that are pushed down to Teradata. See
“fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like()
function.
+, -, * 1
|
|
div2
|
|
Numeric comparisons1
|
|
String comparisons3
|
|
1All numeric types 2Only xs:decimal, xs:float, and xs:double 3CHAR or VARCHAR SQL data types only for all arguments 4CHAR or VARCHAR SQL data type only for first argument 5First argument must be CHAR or VARCHAR SQL data type, second argument must be a constant or parameter |
Cast operations are not pushed down.
Table G-27 lists common query patterns that can be pushed down. (See “Common Query Patterns” for details.)
Each JDBC drivers provide information about inherent properties and capabilities of the RDBMS with which it is associated. During the metadata import process, Oracle Data Service Integrator queries a configured data source’s JDBC driver for basic properties and capabilities information. Much of the information obtained is stored in the metadata section of the data service definition file (.ds). See “ Understanding Data Service Annotations” in the Data Services Developer’s Guide for more information.
The database capabilities listed in Table G-28 are obtained from the operative JDBC driver and stored as properties in the .ds
(data service) definition file.
The Oracle Data Service Integrator XQuery engine typically quotes the names (identifiers) of object names to properly handle any special characters. The identifierQuote property (see Table ) is obtained from the JDBC driver. However, different RDBMSs may use different identifiers for different database object names:
If necessary, you can manually override the identifier quote property for each type of identifier (see Table ).
Typically, the identifierQuote property obtained from the JDBC driver is used. However, if the specific quote property is available and the RDBMS uses it, you can modify the annotation settings in the .ds file (see “ Understanding Data Service Annotations” in the Data Services Developer’s Guide for more information about these properties). The XQuery engine (metadata importer sub-system) uses the specific quote property (see Table G-29) if it is available, otherwise, it uses the “identifierQuote” property provided by the JDBC driver.
The only exception to this rule is for Sybase versions below Sybase 12.5.2, which is treated as a base platform. Sybase does not use quotes for catalogs even though JDBC drivers return double quote ('"') for “identifierQuote” property. The XQuery engine accommodates this mismatch by automatically setting “catalogQuote” property to the empty string.
When mapping SQL to XQuery datatypes, the XQuery engine first checks the JDBC typecode. If the typecode has a corresponding XQuery type, Oracle Data Service Integrator uses the matching native type name. If no matching typecode or type name is available, the column is ignored. Table G-30 shows this mapping.
CLOB1
|
||
xs:date2
|
||
DECIMAL (p,s)3
|
||
LONGVARCHAR1
|
||
NUMERIC (p,s)3
|
||
TIME4
|
xs:time4
|
|
TIMESTAMP4
|
xs:dateTime2
|
|
1Pushed down in project list only. 2Values converted to local time zone (timezone information removed) due to DATE limitations. See “Date and Time Data Type Differences: Timezones and Time Precision” on page 3-6 for more information. 3Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point). 4Precision of underlying RDBMS determines the precision of TIME data type and how much truncation, if any, will occur in translating xs:time to TIME. |
Table G-31 lists functions and operators that are pushed down to base RDBMSs. See
“fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like()
function.
+, -, *1
|
|
div2
|
|
Numeric comparisons1
|
|
String comparisons3
|
|
1All numeric types 2Support for xs:decimal, xs:float, and xs:double data types only. 3Arguments must be CHAR or VARCHAR SQL data types. 4First argument must be SQL data type CHAR or VARCHAR; second argument must be a constant or parameter; and RDBMS must support LIKE (with ESCAPE) clause. |
For base RDBMS, cast operations are not pushed down.
Table G-32 displays other SQL Pushdown capabilities, as discussed in “Common Query Patterns” on page 3-15.