dataupia satori server overview 2.2.pdf · publication part number: dt-2008-demm-001 distributed by...

Dataupia Satori Server

Release 2.2 Overview for Oracle

March 2009

ii

Statement of Conditions

In the interests of improving internal design, operational function, and/or reliability, DATAUPIA reserves the right to make changes to the products described in this document without notice. DATAUPIA does not assume any liability that may occur due to the use or application of the product(s) described herein.

Federal Communications Commission (FCC) Compliance Notice

This equipment has been tested and found to comply with the limits for a Class A digital device, pursuant to FCC 47 CFR Part 15.

EN 55 022 Declaration of Conformance

This is to certify that the Dataupia™ Satori Server is shielded against the generation of radio interference in accordance with the application of Council Directive 89/336/EEC, Article 4a. Conformity is declared by the application of EN 55 022:1998/A1:2000/A2:2003 Class A.

Other Emissions Conformance

Canada – ICES-003 Issue 4 Class A Digital Apparatus

Japan – VCCI Class A ITE

Australia – AS/NZS CISPR 22:2002 Class A ITE

Product and Publication Details

Model Number:

Publication Date: March 2009

Product Name: Dataupia Satori Server

Publication Part Number: DT-2008-DEMM-001

Distributed by

Dataupia Corporation

One Alewife Center

Cambridge, MA 02140, U.S.A

Customer Support

Dataupia Corporation offers a Helpdesk service for our customers. The Helpdesk will assist you in using our products and will answer all questions you might have about Dataupia Corporation or its products. The Helpdesk can be contacted via email, phone or fax.

Customer Portal: www.dataupia.com Click the Customer Login link at the top right of the page. Email: [email protected]

Phone: 866-259-5971

Fax: 617-301-6803

Dataupia Satori Server 2.2 Release Overview

Copyright © 2009 Dataupia Corporation. All Rights Reserved. iii

Copyright and Trademarks

Copyright © 2007-2009 Dataupia Corporation, all rights reserved.

Dataupia, Record-based Optimized Storage, Omniversal Transparency, and the Dataupia logo and slogan are trademarks of Dataupia.

Other brands and their products are trademarks of their respective holders.


Copyright © 2009 Dataupia Corporation. All Rights Reserved. iv

Contents

About This Release Overview ....................................................................................................................1

Chapter 1: Supported Platforms ................................................................................................................2

Chapter 2: Transparency ............................................................................................................................3

SQL Functions ...........................................................................................................................................3 Support for Non-Star Schema ...................................................................................................................3 Support for Sub-Selects.............................................................................................................................4 The HAVING clause...................................................................................................................................4 Bind Variables............................................................................................................................................4 ORDER BY and GROUP BY .....................................................................................................................4 SQL Query Length .....................................................................................................................................4

Chapter 3: Reliability...................................................................................................................................5

Distributed Aggregation .............................................................................................................................5 Transaction Handling .................................................................................................................................5 Memory Management ................................................................................................................................5 Internal Process Management ...................................................................................................................5 Multiple Data Loaders ................................................................................................................................6 Data Loader File Format ............................................................................................................................6 NTP Time...................................................................................................................................................6 Deletion Housekeeping..............................................................................................................................6

Chapter 4: Administration ..........................................................................................................................7

Dataupia Management Console ................................................................................................................7 Cancelling Queries.....................................................................................................................................8 Error Handling............................................................................................................................................8

Chapter 5: Fixed Issues ..............................................................................................................................9

DDL............................................................................................................................................................9 DML............................................................................................................................................................9 Operators .................................................................................................................................................10 CLI, Installation Wizard and Management Console ................................................................................10 Transaction, DB, and Memory .................................................................................................................10 PPO..........................................................................................................................................................11

Chapter 6: Known Issues..........................................................................................................................13

Joins.........................................................................................................................................................13 Data Types...............................................................................................................................................13 Formatting ................................................................................................................................................14 DDL..........................................................................................................................................................19 DML..........................................................................................................................................................20 Functions and Expressions......................................................................................................................21 CLI, Installation Wizard and Management Console ................................................................................27 Transaction, DB, and Memory .................................................................................................................27 Indices......................................................................................................................................................28 Installation ................................................................................................................................................28 Loader ......................................................................................................................................................29 PPO..........................................................................................................................................................30

Appendix A: Dataupia SQL Support ........................................................................................................31


Copyright © 2009 Dataupia Corporation. All Rights Reserved. v

Data Types...............................................................................................................................................31 Operators .................................................................................................................................................32 Conditions ................................................................................................................................................32 Transaction Control..................................................................................................................................33 Oracle Functions......................................................................................................................................33 Formatting ................................................................................................................................................36 Functionality Summary ............................................................................................................................38

Dataupia Satori Server Release 2.2 Overview

1

About This Release Overview

This document describes the features, enhancements, and bug fixes provided by Release 2.2 of the Dataupia Satori Server. For details and usage information on new and pre-existing functionality, please see the Dataupia product documentation.

This overview of the release is organized into these chapters:

• Chapter 1: Supported Platforms

• Chapter 2: Transparency

• Chapter 3: Reliability

• Chapter 4: Administration

• Chapter 5: Fixed Issues

• Chapter 6: Known Issues

• Appendix A: SQL Support


2

Chapter 1: Supported Platforms

Release 2.2 of the Dataupia Satori Server supports the following platforms and combinations:

Oracle 10g Release 10.2.0.3.0

Oracle 11g Release 11.1.0.6.0

Sun Solaris 5.10 64 Bit

Oracle Connector v 2.0

Not Supported

Red Hat Linux 5.2 64 Bit



Notes:

o Prior connector releases (i.e., the Oracle v1.x connectors) are not supported in Release 2.2.

o Prior backend server releases (i.e., v2.0.7) are not supported with the v2.0 Oracle Connector.


3

Chapter 2: Transparency

Transparency is the ability of the Dataupia Satori Server to operate within the existing environment of the host database. To achieve a higher degree of compatibility between Oracle and a Dataupia array, Release 2.2 extends the support for Oracle functions. More complex types of queries are better supported and a broader range of SQL functions are processed by Dataupia’s massively parallel processing (MPP) environment.

A new architecture for Dataupia’s Oracle Connector changes the way Dataupia uses the Oracle communications layers and libraries which results in better performance and lifts certain limitations.

Changes in the following areas have increased the degree of transparency between Oracle and Dataupia:

• SQL functions

• Support for non-star schema

• Support for outer joins

• Sub-selects

• The HAVING clause

• Bind variables

• ORDER BY and GROUP BY

• SQL query length

Please see Appendix A for a list of supported SQL syntax.

SQL Functions

Release 2.2 changes how the Dataupia Satori Server supports Oracle SQL functions. There is no explicit impact to the user, that is, the user does not have to make any changes. However, the user will perceive a greater degree of compatibility with how Oracle has implemented certain SQL functions.

Support for Non-Star Schema

Prior to release 2.1, Satori supported star-schema joins between a single fact table and replicated dimension tables, or self joins of the fact table if it was hash-distributed across blades.

In release 2.2, joins are allowed between tables with any distribution method. In practice this allows snowflake and normalized schema.


4

Support for Sub-Selects

Release 2.2 supports all types of non-correlated sub-selects (nested queries) without limitation, including those within FROM clauses (on derived tables).

