In Big SQL 5.0.1 , out-of-the-box support is available for most Oracle’s SQL and PL/SQL dialects. This enables many applications that were written against Oracle to run in Big SQL virtually unchanged. Additional enhancements have been made in Db2 Big SQL 5.0.2 to provide data type toleration for Oracle’s proprietary VARCHAR2 and NUMBER data types.
Being able to immediately develop Big SQL applications affords a considerable advantage to customers and developers who have invested in Oracle SQL application and PL/SQL development skills.
The following native support will be provided:
- Oracle SQL dialect
- Implicit casting and type resolution
- Extended built-in function library
- SQL compatibility setup
- PL/SQL
- PL/SQL packages
- Built-in package library
Custom Oracle compatibility features
Although every effort was made to provide knob-less support for Oracle features, there are cases in which Big SQL is either syntactically or semantically different from Oracle. To handle those cases, you can use a new session-level variable, the SQL_COMPAT global variable, to activate the following custom Oracle compatibility features:
- TRANSLATE parameter syntax
- PL/SQL support
- SQL data-access-level enforcement
- Oracle database link syntax
The syntax and semantics of the TRANSLATE scalar function in Oracle compatibility mode are different from those in Big SQL. The from-string-exp is the second argument and the to-string-exp is the third argument. If to-string-exp is shorter than from-string-exp, the extra characters in from-string-exp that are found in char-string-exp (the first argument) are removed; that is, the default pad-char-exp argument is effectively an empty string, unless a different pad character is specified in the fourth argument.
PL/SQL (Procedural Language/Structured Query Language) statements can be compiled and executed by using data server interfaces that are provided by Big SQL. This reduces the complexity of enabling existing PL/SQL solutions to work with Big SQL.
This support enables routines to enforce data access levels at run time rather than at compile time.
This feature provides support for Oracle database link syntax by using @ (at sign).
See Compatibility features for Oracle for more information on the above features.
Attention: Setting of the DB2_COMPATIBILITY_VECTOR registry variable is not recommended in Big SQL. Custom compatibility features should be enabled only by using the SQL_COMPAT global variable.
The following code snippet shows you how to use the SQL_COMPAT session variable to enable Oracle compatibility for PL/SQL statements. A first attempt to create a simple PL/SQL routine fails, because SQL PL procedural language is the default in Big SQL. After the SQL_COMPAT session variable is set to ‘ORA’, the same PL/SQL routine is created successfully.
create or replace procedure plsql_proc (fetchval out integer) as cursor cur1 is select count(*) from syscat.tables ;-- begin open cur1 ;-- fetch cur1 into fetchval ;-- close cur1 ;-- end DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "integer) as cursor cur1 is select cou" was found following "l_proc (fetchval out". Expected tokens may include: "". LINE NUMBER=3. SQLSTATE=42601 call plsql_proc(?) Value of output parameters -------------------------- Parameter Name : FETCHVAL Parameter Value : 666 Return Status = 0 set sql_compat='ORA' DB20000I The SQL command completed successfully. create or replace procedure plsql_proc (fetchval out integer) as cursor cur1 is select count(*) from syscat.tables ;-- begin open cur1 ;-- fetch cur1 into fetchval ;-- close cur1 ;-- end DB20000I The SQL command completed successfully. call plsql_proc(?) Value of output parameters -------------------------- Parameter Name : FETCHVAL Parameter Value : 666 Return Status = 0 Oracle Proprietary Data Types
When enabling Oracle applications for Big SQL, use care around the Oracle proprietary data types. It is crucial to understand how to handle the nonstandard basic types, such as NUMBER, VARCHAR2, and DATE.
- The NUMBER data type is not a recognized data type and must be mapped to a different type. Map a NUMBER without a defined precision or scale to INTEGER or DECIMAL, depending on the intended use. Map a NUMBER with defined precision to the DECIMAL data type.
- The VARCHAR2 data type is not a recognized data type and must be mapped to VARCHAR. As a result, VARCHAR2 semantics do not apply, and an empty string (a zero-length string) will not be treated as a null value. This can change the semantics of some functions, such as CONCAT, for example. With Oracle semantics, ‘A’ CONCAT NULL returns ‘A’, but Big SQL returns a null value.
- The DATE data type in Oracle is like TIMESTAMP(0); it includes both a date and a time component. As such, it must be mapped to TIMESTAMP(0). Nonstandard handling of DATE or TIMESTAMP arithmetic is also not supported in Big SQL. For example, in Oracle, an expression like SYSDATE + 1/24 must be changed to SYSDATE + 1 HOUR.
The following table shows the data type mappings between Oracle nonstandard basic types and Hive, HBase, or Big SQL data types.
The following table shows the data type mappings between Oracle nonstandard basic types and Hive, HBase, or Big SQL data types.
| Oracle | Hive | HBase | Big SQL | VARCHAR2(n) | VARCHAR(n) | VARCHAR(n) | VARCHAR(n) |
|---|---|---|---|
| NUMBER | DECIMAL or INTEGER (recommend DECIMAL(31,0)) | DECIMAL or INTEGER (recommend DECIMAL(31,0)) | DECIMAL or INTEGER (recommend DECIMAL(31,0)) |
| NUMBER(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) |
| NUMBER(p) | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) |
| DATE | TIMESTAMP(0) | TIMESTAMP(0) | TIMESTAMP(0) |
NOTE: Starting in Db2 Big SQL 5.0.2, VARCHAR2 and NUMBER will be tolerated, and silently mapped to VARCHAR and DECIMAL respectively.
Beyond these basic types, there are other more complex types that are commonly used in Oracle’s PL/SQL, and that are available for Big SQL customers. These types are restricted to PL/SQL processing, and no additional mapping is needed; they are supported as is.
Thanks to its native multi-dialect SQL support, Big SQL facilitates the enablement of Oracle applications. Vendors of packaged applications can offer their applications for both Oracle and Big SQL at minimal additional cost. Customers can freely choose the vendor that offers the technology that they need without being limited by past choices.
For more detailed information about Oracle compatibility support in Big SQL, see Run Oracle Applications on IBM Big SQL.