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:
Database Capabilities Information
Native RDBMS Data Type Support and XQuery Mappings
Function and Operator Pushdown
Cast Operation Pushdown
Other SQL Generation Capabilities (including join pushdown support and SQL syntax for joins)
The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for IBM DB2.
Table A-1 lists supported data type mappings for IBM DB2.
Table A-1 IBM DB2 Data Type Mappings
| DB2 Data Type | XQuery Type |
|---|---|
|
BIGINT |
xs:long |
|
BLOB |
xs:hexBinary |
|
CHAR |
xs:string |
|
CHAR() FOR BIT DATA |
xs:hexBinary |
CLOBFoot 1 |
xs:string |
|
DATE |
xs:date |
|
DOUBLE |
xs:double |
|
DECIMAL(p,s)Foot 2 (NUMERIC) |
xs:decimal (if s > 0), xs:integer (if s = 0) |
|
INTEGER |
xs:int |
|
LONG VARCHAR |
xs:string |
|
LONG VARCHAR FOR BIT DATA |
xs:hexBinary |
|
REAL |
xs:float |
|
SMALLINT |
xs:short |
|
TIMEFoot 3 |
xs:timeFoot 4 |
|
TIMESTAMPFoot 5 |
xs:dateTime |
|
VARCHAR |
xs:string |
|
VARCHAR() FOR BIT DATA |
xs:hexBinary |
Footnote 1 Pushed down in project list only.
Footnote 2 Where 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).
Footnote 3 Accurate to 1 second.
Footnote 4 Values converted to local time zone (timezone information removed) due to TIME and TIMESTAMP limitations. See Section 3.2.1, "Date and Time Data Type Differences: Timezones and Time Precision" for more information.
Footnote 5 Precision limited to milliseconds.
Table A-2 lists functions and operators that are pushed down to IBM DB2/NT8 RDBMSs. See Section 2.1.2.1, "fn-bea:is-access-allowed" for details about two-argument and three-argument versions of the fn-bea:sql-like() function.
Table A-2 IBM DB2 Functions and Operators
| Group | Functions and operators |
|---|---|
|
Logical operators |
and, or, not |
|
Numeric arithmetic |
+, -, *, div, idivFoot 1 |
|
modFoot 2 |
|
|
Numeric comparisons |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge |
|
Numeric functions |
abs, ceiling, floor, round |
|
String comparisonsFoot 3 |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge |
|
String functions |
concat, upper-case, lower-case, substring(2,3)Foot 4 , string-length, containsFoot 5 , starts-withFootref 5, ends-withFootref 5, fn-bea:sql-like(2,3) fn-bea:trimFoot 6 , fn-bea:trim-left, fn-bea:trim-right, fn-bea:repeat, fn-bea:pad-left, fn-bea:pad-right |
|
Datetime comparisons |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time |
|
Datetime functions |
year-from-dateTime, year-from-date, month-from-dateTime, month-from-date, day-from-dateTime, day-from-date, hours-from-dateTime, hours-from-time, minutes-from-dateTime, minutes-from-time, seconds-from-dateTime, seconds-from-time, fn-bea:date-from-dateTime, fn-bea:time-from-dateTime |
|
Aggregate |
min, max, sum, avg, count, count(distinct-values) |
|
Other |
empty, exists, subsequenceFoot 7 |
Footnote 1 All numeric types.
Footnote 2 xs:integer (and subtypes) only.
Footnote 3 Arguments must have SQL data type CHAR or VARCHAR.
Footnote 4 If second and third arguments are types xs:double or xs:float, they cannot be parameters.
Footnote 5 Second argument must be a constant or a parameter.
Footnote 6 Argument must be SQL data type CHAR or VARCHAR.
Footnote 7 Both two- and three-argument variants supported.
Table A-3 lists supported cast operations.
Table A-3 IBM DB2 Cast Operations
| Source XQuery Type | Target XQuery Type |
|---|---|
|
numeric |
xs:double |
|
numeric |
xs:float |
|
numeric |
xs:int |
|
numeric |
xs:integer |
|
numeric |
xs:short |
|
xs:decimal (and subtypes) |
xs:string |
|
xs:integer (and subtypes) |
xs:decimal |
|
xs:string |
xs:double |
|
xs:string |
xs:float |
|
xs:string |
xs:int |
|
xs:string |
xs:integer |
|
xs:string |
xs:short |
|
xs:dateTime |
xs:time |
Table A-4 lists common query patterns that can be pushed down. See also Section 3.3.7, "Common Query Patterns."
Table A-4 IBM DB2 Other SQL Generation Capabilities
| Feature | Description |
|---|---|
|
If-then-else |
yes |
|
Inner joins |
yes, SQL-92 syntax |
|
Outer joins |
yes, SQL-92 syntax |
|
Semi joins, Anti semi joins |
yes |
|
Order by |
yes |
|
Order by: Empty (NULL) order supported |
Fixed (always sorts NULLs high). Order-bys with "empty least" modifier (the XQuery default) are not pushed down. |
|
Order by: Aggregate function in ordering expression |
yes |
|
Group by |
yes |
|
Distinct pattern |
yes |
|
Trivial aggregate pattern |
yes (using GROUP BY constant) |
|
Direct SQL composition |
yes |
The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Microsoft SQL Server 2008 R2, or Microsoft SQL Server 2012.
Table A-5 lists supported data type mappings for Microsoft SQL Server.
Table A-5 SQL Server Data Type Mapping
| SQL Data Type | XQuery Type |
|---|---|
|
BIGINT |
xs:long |
|
BINARY |
xs:hexBinary |
|
BIT |
xs:boolean |
|
CHAR |
xs:string |
|
DATETIMEFoot 1 |
xs:dateTimeFoot 2 |
|
DECIMAL(p,s)Foot 3 (NUMERIC) |
xs:decimal (if s > 0), xs:integer (if s = 0) |
|
FLOAT |
xs:double |
|
IMAGE |
xs:hexBinary |
|
INTEGER |
xs:int |
|
MONEY |
xs:decimal |
|
NCHAR |
xs:string |
NTEXTFoot 4 |
xs:string |
|
NVARCHAR |
xs:string |
|
REAL |
xs:float |
|
SMALLDATETIMEFoot 5 |
xs:dateTime |
|
SMALLINT |
xs:short |
|
SMALLMONEY |
xs:decimal |
|
SQL_VARIANT |
xs:string |
|
TEXT |
xs:string |
|
TIMESTAMP |
xs:hexBinary |
|
TINYINT |
xs:short |
|
VARBINARY |
xs:hexBinary |
|
VARCHAR |
xs:string |
|
UNIQUIDENTIFIER |
xs:string |
Footnote 1 Fractional-second-precision up to 3 digits (milliseconds). No timezone.
Footnote 2 Values converted to local time zone (timezone information removed) and fractional seconds truncated to milliseconds due to DATETIME limitations. See Section 3.2.1, "Date and Time Data Type Differences: Timezones and Time Precision" for more information.
Footnote 3 Where 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).
Footnote 4 Pushed down in project list only.
Footnote 5 Accuracy of 1 minute.
Additionally, the following XQuery data types can be passed as parameters or returned by pushed functions:
xs:date (see Table A-6 for functions and operators that use xs:date). When xs:date is sent to the database, it is converted to local time zone. See Section 3.2.1, "Date and Time Data Type Differences: Timezones and Time Precision" for more information.
xdt:dayTimeDuration (see "Datetime Arithmetic" functions in Table A-6 for details).
xdt:yearMonthDuration (see "Datetime Arithmetic" functions in Table A-6 for details).
Table A-6 lists functions and operators that are pushed down to Microsoft SQL Server. (See Section 2.1.2.1, "fn-bea:is-access-allowed" for details about two-argument and three-argument versions of the fn-bea:sql-like() function.)
Table A-6 SQL Server Function and Operator Pushdown
| Group | Functions and Operators |
|---|---|
|
Logical operators |
and, or, not |
|
Numeric arithmetic |
+, -, *, div, idivFoot 1 |
|
modFoot 2 |
|
|
Numeric comparisonsFootref 1 |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge |
|
Numeric functions |
abs, ceiling, floor, round |
|
String comparisonsFoot 3 |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge |
|
String functions |
concat, upper-case, lower-case, substring(2,3)Foot 4 , string-length, containsFoot 5 , starts-withFootref 5, ends-withFootref 5, fn-bea:sql-like(2,3)Footref 4, fn-bea:trim, fn-bea:trim-left, fn-bea:trim-right, fn-bea:repeat, fn-bea:pad-left, fn-bea:pad-right |
|
Datetime comparisons |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xdt:yearMonthDuration, xdt:dayTimeDuration |
|
Datetime functions |
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 |
|
Datetime arithmetic |
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 |
|
Aggregate |
min, max, sum, avg, count, count(distinct-values) |
|
Other |
empty, exists, subsequenceFoot 6 |
Footnote 1 For all numeric types
Footnote 2 For xs:integer and its subtypes only.
Footnote 3 Arguments must be of SQL data type CHAR, NCHAR, VARCHAR, or NVARCHAR.
Footnote 4 Both the 2-argument and 3-argument versions of function supported.
Footnote 5 Second argument must be SQL data type CHAR, NCHAR, VARCHAR, or NVARCHAR.
Footnote 6 Only 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 A-7 lists supported cast operations.
Table A-7 SQL Server Cast Operations
| Source XQuery Data Type | Target XQuery Data Type |
|---|---|
|
numeric |
xs:string |
|
numeric |
xs:double |
|
numeric |
xs:float |
|
numeric |
xs:integer |
|
numeric |
xs:long |
|
numeric |
xs:int |
|
numeric |
xs:short |
|
xs:integer (and subtypes) |
xs:decimal |
|
xs:string |
xs:doubleFoot 1 |
|
xs:string |
xs:float |
|
xs:string |
xs:integer |
|
xs:string |
xs:long |
|
xs:string |
xs:int |
|
xs:string |
xs:short |
|
xs:dateTime |
xs:date |
|
xs:dateTime |
xs:string |
Footnote 1 Source SQL type must be CHAR, NCHAR, VARCHAR, or NVARCHAR.
Table A-8 lists common query patterns that can be pushed down. (See Section 3.3.7, "Common Query Patterns" for details.)
Table A-8 SQL Server Other SQL Generation Capabilities
| Feature | Description |
|---|---|
|
If-then-else |
yes |
|
Inner joins |
yes, SQL-92 syntax |
|
Outer joins |
yes, SQL-92 syntax |
|
Semi joins, Anti semi joins |
yes |
|
Order by |
yes |
|
Order by: Empty order (NULL order) |
fixed (always sorts NULLs low). Order-bys with "empty greatest" modifier are not pushed down. |
|
Order by: Aggregate function in ordering expression |
yes |
|
Group by |
yes |
|
Distinct pattern |
yes |
|
Trivial aggregate pattern |
yes (using subquery) |
|
Direct SQL composition |
yes |
The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Oracle 11.x, and Oracle 12.x. 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 A-9 lists supported data type mappings for Oracle 11.x and 12.x.
Table A-9 Oracle Data Type Mapping
| Oracle 9 Data Type | XQuery Type |
|---|---|
|
BFILE |
not supported |
|
BLOB |
xs:hexBinary |
|
CHAR |
xs:string |
CLOBFoot 1 |
xs:string |
|
DATE |
xs:dateTimeFoot 2 |
|
FLOAT |
xs:double |
|
INTERVAL DAY TO SECOND |
xdt:dayTimeDuration |
|
INTERVAL YEAR TO MONTH |
xdt:yearMonthDuration |
|
LONG |
xs:string |
|
LONG RAW |
xs:hexBinary |
|
NCHAR |
xs:string |
|
NCLOB |
xs:string |
|
NUMBER |
xs:double |
|
NUMBER(p,s) |
xs:decimal (if s > 0), xs:integer (if s <=0) |
|
NVARCHAR2 |
xs:string |
|
RAW |
xs:hexBinary |
|
ROWID |
xs:string |
|
TIMESTAMP |
xs:dateTimeFoot 3 |
|
TIMESTAMP WITH LOCAL TIMEZONE |
xs:dateTime |
|
TIMESTAMP WITH TIMEZONE |
xs:dateTime |
|
VARCHAR2 |
xs:string |
|
UROWID |
xs:string |
Footnote 1 Pushed down in project list only.
Footnote 2 When 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 Section 3.2.1, "Date and Time Data Type Differences: Timezones and Time Precision" for more information.
Footnote 3 XQuery 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:
xs:date (see Table A-10 for functions and operators that use xs:date)
xs:integer subtypes (see "Numeric ..." functions and operators in Table A-10 for details)
Table A-10 lists functions and operators that are pushed down to Oracle 11.x and 12.x. See Section 2.1.2.1, "fn-bea:is-access-allowed" for details about two-argument and three-argument versions of the fn-bea:sql-like() function.
Table A-10 Oracle Function and Operator Pushdown
| Group | Functions and Operators |
|---|---|
|
Logical operators |
and, or, not |
|
Numeric arithmeticFoot 1 |
+, -, *, div, idiv, mod |
|
Numeric comparisonsFootref 1 |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge |
|
Numeric functions |
abs, ceiling, floor, round |
|
String comparisonsFoot 2 |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge |
|
String functions |
concat, upper-caseFoot 3 , lower-caseFootref 3, substring(2,3)Footref 3, string-lengthFoot 4 , containsFoot 5 , starts-withFootref 5, ends-withFootref 5, fn-bea:sql-like(2,3), fn-bea:trim, fn-bea:trim-left, fn-bea:trim-right, fn-bea:repeat, fn-bea:pad-left, fn-bea:pad-right |
|
Datetime comparisons |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xdt:yearMonthDuration, xdt:dayTimeDuration |
|
Datetime functions |
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 |
|
Datetime arithmetic |
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 |
|
Aggregate |
min, max, sum, avg, count, count(distinct-values) |
|
Other |
empty, exists, subsequenceFoot 6 |
Footnote 1 For all numeric types
Footnote 2 Arguments must be of SQL type (N)CHAR or (N)VARCHAR2
Footnote 3 Empty input (NULL) handling deviates from XQuery semantics—returns empty sequence (instead of empty string).
Footnote 4 Argument must be CHAR, CLOB, NCHAR, NVARCHAR2, or VARCHAR2 data type.
Footnote 5 Second argument must be CHAR, NCHAR, NVARCHAR2, or VARCHAR2 data type.
Footnote 6 Both two- and three-argument variants of fn:subsequence() are supported without restriction.
Table A-11 lists cast operations that can be pushed down.
Table A-11 Oracle Cast Operation
| Source XQuery Type | Target XQuery Type |
|---|---|
|
numeric |
xs:string |
|
numeric |
xs:decimal |
|
numeric |
xs:integer |
|
numeric |
xs:float |
|
numeric |
xs:double |
|
xs:string |
xs:decimalFoot 1 |
|
xs:string |
xs:integer |
|
xs:string |
xs:float |
|
xs:string |
xs:double |
|
xs:dateTime |
xs:date |
|
xs:date |
xs:dateTimeFoot 2 |
Footnote 1 Source SQL type must be CHAR, NCHAR, VARCHAR2, or NVARCHAR2.
Footnote 2 Source SQL type must be DATE or TIMESTAMP to achieve this mapping.
Table A-12 lists common query patterns that can be pushed down. (See Section 3.3.7, "Common Query Patterns" for details.)
Table A-12 Oracle Other SQL Generation Capabilities
| Feature | Description |
|---|---|
|
If-then-else |
yes |
|
Inner joins |
yes, SQL-92 syntax |
|
Outer joins |
yes, SQL-92 syntax |
|
Semi joins, Anti semi joins |
yes |
|
Order by |
yes |
|
Order by: Empty order (NULL order) |
dynamic, no restriction on order by pushdown |
|
Order by: Aggregate function in ordering expression |
yes |
|
Group by |
yes |
|
Distinct pattern |
yes |
|
Trivial aggregate pattern pushdown |
yes (using GROUP BY constant) |
|
Direct SQL composition |
yes |
The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Sybase Adaptive Server Enterprise 15.7.
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 A-13 lists supported data type mappings for Sybase Adaptive Server Enterprise.
Table A-13 Sybase ASE Data Type Mapping
| Sybase Data Type | XQuery Type |
|---|---|
|
BINARY |
xs:hexBinary |
|
BIT |
xs:boolean |
|
CHAR |
xs:string |
|
DATE |
xs:date |
|
DATETIMEFoot 1 |
xs:dateTimeFoot 2 |
|
DECIMAL(p,s)Foot 3 (NUMERIC) |
xs:decimal (if s > 0), xs:integer (if s == 0) |
|
DOUBLE PRECISION |
xs:double |
|
FLOAT |
xs:double |
|
IMAGE |
xs:hexBinary |
|
INT (INTEGER) |
xs:int |
|
MONEY |
xs:decimal |
|
NCHAR |
xs:string |
|
NVARCHAR |
xs:string |
|
REAL |
xs:float |
|
SMALLDATETIMEFoot 4 |
xs:dateTime |
|
SMALLINT |
xs:short |
|
SMALLMONEY |
xs:decimal |
|
SYSNAME |
xs:string |
|
TEXTFoot 5 |
xs:string |
|
TIME |
xs:time |
|
TINYINT |
xs:short |
|
VARBINARY |
xs:hexBinary |
|
VARCHAR |
xs:string |
Footnote 1 Supports fractional seconds up to 3 digits (milliseconds) precision; no timezone information.
Footnote 2 Values converted to local time zone (timezone information removed) and fractional seconds truncated to milliseconds due to DATETIME limitations. See Section 3.2.1, "Date and Time Data Type Differences: Timezones and Time Precision" for more information.
Footnote 3 Where 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).
Footnote 4 Accurate to 1 minute.
Footnote 5 Expressions 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:
xdt:dayTimeDuration
xdt:yearMonthDuration
Table A-14 lists functions and operators that are pushed down to base RDBMSs. (See Section 2.1.2.1, "fn-bea:is-access-allowed" for details about two-argument and three-argument versions of the fn-bea:sql-like( ) function.)
Table A-14 Sybase ASE Function and Operator Pushdown
| Group | Functions and operators |
|---|---|
|
Logical operators |
and, or, not |
|
Numeric arithmetic |
+, -, *, div Foot 1 |
|
idivFoot 2 |
|
|
modFoot 3 |
|
|
Numeric comparisonsFootref 1 |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge |
|
Numeric functions |
abs, ceiling, floor, round |
|
String comparisonsFoot 4 |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge |
|
String functions |
concatFoot 5 , upper-case, lower-case, substring(2,3), string-length, containsFoot 6 , starts-with, ends-with, fn-bea:sql-like(2,3), fn-bea:trim, fn-bea:trim-left, fn-bea:trim-right, fn-bea:repeat, fn-bea:pad-left, fn-bea:pad-right |
|
Datetime comparisons |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time, xdt:yearMonthDuration, xdt:dayTimeDuration |
|
Datetime functions |
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 |
|
Datetime arithmetic |
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 |
|
Aggregate |
min, max, sum, avg, count, count(distinct-values) |
|
Other |
empty, exists |
Footnote 1 All numeric types (+, -, *, div operators are pushed down for all numeric types).
Footnote 2 xs:decimal (and subtypes) only
Footnote 3 xs:integer (and subtypes) only
Footnote 4 Arguments must be SQL data type CHAR, NCHAR, NVARCHAR, or VARCHAR.
Footnote 5 Each argument must be SQL data type CHAR, NCHAR, NVARCHAR, or VARCHAR.
Footnote 6 Second argument must be constant or SQL parameter.
Table A-15 lists supported cast operations.
Table A-15 Sybase ASE Cast Operation Pushdown
| Source XQuery Type | Target XQuery Type |
|---|---|
|
numeric |
xs:double |
|
numeric |
xs:float |
|
numeric |
xs:int |
|
numeric |
xs:short |
|
numeric |
xs:string |
|
xs:decimal (and subtypes) |
xs:integer |
|
xs:integer (and subtypes) |
xs:decimal |
|
xs:string |
xs:doubleFoot 1 |
|
xs:string |
xs:float |
|
xs:string |
xs:int |
|
xs:string |
xs:integer |
|
xs:string |
xs:short |
|
xs:dateTime |
xs:date |
|
xs:dateTime |
xs:time |
Footnote 1 Source SQL type must be (N)CHAR or (N)VARCHAR
Table A-16 lists common query patterns that can be pushed down. See Section 3.3.7, "Common Query Patterns" for details.
Table A-16 Sybase Other SQL Generation Capabilities
| Feature | Description |
|---|---|
|
If-then-else |
yes |
|
Inner joins |
yes, SQL-92 syntax |
|
Outer joins |
yes, SQL-92 syntax |
|
Semi joins, Anti semi joins |
yes |
|
Order by |
yes |
|
Order by: Empty order (NULL order) |
fixed (always sorts NULLs low). Order-bys with "empty greatest" modifier are not pushed down. |
|
Order by: Aggregate function in ordering expression |
yes |
|
Group by |
yes |
|
Distinct pattern |
yes |
|
Trivial aggregate pattern |
yes (using subquery) |
|
Direct SQL composition |
yes |
The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Teradata 13.x, 14.x.
Table A-17 lists supported data type mappings for Teradata.
Table A-17 Teradata Data Type Mapping
| Teradata Data Type | XQuery Type |
|---|---|
|
BYTE |
xs:hexBinary |
|
BYTEINT |
xs:short |
|
CHAR |
xs:string |
|
DATE |
xs:date |
|
DECIMAL(p,s) (NUMERIC) |
xs:decimal (if s > 0), xs:integer (if s == 0) |
|
FLOAT (REAL, DOUBLE PRECISION) |
xs:double |
|
INTEGER |
xs:int |
|
LONG VARCHAR |
xs:string |
|
SMALLINT |
xs:short |
|
TIME |
xs:time |
|
TIMESTAMP |
xs:dateTime |
|
VARBYTE |
xs:hexBinary |
|
VARCHAR |
xs:string |
Table A-18 lists functions and operators that are pushed down to Teradata. See Section 2.1.2.1, "fn-bea:is-access-allowed" for details about two-argument and three-argument versions of the fn-bea:sql-like() function.
Table A-18 Teradata Function and Operator Pushdown
| Group | Functions and operators |
|---|---|
|
Logical operators |
and, or, not |
|
Numeric arithmetic |
+, -, * Foot 1 |
|
divFoot 2 |
|
|
Numeric comparisonsFootref 1 |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge |
String comparisonsFoot 3 |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge |
|
String functions |
concat, upper-caseFoot 4 , lower-caseFootref 4, containsFoot 5 , starts-with, ends-with, fn-bea:sql-like(2,3) |
|
Datetime comparisons |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time |
|
Aggregate |
min, max, sum, avg, count, count(distinct-values) |
|
Other |
empty, exists |
Footnote 1 All numeric types
Footnote 2 Only xs:decimal, xs:float, and xs:double
Footnote 3 CHAR or VARCHAR SQL data types only for all arguments
Footnote 4 CHAR or VARCHAR SQL data type only for first argument
Footnote 5 First argument must be CHAR or VARCHAR SQL data type, second argument must be a constant or parameter
Table A-19 lists common query patterns that can be pushed down. (See Section 3.3.7, "Common Query Patterns" for details.)
Table A-19 Teradata Other SQL Generation Capabilities
| Feature | Description |
|---|---|
|
Inner joins |
yes, SQL-92 syntax |
|
Outer joins |
yes |
|
Semi joins, Anti semi joins |
yes |
|
Order by |
yes |
|
Order by: Empty order (NULL order) |
fixed (always sorts NULLs low). Order-bys with "empty greatest" modifier are not pushed down. |
|
Order by: Aggregate function in ordering expression |
no |
|
Group by |
yes |
|
Distinct pattern |
yes |
|
Trivial aggregate pattern pushdown |
yes (using GROUP BY constant) |
|
If-then-else pushdown |
yes |
|
Subsequence pushdown |
yes |
|
SQL Exit query composition (pushdown on top of SQL Exit) |
yes |
|
Runtime connection management |
no connection sharing |
The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Derby 10.6 (and higher).
Table A-20 lists supported data type mappings for Derby
Table A-20 Derby Data Type Mappings
| Derby Data Type | XQuery Type |
|---|---|
|
BIGINT |
xs:long |
|
BLOB |
Not supported |
|
CHAR() for bit data |
xs:string |
|
CLOB |
xs:string |
|
DATE |
xs:date |
|
DOUBLE |
xs:double |
|
DECIMAL(p,s) |
xs:decimal (if s > 0) xs:integer (if s = 0) |
|
INTEGER |
xs:int |
|
LONG VARCHAR |
xs:string |
|
LONG VARCHAR for bit data |
xs:hexBinary |
|
REAL |
xs:float |
|
SMALLINT |
xs:short |
|
TIME |
xs:datetime |
|
VARCHAR |
xs:string |
|
VARCHAR() for bit data |
xs:hexBinary |
|
BOOLEAN |
xs:boolean |
Table A-21 lists supported functions and operators for Derby
Table A-21 Derby Functions and Operators
| Group | Functions and Operators |
|---|---|
|
Logical operators |
and, or, not |
|
Numeric operators |
+, -, *, div, idiv, mod |
|
Numeric comparisons |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge |
|
Numeric functions |
abs, ceiling, floor |
|
String comparisons |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge |
|
String functions |
concat, upper-case, lower-case, substring(2,3),string-length, contains, starts-with, ends-with, fn-bea:sql-like(2,3) fn-bea:trim, fn-bea:trim-left, fn-bea:trim-right, fn-bea:repeat, fn-bea:pad-left, fn-bea:pad-right |
|
Datetime comparisons |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time |
|
Datetime functions |
year-from-dateTime, year-from-date, month-from-dateTime, month-from-date, day-from-dateTime, day-from-date, hours-from-dateTime, hours-from-time, minutes-from-dateTime, minutes-from-time, seconds-from-dateTime, seconds-from-time, fn-bea:date-from-dateTime, fn-bea:time-from-dateTime |
|
Aggregate |
min, max, sum, avg, count, count(distinct-values) |
|
Other |
empty, exists, subsequence |
Table A-22 lists supported cast operations for Derby
Table A-22 Derby Functions and Operators
| Source XQuery Type | Target XQuery Type |
|---|---|
|
numeric |
xs:double |
|
numeric |
xs:float |
|
numeric |
xs:int |
|
numeric |
xs:integer |
|
numeric |
xs:short |
|
xs:decimal (and subtypes) |
xs:string |
|
xs:integer (and subtypes) |
xs:decimal |
|
xs:string |
xs;double |
|
xs:string |
xs:float |
|
xs:string |
xs:int |
|
xs:string |
xs:integer |
|
xs:string |
xs:short |
|
xs:dateTime |
xs:time |
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 7 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 at http://download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3/datasrvc/Understanding Data Service Annotations.html for more information.
The database capabilities listed in Table A-23 are obtained from the operative JDBC driver and stored as properties in the .ds (data service) definition file.
Table A-23 Database Properties Derived from the JDBC Driver
| Property | Description | Possible Values |
|---|---|---|
|
supportsSchemasInDataManipulation |
Boolean that identifies whether SQL statements can include schema names |
true, false |
|
supportsCatalogsInDataManipulation |
Boolean that identifies whether database catalogs can be addressed by SQL |
true, false |
|
supportsLikeEscapeClause |
Boolean that identifies if the database supports ESCAPE clause in LIKE expression |
true, false |
|
nullSortOrder |
Order in which NULLs are sorted |
low, high, unknown |
|
identifierQuote |
String used as delimiter to denote (offset) identifier labels |
String value (can be empty) |
|
catalogSeparator |
String used as delimiter (separator) between catalog (or schema) and table name |
String value |
The Oracle Data Service Integrator XQuery engine typically quotes the names (identifiers) of object names to properly handle any special characters. The identifierQuote property is obtained from the JDBC driver. However, different RDBMSs may use different identifiers for different database object names:
catalogs
schemas
tables
columns
If necessary, you can manually override the identifier quote property for each type of identifier.
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 at http://download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3/datasrvc/Understanding Data Service Annotations.html for more information about these properties). The XQuery engine (metadata importer sub-system) uses the specific quote property (see Table A-24) 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.
Table A-24 Optional Quote Properties for Database Objects
| Property | Description | Possible Values |
|---|---|---|
|
catalogQuote |
Special character used as quote to denote name of catalog |
string |
|
schemaQuote |
Special character used as quote to denote name of schema |
string |
|
tableQuote |
Special character used as quote to denote name of table |
string |
|
columnQuote |
Special character used as quote to denote name of column |
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 A-25 shows this mapping.
Table A-25 Base Platform Data Type Mapping (JDBC<–>XQuery Equivalents)
| JDBC Data Type | Typecode | XQuery Data Type |
|---|---|---|
|
BIGINT |
-5 |
xs:long |
|
BINARY |
-2 |
xs:string |
|
BIT |
-7 |
xs:boolean |
|
BLOB |
2004 |
xs:hexBinary |
|
BOOLEAN |
16 |
xs:boolean |
|
CHAR |
1 |
xs:string |
|
CLOBFoot 1 |
2005 |
xs:string |
|
DATE |
91 |
xs:dateFoot 2 |
|
DECIMAL (p,s)Foot 3 |
3 |
xs:decimal (if s > 0), xs:integer (if s =0) |
|
DOUBLE |
8 |
xs:double |
|
FLOAT |
6 |
xs:double |
|
INTEGER |
4 |
xs:int |
|
LONGVARBINARY |
-4 |
xs:hexBinary |
|
LONGVARCHARFootref 1 |
-1 |
xs:string |
|
NUMERIC (p,s)Footref 3 |
2 |
xs:decimal (if s > 0), xs:integer (if s =0) |
|
REAL |
7 |
xs:float |
|
SMALLINT |
5 |
xs:short |
TIMEFoot 4 |
92 |
xs:time |
|
TIMESTAMP |
93 |
xs:dateTime |
|
TINYINT |
-6 |
xs:short |
|
VARBINARY |
-3 |
xs:hexBinary |
|
VARCHAR |
12 |
xs:string |
|
OTHER |
1111 |
Oracle Data Service Integrator uses native data type name to map to an appropriate XQuery data type. |
|
Other vendor-specific JDBC type codes |
||
Footnote 1 Pushed down in project list only.
Footnote 2 Values converted to local time zone (timezone information removed) due to DATE limitations. See Section 3.2.1, "Date and Time Data Type Differences: Timezones and Time Precision" for more information.
Footnote 3 Where 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).
Footnote 4 Precision 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 A-26 lists functions and operators that are pushed down to base RDBMSs. See Section 2.1.2.1, "fn-bea:is-access-allowed" for details about two-argument and three-argument versions of the fn-bea:sql-like() function.
Table A-26 Base Platform Functions and Operators
| Group | Functions and Operators |
|---|---|
|
Logical operators |
and, or, not |
|
Numeric arithmetic |
+, -, *Foot 1 |
|
divFoot 2 |
|
|
Numeric comparisonsFootref 1 |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge |
|
String comparisonsFoot 3 |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge |
|
String functions |
containsFoot 4 , starts-with, ends-with, fn-bea:sql-like(2), fn-bea:sql-like(3), upper-case, lower-case |
|
Datetime comparisons |
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time |
|
Other |
empty, exists |
Footnote 1 All numeric types
Footnote 2 Support for xs:decimal, xs:float, and xs:double data types only.
Footnote 3 Arguments must be CHAR or VARCHAR SQL data types.
Footnote 4 First 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.
Table A-27 displays other SQL Pushdown capabilities, as discussed in Section 3.3.7, "Common Query Patterns."
Table A-27 Base Platform SQL Generation Capabilities
| Query | Supported |
|---|---|
|
If-Then-Else |
no |
|
Inner joins |
yes (SQL-89 syntax) |
|
Outer joins |
no |
|
Semi-joins, Anti-semi-joins |
no |
|
Order by |
yes |
|
Order by: Empty (NULL) order supported |
Database-dependent |
|
Order by: Aggregate function in ordering expression |
no |
|
Group by |
yes (by column only) |
|
Distinct pattern |
yes |
|
Trivial aggregate pattern |
no |
|
Direct SQL composition |
no |