The HAVING clause

The Dataupia Satori Server now supports the HAVING clause.

Bind Variables

The Dataupia Satori Server now supports bind variables.

ORDER BY and GROUP BY

The Dataupia Satori Server now uses its MPP environment to process more queries that include ORDER BY and GROUP BY clauses.

SQL Query Length

Dataupia now accepts queries that are 256K characters long. Previously, there was a 2K character limit to the length of SQL queries.

Please note that the 256K maximum size is after all expansion by Oracle, so your original query might be less than 256K, but after Oracle expands the query, it is larger than 256K. For example, one area where this occurs is when Oracle expands a "*" in the projection list to the complete list of field names for the table.

Therefore, the effective query length limit is somewhat less than 256K, depending on the extent that Oracle expands the original query.


5

Chapter 3: Reliability

Reliability features and enhancements improve the robustness and predictability of a Dataupia Satori Server deployment. Much of the work to increase reliability occurred in these areas:

• Distributed aggregation

• Transaction handling

• Memory management

• Internal process management

• Data loader

• NTP time

• Deletion housekeeping

Distributed Aggregation

Prior to Release 2.1, the Dataupia Satori Server implemented MPP where each blade in an array processed their portion of queries independently. There were no inter-blade transfers of tuples or results. Aggregations were handled in a separate aggregation server with enhanced memory. The maximum number of aggregate groups and the total query throughput was limited by the resources of the aggregation server.

Now, the process of aggregating results is distributed across an entire array, removing the bottleneck of a single server. In addition to expanding the Dataupia Satori Server’s throughput, distributed aggregation and inter-blade operations have enabled the support of more SQL constructs.

Transaction Handling

Several operations that required exclusive table access in previous releases have been changed to now require shared access. These changes in transaction handling have improved concurrency as well as the reliability of the system.

Memory Management

Changes have been made to the way various threads and common processes utilize memory on the Dataupia Satori Server, resulting in improved performance and scalability.

Internal Process Management

Some activities that were performed serially by a single thread in prior releases have now been redesigned to be performed in parallel. This has resulted in significant improvements in concurrency and eliminated deadlocks that were exhibited by previous releases.


6

Multiple Data Loaders

Previous releases supported running parallel data loaders to a Dataupia array but not to the same table. Release 2.2 supports multiple data loaders concurrently loading data to the same table.

Data Loader File Format

In Release 2.2 the data description files which are passed to the data loader have a new format and several new requirements. There have been changes to the Control section of the file. The changes also affect any customizations you have made.

Please contact Dataupia Technical Support for instructions and assistance when upgrading your data description files.

For example, due to improved error detection and handling, for the int directive you must now use the opt=L option to specify that leading blank spaces should be skipped.

Before creating a new data description file, please read Chapter 2 in the Dataupia Satori Server User Guide.

NTP Time

The time settings on all blades in an array need to be synchronized. This release requires an NTP server on the network to enable this synchronization.

Deletion Housekeeping

The housekeeping utility which frees space after deletions is more reliable and robust. Changes have been made to how the housekeeping utility uses in-memory table space and accesses objects, enabling the utility to scale and perform better.


7

Chapter 4: Administration

Another area of emphasis for Release 2.2 is the administration of the Dataupia Satori Server. There have been enhancements to the tools and to the amount of diagnostic information available to users. This chapter describes changes in the following areas:

• Data Management Console

• Cancelling queries

• Error handling

Dataupia Management Console

Several improvements have been made to the Dataupia Management Console (DMC) such as:

• The DMC initializes and populates information more quickly.

• The array status screen now shows node and slice status in addition to blade status. This functionality was previously supported only through the CLI.

• Under the Configuration menu, a new option, Notification, allows users to configure thresholds and recipients for email alerts. Previously users had to configure alerts through the CLI. See the following images:


8

Cancelling Queries

Users can now terminate or kill queries across the whole array instead of having to do it one blade at a time.

Error Handling

The Dataupia Satori Server now responds differently to certain error conditions; instead of automatically shutting down, it alerts the user to the potential problem.

Error handling in the Dataupia Satori Server has been significantly improved. In earlier releases, the occurrence of some types of errors caused an immediate shutdown of the server resulting in query interruptions. In many instances the Dataupia Satori Server alerts the user to a potential problem instead of simply shutting down.


9

Chapter 5: Fixed Issues

This chapter provides information on issues that have been fixed in Release 2.2. Each item includes information on the resolution or usage notes when applicable.

DDL

Oracle Connector: TIMESTAMP function

Issue: Dataupia does not support certain time formats. For example, this query : update "FILE_VIEW_PERF"@dt_relay set "DATE"='08-APR-08 12.00.00.000000 AM' where "DATE"='21-DEC-06 12.00.00.000000 AM' and "OPERATOR"=7;

results in format issues.

• Dataupia does not support STRUCT TIMESTAMP WITH TIME ZONE in this context: (#3141)

insert into insert_timestamp values(TIMESTAMP '2008-06-28 10:10:10 PST')

Resolution: You can now use STRUCT TIMESTAMP WITH TIME ZONE in this context. The delimiter of TIMESTAMP cannot be periods (.). Casting doesn't work. If you do a Select query with CAST function on CHAR to TIMESTAMP it throws error. This is a formatting issue of the timestamp-string. The delimiter between hour, minute, and second cannot be a period ('.') and must be a colon (':').

Resolution: This issue has been fixed.

Oracle Connector: MINUS and INTERSECT do not work in a Subselect.

Using MINUS or INTERSECT in a Subselect does not work. MINUS or INTERSECT not in a subselect do work.

Resolution: This issue has been fixed so that you can now use MINUS or INTERSECT in a Subselect.

DML

Do not use “–“ operator with UPDATE query

Issue: An error occurred if you used the “–“ operator with an UPDATE query.

Resolution: This issue has been fixed so that you can now use the “–“ operator with an UPDATE query.


10

Operators

Use explicit cast with concatenation and mathematical comparison operators

Issue: Queries using the concatenation operator require the use of parentheses due to a difference in the order of precedence between Oracle and Dataupia for this operator. There is also a difference in order of precedence for the mathematical comparison operator, but generally the syntactical use of the mathematical comparison operator prevents improper operator precedence. However, it is suggested that you use parentheses around queries using the mathematical comparison operator, as well. Parentheses are currently stripped out of queries, so for this release you will need to use either an explicit cast or a logical reconstruction when using the concatenation operator. Resolution: this issue has been fixed so that there is no longer a difference in the order of precedence. Also, parentheses are no longer stripped out of queries.

CLI, Installation Wizard and Management Console

Installation (Oracle Connector): dtoracfg crashes

Issue: This issue arises if there is an error inside the tnsnames.ora or listener.ora file. Workaround: The solution is to change the badly formatted lines. For example, add single quotes around the path specified in ENVS = ‘LD_LIBRARY_PATH= path’ Resolution: This issue has been fixed so that dtoracfg no longer crashes under these conditions.

Transaction, DB, and Memory

Hash table to hash table join results in dataupia-db exception Issue: An issue existed where running an insert into/select from 2 hash distributed tables would sometimes result in a dataupia-db exception. Resolution: This issue has been fixed so that this exception no longer occurs.

Database crash while running query that returns ~ 200M rows (zipcode aliasing with Maintenance running) Issue: A database crash would sometimes occur when running a query with zipcode aliasing that returns a large number (~200M) of rows, and with Maintenance running. Resolution: This issue has been fixed so that the database no longer crashes under these conditions.


11

Some sessions hang when running multiple concurrent sqlplus sessions Issue: Previously, when running multiple concurrent sqlplus sessions, individual sessions would sometimes hang. Resolution: This issue has been fixed so that the hangs no longer occur when running multiple concurrent sqlplus sessions.

Intrablade deadlock between INSERT INTO ... SELECT FROM and DROP TABLE Issue: An intrablade deadlock occurred when an attempt was made to drop a table that was in the process of being inserted into. Resolution: this issue has been fixed so that the deadlock no longer occurs under these circumstances.

PPO

Equijoin of varchar distribution keys should result in local processing Issue: Previously, equijoins involving varchar distribution keys did not result in local processing, which resulted in degraded performance. Resolution: this issue has been fixed so that equijoins involving varchar distribution keys now result in local processing.

Insert-into-select query hangs Issue: Previously, insert-into-select queries would sometimes hang. Resolution: this issue has been fixed so that this issue no longer occurs with insert-into-select queries.

Star schema benchmark queries fetching incorrect data for tables registered with RR-ALL distribution pattern combination Previously, Star schema benchmark queries would sometimes fetch incorrect data for tables registered with an RR-ALL distribution pattern combination. Resolution: this issue has been fixed so that Star schema benchmark now fetch correct data for tables registered with an RR-ALL distribution pattern combination.


12

Performance enhancement: Support lazy round robin in “INSERT INTO SELECT FROM” Issue: Release 2.1 simplistically enforced the balancing of rows in round robin tables populated via "insert into select from". This enforcement involved a final round robin redistribution step of the result of "insert into select from" statements, rather than an assumption that storing each result row at the location where it was produced is suitably random. This hurt performance in "close enough" cases where the de facto locations were suitably random or where their skew was not a significant consideration. Resolution: a "lazy round robin" mode now bypasses the final redistribution for such cases.


13

Chapter 6: Known Issues

This chapter provides information on known issues in Release 2.2. Each item includes information on workarounds or usage notes when applicable. Many of the items have an ID that Dataupia uses for internal tracking purposes. You can refer to the ID when communicating with Dataupia Technical Support. Please see Appendix A for a list of supported SQL.

Joins

Select query with inner join and ORDER BY

Using ORDER BY on the same column used in the aggregate function is not supported. For example: SQL> SELECT AVG(t1."col1") from ppo_dqptable1float_rr t1 INNER JOIN ppo_dqptable2float_rr t2 on t1."col1" = t2."col1" ORDER BY t1."col1";

Workaround: Remove the ORDER BY clause.

Outer join queries

Outer join queries with tables whose distribution method is all are not supported in this release. The queries will return errors.

Workaround: Create a copy of the table registered as all using a different distribution method.

Data Types

Oracle Connector: Implicit conversion for VARCHAR and CHARACTER data types

In this release, Dataupia does not automatically convert VARCHAR and CHARACTER data types where an operation requires an implicit conversion to numeric as in select colvarchar + 5 from chartable;

Workaround: Explicitly CAST the data type to a numeric before the operation.

Use ORA_DATE for a date stored column

To get true Oracle Date capabilites for a date stored column, the new ORA_DATE backend type must be used. It is highly desirable NOT to mix the use of ORA_DATE and the old Dataupia-db DATE type in backend applications. Doing so could cause results that are different for DATE manipulations. Rather, if the new capabilities are desired, all backend production tables should be changed to store ORA_DATE.


14

Oracle Connector: SELECT query rounds off the floating point value

A SELECT query rounds off the floating point value to three decimal places. For example, for this query: SELECT AVG(DISTINCT(t1."col1")) from ppo_dqptable1float_rr t1 FULL JOIN ppo_dqptable2float_rr t2 on t1."col1" = t2."col1";

This is the expected result: AVG(DISTINCT(T1."COL1")) ------------------------ 222674.1836

Here is the actual result: AVG(DISTINCT(T1."COL1")) ------------------------ 222674.184

Formatting

General

You must specify the format model with the TO_CHAR, TO_DATE, TO_NUMBER, and TO_TIMESTAMP functions. There are some limitations with the format options. These are listed in the descriptions of the relevant functions.

Query case should match table and column name case

When constructing queries, it is recommended that the case used in the query match the case of the applicable table or column name. The following table provides more detailed information about case sensitivity, but as a general practice you should use the same case used in the table when writing queries.

Array and Query Case Sensitivity Matrix:

DT Array Table Attribute Name Case Query Use of Attribute Name Case Pass/Fail

1 All Lower Case Lower or Upper or Mix Case with No Quotes Pass

Lower with Quotes Pass

Upper or Mix Case with Quotes Fail

2 All Upper Case Upper case with Quotes Pass

Lower or Upper or Mix Case with No Quotes Fail

Lower or Mix case with Quotes Fail

3 Upper & Lower (Mix) Case Mix (exact match with array table) Case with Quotes Pass

Lower or Upper or Mix Case with No Quotes Fail

Lower or Upper Case with Quotes Fail

Attribute = column or table name


15

Issue: to_timestamp format_models.ff for timestamp data type throws error

We now support FF[n-1..6] where n is the precision of the decimal places. FF=7,8,9 are not currently supported.

Oracle Connector: TS abbreviation for TIMESTAMP format

Workaround: Use the complete HH:…. syntax instead of the TS short cut.

Usage Note: The radix character “x” is a format for printing numbers in hex. Dataupia does not support using the radix character for a region-specific decimal separator. Queries should be region specific (‘HH:MI:SS.US” or “HH:MI:SS,US” instead of the generic “HH:MI:SSXUS”). For example, when parsing “12:34:56.123456” when it’s 12:34pm with 56.123456 seconds, the radix character is a period “.” Compared to some European formats where the radix character is a comma "," (12:34:56,123456).

For example:

select TO_CHAR("ci",'TS') from try_time where "ci"='19-JUL-91';

should return:

12:00 AM

Instead returns:

TS

Oracle Connector: issues related to queries with a combination of local and remote tables

For some queries involving remote tables, the Oracle optimizer performs a local join by issuing multiple queries to the remote database, which based on the size of data could fail in DT-OracleConnector. Using the driving_site() hint on such queries forces Oracle to push the entire query to the remote database, avoiding the multiple query problem. However, using the driving_site() hint does not work when the query involves a combination of local and remote tables. In these cases, using the driving_site() hint forces Oracle to send the entire query to the remote database, which is unable to access the local tables.

Workarounds:

Here are some methods by which the driving_site() hint can be used to avoid multiple-query problems: 1) Always create views on remote tables with the driving_site() hint. If joining local and remote tables

results in the following error:

ORA-20500: relation "LOCAL_TABLE" does not exist ORA-06512: at "DT_RELAY.DT_EXCEPTION", line 4 ORA-02063: preceding 2 lines from DT_RELAY

Modify the query by adding driving_site(<local_table>) hint.


16

For example: CREATE VIEW H_remote_table_1 AS SELECT /*+ driving_site(“remote_table_1”) */ * FROM “remote_table_1”@dt_relay;

SELECT /*+ driving_site(Local_table) */ * FROM H_remote_table, Local_table WHERE …;

2) Create two views on remote tables, one with and one without the hint. Use the view with the hint for

queries involving remote tables only, and use the view without the hint for queries that have a mix of local and remote tables.

For example:

CREATE VIEW remote_table_1 AS SELECT * FROM “remote_table_1”@dt_relay; CREATE VIEW H_remote_table_1 AS SELECT /*+ driving_site(“remote_table_1”) */ * FROM “remote_table_1”@dt_relay; CREATE VIEW remote_table_2 AS SELECT * FROM “remote_table_2”@dt_relay; CREATE VIEW H_remote_table_2 AS SELECT /*+ driving_site(“remote_table_2”) */ * FROM “remote_table_2”@dt_relay;

a) For joins with only remote tables use the views that have the hint:

SELECT … FROM H_remote_table_1, H_remote_table_2 WHERE … ;

b) For joins with combination of local and remote tables use the views that do not have the hint:

SELECT … FROM remote_table_1, remote_table_2, local_table WHERE … ;

Unfortunately, depending on the number of local and remote tables, and the complexity of the query, the above query (b) might still fail if Oracle optimizer decides to issue multiple queries to the remote database.

2) Create views on remote tables without the driving_site() hint, but if the query fails, alter it using the driving_site() hint suggested in the error message from DT-OracleConnector.

For example: SELECT … FROM remote_table_1, remote_table_2 WHERE … ;

ORA-20001: Unable to process query. Retry the query by adding the following hint: SELECT /*+ driving_site(<external-table-name>) */ ... ORA-02063: preceding 2 lines from DT_RELAY

SELECT /*+ driving_site(remote_table_1) */ … FROM remote_table_1, remote_table_2 WHERE … ;

Examples of queries that exhibit the problem: There are two types of queries that exhibit the multiple-query problem: 1) A query has a join of two remote tables where one of the tables is a sub-select with an aggregation,

and there is an order by clause on the query.

Example 1: Select A.Col_1, A.Col_2, B.SumVal


17

From TableA A, (Select Col_1, Sum(Col_2) SumVal From TableB Group by Col_1) B Where A.Col_1 = B.Col_1 Order By A.Col_1

This gets broken down into two queries: 1) Select Col_1, MAX(Col_2) MaxVal From TableB Group by Col_1

2) Select Col_1, Col_2 from TableA where Col_1 = :1 Example 2: Create View View_B (Col1, SumVal) as (Select Col_1, Sum(Col_2) From TableB Group by Col_1) Select A.Col_1, A.Col_2, B.SumVal From TableA A, View_B B Where A.Col_1 = B.Col_1 And B.SumVal = (select MAX(SumVal) from View_B) Order By A.Col_1

This gets broken down into three queries on two connections: Connection 1: 1) View_B Connection 2: 2) Select MAX(SumVal) from View_B

3) Select Col_1, Col_2 from TableA where Col_1 = :1 2) A JOIN between two tables with an aggregation.

Example 1: SELECT AVG(DISTINCT(t1.col1)) from t1 FULL JOIN t2 on t1.col1 = t2.col1;

Example 2: SELECT count(*) from t1 NATURAL RIGHT JOIN t2;

Where the two tables do not have any common join columns defined.

Recommendations:

• In the event of failed queries indicated by the error:

ORA-20500: relation "LOCAL_TABLE" does not exist ORA-06512: at "DT_RELAY.DT_EXCEPTION", line 4 ORA-02063: preceding 2 lines from DT_RELAY

The view should be recreated without the driving_site() hint.


18

• In the event that both local table joins and remote table joins split the query, resulting in the error:

ORA-20001: Unable to process query. Retry the query by adding the following hint: SELECT /*+ driving_site(<external-table-name>) */ ... ORA-02063: preceding 2 lines from DT_RELAY

Delegated table views should be created with the driving_site() hint.

Comparison with 2.0.7. In 2.0.7 we had similar issues with pushed down queries and local/remote joins. Most of these issues have been improved with this approach, however, there are some differences that could result in some queries now working that didn’t before, and some queries that did work now needing to reworked around using the preceding information. At issue is Oracle “handling” splitting the query in two in 2.0.7 but DT-OracleConnector not handling bind variables – which has been now been fixed. Even when some of the queries did not functionally fail in 2.0.7, they would appear to “hang” due to processing inefficiencies. We will continue to work to resolve remaining join locality issues in subsequent releases.

Oracle views created in 2.0.7 need to be recreated using @DT_RELAY

Any Oracle view created in 2.0.7 will need to be recreated to use @DT_RELAY instead of @DTNAS. Using @DTNAS will not take advantage of the Connector/relay; it will use the “old” database link method of connecting. To see how the view was created, you can run the following in Oracle (VERY useful to see how the view was created): set serveroutput on; declare c varchar2(32767); begin select text into c from user_views where view_name=UPPER(’view_name’); dbms_output.put_line(c); end;

Correlated Subqueries

Correlated subqueries are not currently supported.


19

DDL

Database: Table names are limited to 30 characters

Oracle limits table names to 30 characters.

Database: Long table names truncated in CREATE TABLE

When a name longer than 30 characters is submitted to CREATE TABLE, the command succeeds after truncating the name, instead of failing. Workaround: Make sure your table names are within the acceptable limit of 30 characters.

Run DESCRIBE table@dt_relay after altering a Dataupia database table

The Dataupia Oracle Connector (dt_relay) creates empty tables that shadow the Dataupia database tables in its Oracle instance. These “shadow” tables are created the first time the Dataupia database table is accessed by the Oracle instance, and are recreated every time a “DESCRIBE table@dt_relay” is executed.

If a Dataupia database table is altered at any time after its “shadow” table was created in the dt_relay Oracle instance, any subsequent queries will not see the new definition of the table, but instead will use the old definition from the original “shadow” table. To ensure that the “shadow” table is updated properly, you should run “DESCRIBE table@dt_relay” for the altered table.


20

DML

INSERT into <table> with hash distribution

Changing a table’s data distribution method from all to hash with an INSERT into SELECT FROM is unsupported in this release.

In short, " INSERT into SELECT FROM..." cannot be used to produce round robin or hashed results from an all-distributed data source. The table below gives the supported changes to distribution methods.

Usage Notes:

• If you want to change the distribution method for a table, unload the data, change the distribution method for the table, and load the data back in.

• Only columns of data type SMALLINT, INTEGER, BIGINT, and VARCHAR() can be used for hash distribution.

Original Data Distribution

Target Data Distribution

Whether/How Supported in the Current Release

All All Fully supported All Single Fully supported All Hashed Not supported All Round Robin Not supported Single All Fully supported Single Single Fully supported Single Hashed Fully supported Single RR Fully supported Hashed All Fully supported Hashed Single Fully supported Hashed Hashed Fully supported Hashed RR Fully supported RR ALL Fully supported RR Single Fully supported RR Hashed Fully supported RR RR Fully supported

INSERT, UPDATE, DELETE statements

There are some limitations in using the following syntax within INSERT, UPDATE, and DELETE statements as noted:

DECODE in a sub-query is not supported.

INSERT, UPDATE, DELETE, & SELECT statements

Name binding is not supported within INSERT, UPDATE, DELETE, and SELECT statements.


21

Functions and Expressions

Results of subqueries are distributed to all blades

Currently the results of any subquery are distributed to all blades, which may not be necessary, and which can also affect performance.

Workaround: use a LEFT OUTER JOIN. Example: Original Query: EXPLAIN INSERT INTO cub030p010 ( "CALL_DATE" ,"DIALLED_DIG_STR" ,"FK_NOP_ID" ,"CNSIDENT" ) SELECT "CALL_DATE" ,"DIALLED_DIG_STR" ,"FK_NOP_ID" ,('{xlat:substr' / (((1 * "A2"."DIALLED_DIG_STR") + (2 * 1)) + (3 * 8))) FROM "cub030p005" "A2","INCA_WS_CNS" "A1" WHERE "A2"."CALL_DATE" >= "A1"."EFFECTIVE_TSTAMP" AND "A2"."CALL_DATE" < "A1"."INEFFECTIVE_TSTAMP" AND ('{xlat:substr' / (((1 * "A2"."DIALLED_DIG_STR") + (2 * 1)) + (3 * 8))) = "A1"."IDENT" AND ("CALL_DATE","DIALLED_DIG_STR") NOT IN (SELECT "CALL_DATE","DIALLED_DIG_STR" FROM cub030p010); QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Merge Join (cost=6533.20..66908.55 rows=259195 width=25) Merge Cond: ("outer"."?column5?" = "inner"."?column3?") Join Filter: (("A2"."CALL_DATE" >= "A1"."EFFECTIVE_TSTAMP") AND ("A2"."CALL_DATE" < "A1"."INEFFECTIVE_TSTAMP")) -> Sort (cost=951.25..979.40 rows=11259 width=51) Sort Key: ("A1"."IDENT")::text -> Seq Scan on "INCA_WS_CNS" "A1" (cost=0.00..193.59 rows=11259 width=51) -> Sort (cost=5581.94..5685.54 rows=41438 width=23) Sort Key: substr(("A2"."DIALLED_DIG_STR")::text, 1, 8) -> Seq Scan on cub030p005 "A2" (cost=965.80..2403.93 rows=41438 width=23) Filter: (NOT (hashed subplan)) SubPlan -> Coalesce (redistributed data) to node 0x5 (cost=0.00..0.00 rows=0 width=0) -> Distribute to all servers from node 0x4 (cost=0.00..0.00 rows=0 width=0) -> Seq Scan on cub030p010 (cost=0.00..807.84 rows=63184 width=23) (14 rows)


22

Rewrite query as follows: EXPLAIN INSERT INTO cub030p010 ( "CALL_DATE" ,"DIALLED_DIG_STR" ,"FK_NOP_ID" ,"CNSIDENT" ) SELECT "A2"."CALL_DATE" ,"A2"."DIALLED_DIG_STR" ,"A1"."FK_NOP_ID" ,('{xlat:substr' / (((1 * "A2"."DIALLED_DIG_STR") + (2 * 1)) + (3 * 8))) FROM "cub030p005" "A2" INNER JOIN "INCA_WS_CNS" "A1" ON "A2"."CALL_DATE" >= "A1"."EFFECTIVE_TSTAMP" AND "A2"."CALL_DATE" < "A1"."INEFFECTIVE_TSTAMP" AND ('{xlat:substr' / (((1 * "A2"."DIALLED_DIG_STR") + (2 * 1)) + (3 * 8))) = "A1"."IDENT" LEFT OUTER JOIN cub030p010 "A3" ON "A3"."CALL_DATE" = "A2"."CALL_DATE" AND "A3"."DIALLED_DIG_STR" = "A2"."DIALLED_DIG_STR" WHERE "A3"."DIALLED_DIG_STR" IS NULL; With explain plan QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Hash Join (cost=6353.42..6660.49 rows=6 width=25) Hash Cond: (("A1"."IDENT")::text = substr(("A2"."DIALLED_DIG_STR")::text, 1, 8)) Join Filter: (("A2"."CALL_DATE" >= "A1"."EFFECTIVE_TSTAMP") AND ("A2"."CALL_DATE" < "A1"."INEFFECTIVE_TSTAMP")) -> Seq Scan on "INCA_WS_CNS" "A1" (cost=0.00..193.59 rows=11259 width=51) -> Hash (cost=6353.42..6353.42 rows=1 width=23) -> Hash Left Join (cost=1519.63..6353.42 rows=1 width=23) Hash Cond: (("A2"."CALL_DATE" = "A3"."CALL_DATE") AND ("A2"."DIALLED_DIG_STR" = "A3"."DIALLED_DIG_STR")) Filter: ("A3"."DIALLED_DIG_STR" IS NULL) -> Seq Scan on cub030p005 "A2" (cost=0.00..1023.75 rows=82875 width=23) -> Hash (cost=1092.42..1092.42 rows=85442 width=23) -> Seq Scan on cub030p010 "A3" (cost=0.00..1092.42 rows=85442 width=23)

FULL OUTER JOINS

Full outer joins in sub-queries are not supported.

Oracle Connector: SUBSTR function

The values for the second parameter (position) of the SUBSTR function can be one of four cases: 0,1, > 1, < 0:


23

• 0 and 1 return the same string. • <0 2

nd parameter (position) is supposed to count characters back from the end of the string, but

Dataupia returns an error. • <0 3

rd parameter (length) returns null strings for Oracle (but bad column header and other output)

but Dataupia returns an error.

Oracle Connector: SELECT Query with INSTR

INSTR(4 arguments) is unsupported. That is, the 4 parameter version of INSTR is not supported at this time.

Oracle Connector: MOD function limitation

MOD works for all data types, with the following exception. Numbers larger than 0x1f ffff ffff ffff (9,007,199,254,740,991) can return incorrect results. Oracle casts input to MOD as a FLOAT. Oracle’s FLOAT is 126 bit. Dataupia supports 52-bit precision, therefore, the largest precision Dataupia supports is 2^53-1 (double-precision 8-byte number).

Oracle Connector: SYSDATE function

SYSDATE is supported and works correctly. However, there are some presentation limitations. The first limitation is that Oracle controls the time it presents depending on a server setting of time zone. Dataupia currently presents in GMT. The second limitation is that the default SYSDATE format in Oracle is just the date and for Dataupia, it is the date with time. Also, in Oracle you can control the presentation format with the NLS_DATE_FORMAT session variable This variable does not work for Dataupia. Three Workarounds:

1. Cast (SYSDATE as a TIMESTAMP).

This workaround is more of a change to the Oracle's output to always show SYSDATE as timestamp rather than DATETIME.

a. an example CAST(SYSDATE as TIMESTAMP)

SQL> SELECT CAST (SYSDATE as TIMESTAMP) from "test"; 09-JUL-08 06.54.08.000000 PM

2. Specify output as string.

This workaround is also a change to the Oracle's output as output is neither timestamp nor DATETIME, but a string.

a. an example. TO_CHAR(SYSDATE, ‘YY-DD-MON’);

SQL> SELECT TO_CHAR(SYSDATE, 'YY-DD-MON') from "test"; 08-09-JUL


24

SQL> SELECT TO_CHAR (SYSDATE, 'YY-DD-MON HH:SS') from "test"; 08-09-JUL 07:53

3. Use NLS_TIMESTAMP_FORMAT instead of NLS_DATE_FORMAT.

This workaround is a change in format of the timestamp returned by Dataupia to render similarly to Oracle's DATETIME .

a. an example ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY'

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY' Session altered. SQL> SELECT SYSDATE from "test"; 09-JUL-08

Oracle Connector: DECOMPOSE function

SELECT query with DECOMPOSE function returns "function decompose(character varying) does not exist" error message. For example: this statement returns an error: SQL>SELECT DECOMPOSE (colvarchar) FROM table_varchar;

Oracle Connector: Oracle doesn’t consider a parse error a transaction breaker

Currently, if the Dataupia backend has a problem with a transaction (say a parse error on update), the entire transaction is aborted. If Oracle has that kind of problem, the transaction is still intact on the Dataupia Satori Server.

Oracle Connector: TO_CHAR function

There are known limitations with TO_CHAR in this release.

• TO_CHAR function on a bigint data type throws error. Dataupia maps a bigint to a NUMBER(19).

• Currently, Dataupia might not select the expected TO_CHAR function. This means there isn't one that's an exact match (i.e. TO_CHAR(bigint)), but there's more than one that takes an argument that can be obtained from a bigint by coercion (for example, TO_CHAR(text), TO_CHAR (any element), TO_CHAR (double precision), etc).

• TO_CHAR format model parsing and FM support. The fm parameter is supported in this release,

but not in quite the same way as Oracle supports it. Consider the following date and format: July 7, 0008 "fmDD yy" With Oracle this will print something like: "7 8" For Dataupia this will print something like: "7 08"

Once fm occurs in a format string for Oracle, it stays set until it appears again which resets the format to non-fm. While using Dataupia the fm parameter only applies to the next format string.

Oracle Connector: TIMESTAMP function

Dataupia does not support certain time formats.


25

For example, this query : update "FILE_VIEW_PERF"@dt_relay set " DATE"='08-APR-08 12.00.00.000000 AM' where " DATE"='21-DEC-06 12.00.00.000000 AM' and "OPERATOR"=7;

results in format issues.

• Dataupia does not support STRUCT TIMESTAMP WITH TIME ZONE in this context: (#3141)

insert into insert_timestamp values(TIMESTAMP '2008-06-28 10:10:10 PST')

• You cannot create a view in Oracle for TIMESTAMP with TIME ZONE data type. Queries with the combination of TIMESTAMP and Integer may return an incorrect result.

Workaround: Rewrite the query to use an explicit cast:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD "00:00:00"';

• Oracle Connector Issue: Alter session NLS_DATE_FORMAT with TIMESTAMP specifier does

not take effect for *first* query ( #2537). If you use a format model that specifies a time component (which Oracle DATEs don't have), you get unexpected results in your output.

Workaround: You could do something like:

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

Instead of this: ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';


26

Oracle Connector: Dates addition or subtraction

Subtracting DATE or TIMESTAMP from TIMESTAMP is not supported in this release. These operations result in an INTERVAL data type, which is not supported in this release.

Adding or subtracting SYSDATE from TIMESTAMP is also not supported.

Usage Note: Dataupia treats DATE and numeric or integer additions/subtractions differently than Oracle as follows:

Operation Result

DATE ± INTEGER DATE

DATE ± NUMERIC TIMESTAMP

SYSDATE ± INTEGER DATE

SYSDATE ± NUMERIC TIMESTAMP

TIMESTAMP ± INTEGER TIMESTAMP

TIMESTAMP ± NUMERIC TIMESTAMP

Oracle Connector: GROUP_ID, GROUPING and GROUPING_ID functions

Queries with these functions return error messages.

Oracle Connector: stddev() function has limitations

The stddev() function is giving a blank for null values whereas Oracle returns 0. For the following query: SELECT STDDEV(coldouble), count(*) FROM num2006dec27 group by coldouble having coldouble <=8888.45;

The expected result is: 0|1

The actual result is: |1.

Oracle Connector: ROWNUM limitations

The ROWNUM Oracle function is supported for building sample data sets, however, it does not consistently return the same data.


27

Oracle Connector: Implicit conversion fails for character datatypes for trunc function

Currently, using the trunc function in an implicit conversion returns an error.

CLI, Installation Wizard and Management Console

DMC fails to create an array if its name doesn't start with a letter

The DMC has extra validation that checks that names start with a letter. So if you are naming an array through the DMC, the array name must start with a letter. If you are using the CLI to name an array, the array name does not have to start with a letter. Workaround: If you don’t want to start a name with a letter, use the CLI to name arrays.

DMC - Error installing Flash Player

Per the Adobe release notes, run 32-bit Firefox to install Flash on 64-bit Linux system. Adobe Flash Player is not supported for playback in a 64-bit browser. Workaround: To use the Flash Player to view Flash content on a 64-bit operating system, you must run a 32-bit browser.

DMC – Firefox 3.0 does not always display entire Summary drop-down menu

In the latest version of the Mozilla Firefox browser (version 3.0), the last item on the Summary drop-down menu (Upgrade) is not always displayed – it is sometimes obscured by the frame below the menu. Workaround: Uninstall Firefox 3.0 and install Firefox 2.0. Or use Internet Explorer.

Selecting a static IP address using the wizard generates an error

Workaround: Use the CLI instead of the wizard to set up an array.

Do not access qostest file with the DAS GUI

If you are navigating the folder structure with the CLI, you may notice a qostest project in the projects_old directory. This is a test project that can be used by Dataupia Support to diagnose technical issues, and should not be accessed with the Dynamic Aggregaion Studio GUI.

Transaction, DB, and Memory

The default for indexonload is now true

The default for indexonload is now true.

Previously, the default for indexonload was false, which resulted in indexes not being updated as data was added to the heap. This caused the index to try to catch up to the heap during read operations.


28

With indexonload set to true, read_only_indexed_rows should also be set to true (to improve performance).

Note: indexonload only affects bulk load; it does not affect insert.

Supported isolation levels

Dataupia ONLY supports "read committed" as an isolation level. The following isolation levels are not supported in this release:

• Serializable • UnCommitted Read • Repeatable Read

Memory can be exhausted on select count(distinct(col)) from <table>

Very large count(distinct) or operations with significant numbers of groups could consume large amounts or memory and take significant amount of time. For example, this issue arises when there are billions of rows returned. Usage Note: Avoid using a Select Count (Distinct(col)) when there are more than 100 million rows in an array.

Indices

IndexScan on "indexed expression" returns 0 rows Indexes defined on expressions return 0 rows, so you should avoid using indexed expressions. For example: create index bbitest_bbi_absy2 on bbitest using bbi ( ( abs(y2) ) );

This returns 0 rows. Workaround: refrain from using indexed expressions.

Installation

Run fix_loaded script when upgrading from 2.1 or earlier versions Note: When upgrading from 2.1 or earlier versions, you must run the following script to update the meta data tables: $DATAUPIA_HOME/bin/fix_loaded $DT_SYSTEM_ID


29

Installation (upgrade): configuration file overwritten, backups removed

If you are upgrading from an earlier version, the installer will overwrite the satori.conf configuration file, and will also remove any backup copies on file in the /opt/tms/lib/md/templates directory on the blade.

Workarounds:

� If you have custom settings in your configuration file, you will need to save the configuration file to another directory, install the upgrade, then paste the custom settings into the new satori.conf file.

� If you have backup copies you would like to save in the /opt/tms/lib/md/templates directory, you should copy these files to another location before installing the upgrade.

Oracle 11g Linux connector installation settings

The following settings required for the Connector installation on Linux:

� GLOBAL_NAMES must equal FALSE

� tnsnames.ora needs to have "localhost" for HOST in all entries, except for ORCL entry which can have servers IP address

� listener.ora should also have "localhost"

Loader

Data loader file format

In Release 2.2 the data description files which are passed to the data loader have a new format and several new requirements. There have been changes to the Control section of the file. The changes also affect any customizations you have made.

Please contact Dataupia Technical Support for instructions and assistance when upgrading your data description files.

int directive: use the opt=L option to specify leading blank spaces Due to improved error detection and handling, for the int directive you must now use the opt=L option to specify that leading blank spaces should be skipped.

TCP keepalive The TCP keepalive on the client side is now enabled all the time -- there is no administrative way to disable it. If the system-level keepalive idle time is set to below 10 minutes, the system settings will be retained. Otherwise the keepalive parameters will be set to 10 minutes followed by 5 probes 1 minute apart. Note that the system level keepalive parameters can be controlled by sysctl: sysctl net.ipv4 | grep keepalive

Note that the default Linux values are 2 hours followed by 9 probes 75 seconds apart.


30

Also you cannot control keepalive on per-socket basis in Solaris; the only controllable parameter seems to be tcp_keepalive_interval. Consequently, you have to make sure that its global setting is satisfactory for all of the applications running on the host. On Solaris, you can control the system parameters using ndd. The argument is in milliseconds: ndd -set /dev/tcp tcp_keepalive_interval 300000

PPO

"ERROR: distributed plan error" when running update When updating a hash distributed table, you may get the following error: "ERROR: distributed plan error." Workaround: if you get this error message when updating a hash distributed table:

� Make one of the tables (preferably the one with the lesser amount of data) an ALL distributed table, then run the update again. -or-

� If possible, make both tables hash distributed on the same column, then run the update again.

Nested SELECT queries with inner query containing ORDER BY and outer query containing LIMIT/ROWNUM return inconsistent results (as opposed to the consistent results in pure Oracle) Example query 1: SELECT * FROM (SELECT "COL2" FROM TEST_ROWNUM ORDER BY "COL1","COL2") WHERE ROWNUM < 6;

This query returns consistent results in Oracle, but returns inconsistent results in Dataupia. This is because nested queries are treated differently in Oracle and Dataupia SQL. Oracle treats the output of the nested query as a result set, whereas Dataupia treats it as a temporary table. Since Dataupia treats the result of the nested query as a temporary table, it is fetching the rows in a random order and the limit clause is picking the first five rows from the randomly returned rows. Workaround: modify the subquery as follows: SQL> SELECT "COL2" FROM TEST_ROWNUM WHERE ROWNUM < 6 ORDER BY "COL1","COL2";

This will return consistent results because Dataupia is applying ORDER BY before LIMIT. Example query 2: select aColumn from (select aColumn from tbl order by someColumn) subq;

This query should produce results in someColumn order, but does not. Workaround: add an ORDER BY to the outer select statement.


31

Appendix A: Dataupia SQL Support

• Release 2.2 supports SQL-92 and a number of SQL functions implemented by Oracle. This appendix lists the elements of SQL syntax that Dataupia supports.

Information in this document is subject to change without notice.

This appendix is organized as follows:

� Data types

� Operators

� Conditions

� Oracle functions

� Formatting

� Functionality Summary

Data Types

Data Types Supported

Release Note

BIGINT √ BINARY_DOUBLE BINARY_FLOAT BLOB CHAR √ CHARACTER √ CHARACTER VARYING √ CLOB DATE √ DECIMAL √ FLOAT √ INT[EGER] √ INTERVAL DAY TO SECOND INTERVAL YEAR TO MONTH LONG NATIONAL CHAR[ACTER VARYING] NATIONAL CHAR[ACTER] NCHAR NUMBER √ NUMERIC √ NVARCHAR2 REAL √ Precision conforms to IEEE Standard 754 with 6

decimal digits precision


32

Data Types Supported

Release Note

SMALLINT √ TIMESTAMP √ TIMESTAMP WITH LOCAL TIMEZONE TIMESTAMP WITH TIMEZONE VARCHAR2 √

Operators

Operators Supported

Release Note

Arithmetic Operators (+, -, *, /) √ Concatenation Operator (||) √ CHAR data type not supported DATE - DATE √ DATE - INTERVAL DATE - NUMERIC √ DATE - TIMESTAMP √ Fractional precision is limited to 6 digits DATE + INTERVAL DATE + NUMERIC √ INTERVAL - INTERVAL INTERVAL * NUMERIC INTERVAL / NUMERIC INTERVAL + DATE INTERVAL + INTERVAL INTERVAL + TIMESTAMP NUMERIC * INTERVAL NUMERIC + DATE √ NUMERIC + TIMESTAMP √ TIMESTAMP - DATE √ Fractional precision is limited to 6 digits TIMESTAMP - INTERVAL TIMESTAMP - NUMERIC √ TIMESTAMP - TIMESTAMP √ Fractional precision is limited to 6 digits TIMESTAMP + INTERVAL TIMESTAMP + NUMERIC √ Unary - √ Unary + √

Conditions

Conditions Supported Release Note

=, !=, <, >, <=, >= √


33


ALL √ AND √ ANY √ BETWEEN and NOT BETWEEN √ EXISTS √ EXISTS √ IN and NOT IN √ IS [NOT] EMPTY IS [NOT] INFINITE IS [NOT] NAN IS NULL, IS NOT NULL √ LIKE, NOT LIKE √ NOT √ OR √ REGEXP_LIKE SOME √

Note: These conditions may be affected by data type precision (i.e. real data type)

Transaction Control


BEGIN √ COMMIT [WORK] √ ROLLBACK √ ROLLBACK TO SAVEPOINT SAVEPOINT SET_TRANSACTION READ_ONLY

Oracle Functions

Functions Supported Release Note

ABS (N) √ ACOS (N) √ ADD_MONTHS (date, integer) ASCII (char) √ ASIN (N) √ ATAN (N) √


34


ATAN2 (N1, N2) √ AVG ([DISTINCT|ALL] N) √ CAST (BINARY FLOAT/DOUBLE AS CHAR/VARCHAR2)

CAST (BINARY FLOAT/DOUBLE AS DATE/TIME)

CAST (BINARY FLOAT/DOUBLE AS NUMBER)

CAST (CHAR/VARCHAR AS BINARY FLOAT/DOUBLE)

CAST (CHAR/VARCHAR AS DATE/TIME)

√

CAST (CHAR/VARCHAR AS NUMBER)

√

CAST (DATE/TIME AS CHAR/VARCHAR)

CAST (NUMBER AS BINARY FLOAT/DOUBLE)

CAST (NUMBER AS CHAR/VARCHAR)

CEIL (N) √ CHR (N) COALESCE (E1, E2, …) √ CONCAT (S1, S2) √ CHAR data type not supported CORR (E1, E2) √ COS (N) √ COSH (N) COUNT ([DISTINCT|ALL] [E1|*]) √ CURRENT_DATE CURRENT_TIMESTAMP ([precision])

DBTIMEZONE DECODE (E, S1, R1, [Sn, Rn]*, [D]) EXP (N) √ EXTRACT (DAY FROM datetime) √ EXTRACT (HOUR FROM datetime) √ EXTRACT (MINUTE FROM datetime)

√

EXTRACT (MONTH FROM datetime)

√

EXTRACT (SECOND FROM datetime

√

EXTRACT (YEAR FROM datetime) √ FIRST FLOOR (N) √ FROM_TZ (timestamp_value, timezone_value)


35


GREATEST (E1, E2, …) INITCAP (string) INSTR (string, substring) √ LAST LAST_DAY (date) LEAST (E1, E2, …) LENGTH (string) √ CHAR data type not supported LIKE √ LN (N) √ LNNVL (condition) √ LOCALTIMESTAMP (precision) LOG (N2, N1) √ FLOAT data type not supported LOWER (string) √ LPAD (S1, N, [S2]) √ LTRIM (string, [remove-set]) √ MAX (N) √ MEDIAN (N) MIN (N) √ MOD (N2, N1) √ MONTHS_BETWEEN (date1, date2)

NANVL (N2, N1) NEW_TIME (date, tz1, tz2) NEXT_DAY (date, day) NLS_INITCAP NLS_LOWER NLS_UPPER NLSSORT NULLIF (E1, E2) √ NVL (E1, E2) √ NVL2 (E1, E2, E3) PERCENT_RANK POWER (n2, n1) √ RANK (expression) REGEXP_LIKE REMAINDER (n2, n1) REPLACE (string1, string2, [string3])

√

ROUND (date, [fmt]) ROUND (N1, [N2]) √ RPAD (S1, N, [S2]) √ RTRIM (string, [remove-set]) √ SESSIONTIMEZONE SIGN (N) √ SIN (N) √ SINH (N) SOUNDEX (string)


36


SQRT (N) √ STDDEV ([DISTINCT|ALL] E1) √ STDDEV_POP (E1) STDDEV_SAMP (E1) SUBSTR (string, position, [length]) √ SUM ([DISTINCT|ALL] E1) √ SYSDATE √ SYS_EXTRACT_UTC SYSTIMESTAMP √ TAN (N) √ TANH (N) TO_BINARY_DOUBLE (E1, [fmt]) TO_BINARY_FLOAT (E1, [fmt]) TO_CHAR (datetime, [fmt]) √ TO_CHAR (E, [fmt]) √ TO_DATE (E, [fmt]) √ TO_DSINTERVAL TO_NUMBER (E, [fmt]) √ TO_TIMESTAMP (E, [fmt]) TO_TIMESTAMP_TZ TRANSLATE (E, from, to) √ TRIM ([LEADING|TRAILING|BOTH][string FROM] string)

√ Trim string is required

TRUNC (date, [fmt]) √ TRUNC (N1, [N2]) √ UPPER (string) √ USER VARIANCE ([DISTINCT|ALL] E) √ WIDTH_BUCKET

Formatting

Number Formatting Supported Release Note

$ (literal $ sign) √ , (number with commas) √ . (literal period) √ 0 (leading zeros) √

9 (specified number of digits)

√ Digits are right-aligned to the number of 9 digits specified. For example, “$9999” reads

in up to 4-digits left-blank padding between the literal ‘$’ and the number. That is, “$9999” with number 123 results in ‘$ 123’ rather than ‘ $123’.


37

Number Formatting Supported Release Note

B (leading space) √ C (currency) D (decimal) E (exponential) G (meta comma) L (local currency) √ MI (minus sign) √ PR (negative in brackets) √ RN and rn (roman numerals) S (sign character) √ TM (minimum number format) U (dual currency) V (multiplier) √

Date/Time Formatting Supported Release Note

/ - , . ; : punctuation marks √

AM or A.M, PM or P.M √

CC and SCC (century identifier)

D, DAY, DY (day of the week) √

DD (Day of month) √ Format not supported as sole input to dates

(e.g. to_date(colchar,’DD’)). Year required.

DDD (Day of year)

√ Format not supported as sole input to dates (e.g. to_date(colchar,’DDD’)). Year required.

DL and DS (long and short format)

FF (Fractional Seconds)

HH, HH12 and HH24 √ Format not supported as sole input to dates

(e.g. to_date(colchar,’HH’)). Year required.

I, IY, IYY, IYYY (last 1, 2 or 3 digits of ISO year or full ISO year)

IW (Week of year)

J (Julian)

MI (minute) √ Format not supported as sole input to dates

(e.g. to_date(colchar,’MI’)). Year required.

MM, MON, MONTH (Month) √

Q (Quarter of year) √

RM (Roman Numeral Month) √

RR (two digit 21st century)

RRRR (year modifier)

SS (Seconds) √ Format not supported as sole input to dates

(e.g. to_date(colchar,’SS’)). Year required.


38

Date/Time Formatting Supported Release Note

SSSSS (seconds past midnight)

TS (short time)

TZD, TZR (DST modifiers)

TZH, TZM, (Time Zone hour and minute)

W, WW (week of month or year) √

Y,YYY (year with comma as specified)

YEAR, SYEAR (year spelt out)

YYYY, YYYY, YYY, YY, Y (various year specifiers)

√

SYYYY (year specifier)

Note: Use of NLS format (e.g. NLS_DATE_FORMAT) is not currently supported for use with formatting. Therefore, input to dates may not solely contain formats excluding year, month, or day (e.g. to_date(colchar, ‘SS’));

Functionality Summary

This table provides a high-level overview of functionality in the area of transparency between Oracle and Dataupia. Some of the information is also included in the previous tables as it relates to specific SQL syntax.

Functionality Supported Release Note

Arithmetic Expressions √

CASE Expressions √

Column names up to 30 characters long

√

Comparison of data types √

Comparisons involving a NULL √

DATE Literal √

Datetime Expressions

Daylight Savings Time

DDL and DML Extensions

EXPLAIN command

Expressions involving a NULL value √

Functions involving NULL √

Implicit conversion of data types

√ Comparison of data types should generally

be more explicit to ensure the expected functionality

INTERVAL Literal

Name equivalence (quoting and case)

√

NUMERIC Literals [+-] NNNNN.NNNNN

√


39

Functionality Supported Release Note

NUMERIC Literals [+-]NNNN.NNNN[dD]

NUMERIC Literals [+-]NNNN.NNNN[fF]

NUMERIC Literals [+-]NNNNE[+-]NNNN

√

NUMERIC Literals [+-]NNNNN √

Object security

Oracle ROWNUM pseudo column √

ORACLE Sequence pseudo column

Scalar Sub-query Expressions √

SQL Command Length Greater than 2048 bytes

√

Support for Oracle 3 part naming

Text Literal enclosed in single quotes

√

Text Literal enclosed in the "q" syntax

TIMESTAMP Literal

TIMESTAMP WITH TIMEZONE Literal

dataupia satori server overview 2.2.pdf · publication part number: dt-2008-demm-001 distributed by...

Documents