Introduction

Starting with Db2 Big SQL 4.2 December refresh , Big SQL now has out-of-the-box support for Oracle’s SQL and PL/SQL dialects. This allows many applications written against Oracle to execute in Big SQL virtually unchanged. In this article, get a high-level overview of what Oracle compatibility means in Big SQL. These features have been added to Big SQL across the last several releases. 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.

Many large organizations have significant in-house skills in Oracle SQL dialect and PL/SQL procedures. Very often the barrier to adopting new technologies might not necessarily be the availability of tools and software, but can be the straightforward lack of time to develop alternative skills and skillsets to leverage these new technologies. With Big SQL, IBM is providing the software to enable the integration of SQL and HADOOP data alongside the benefit of enterprise class data analytics.

Big SQL will provide the ability to use Oracle SQL dialect out of the box, as well as provide the ability to enable Oracle PL/SQL procedure support. This is of considerable advantage to customers and developers who have invested in Oracle SQL application and PL/SQL development skills, thus enabling immediate development of Big SQL applications.

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

Oracle SQL Dialect

The very fabric of the SQL dialects, their keywords and semantics differ in some areas. Big SQL has been extended to support many of the key features of the Oracle SQL dialect with no additional configuration needed.

Feature Description
CONNECT BY recursion Oracle-style CONNECT BY has been added, including the various functions and pseudo columns such as LEVEL and CONNECT_BY_PATH.
Oracle join syntax (+) This syntax is actually discouraged even by Oracle, but there are numerous applications and developers who still use this form of OUTER JOIN syntax.
DUAL table A single-row, single-column table used pervasively in Oracle applications as a dummy.
ROWNUM pseudo column This pseudo column is typically used to limit the number of rows returned and to enumerate rows in a result set.
ROWID pseudo column Rowids are used to quickly retrieve a row that was previously fetched based on its physical address.
MINUS SQL operator In Oracle, MINUS is used instead of EXCEPT to subtract one result set from another.
PUBLIC SYNONYM A public synonym is an alias without a schema name. Supports public synonyms for table objects, sequences, and PL/SQL packages.
CREATE OR REPLACE object statements This notation allows you to replace objects if they already exist with new versions.
Named parameters (=>) You can invoke procedures and functions by associating arguments to parameters by name using a => notation, rather than relying on positional association.
Relaxed name resolution Does not require inline views to be named. Also, column names can be inherited more easily from set operators such as UNION.

Implicit Casting and Type Resolution

Big SQL supports implicit casting (or weak typing) meaning that strings and numbers can be compared, assigned, and operated on in a very flexible fashion.

In addition, untyped NULLs can be used in many places and untyped parameter markers can be used nearly anywhere, thanks to deferred prepare, which is now the default behavior for Big SQL. That is, Big SQL will not resolve the type of a parameter marker until it has seen the first actual value.
To round out implicit casting, Big SQL also supports defaulting of routine parameters as well as the association of arguments to parameters by name.

Extended Built-in Function Library

All database products provide libraries of functions to operate on the data. The problem is that no two use the same names for these functions, even if in the end, the functionality is very similar. In addition to it the SQL standard set of functions, Big SQL supports a library of built-in functions compatible with Oracle. The following list provides a quick overview, but it is by no means an exhaustive list:

  • Conversion and cast functions:
    • TO_DATE
    • TO_CHAR
    • TO_CLOB
    • TO_NUMBER
    • TO_SINGLE_BYTE
    • TO_TIMESTAMP

    Each of these functions supports a rich set of compatible formatting strings.

  • Date time:
    • EXTRACT
    • ADD_MONTHS
    • MONTHS_BETWEEN
    • NEXT_DAY
  • String manipulation:
    • INITCAP
    • INSTR
    • INSTRB
    • INSTR2
    • INSTR4
    • LENGTHB
    • LENGTH2
    • LENGTH4
    • LISTAGG
    • LPAD
    • LTRIM
    • REGEXP_COUNT
    • REGEXP_INSTR
    • REGEXP_LIKE
    • REGEXP_REPLACE
    • REGEXP_SUBSTR
    • RPAD
    • RTRIM
    • SUBSTRB
    • SUBSTR2
    • SUBSTR4
  • Numeric
    • WIDTH_BUCKET
  • Miscellaneous
    • NVL
    • NVL2
    • HEXTORAW
    • DECODE
    • LEAST
    • GREATEST
    • BITAND
    • RATIO_TO_REPORT
  • Aggregate
    • CUME_DIST
    • PERCENT_RANK
    • COVAR_SAMP
    • COVAR_POP
    • MEDIAN
    • PERCENTILE_CONT
    • PERCENTILE_DISC
    • STDDEV_SAMP
    • VAR_SAMP
  • OLAP
    • CUME_DIST
    • PERCENT_RANK

The greatly increased overlap in supported functions between the two products implies a greatly improved out-of-the-box success, enabling an Oracle application to Big SQL.

SQL Compatibility Setup

Although every effort is made to provide knobless support for the Oracle features, there are cases where the existing SQL support in Big SQL is either syntactically or semantically different than Oracle. To handle those cases, a new session level variable, SQL_COMPAT, has been introduced so that users can control which dialect to use in a particular session. The following statement would be used to enable Oracle dialect:

 

set SQL_COMPAT='ORA';

When this session variable is set to ORA, the session will be enabled for Oracle which includes the following features:

  • TRANSLATE parameter syntax
  • The syntax and semantics of the TRANSLATE function in ORA compatibility mode are different than 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. For more information, see TRANSLATE scalar function.

  • PL/SQL support
  • PL/SQL (Procedural Language/Structured Query Language) statements can be compiled and executed using data server interfaces provided by Big SQL. This reduces the complexity of enabling existing PL/SQL solutions to work with the Big SQL.

  • SQL data-access-level enforcement
  • Provides support for to have routine enforce data-access levels at run time rather than at compile time.

  • Oracle database link syntax
  • Provides support for Oracle database link syntax using the @ (at sign).

PL/SQL Support

Commonly, when an application is ported from one product to another, the SQL and procedural language is translated from one SQL dialect to the other. This poses several problems, including:

  • The resulting translated code tends to be convoluted due to automation and impedance mismatch between the source and target dialect.
  • The application developers are not familiar with the target SQL language dialect which makes it hard to debug the ported code. Over time, further maintenance becomes a challenge due to the lack of skills.
  • In the case of packaged applications, translation needs to be repeated for every new release of the application.
  • In the end, the result is an emulation, which by definition runs slower than the original.

To avoid these issues, Big SQL includes native PL/SQL support. The Big SQL engine includes a PL/SQL compiler side by side with the existing SQL PL compiler. Both compilers produce virtual machine code for Big SQL’s SQL Unified Runtime Engine. Big SQL provides a session level variable to allow the user to choose which procedural language to use.

PL/SQL syntax details

So what exactly does PL/SQL support imply? First, there is the core syntax support. Big SQL supports all the common constructs of PL/SQL, such as the following.

  • if then else
  • while loops
  • := assignments
  • local variables and constants
  • #PRAGMA EXCEPTION and exception handling
  • Various forms of for loops (range, cursor, and query)
  • %TYPE and %ROWTYPE anchoring of variables and parameters to other objects
  • Local types can be declared within any PL/SQL block to be consumed within that block.
  • Local procedures can be declared within PL/SQL blocks and be called from within that same block.
  • SUBTYPE declarations are used to restrict variables and parameters to domains within built-in types.
  • #PRAGMA AUTONOMOUS transactions, which allow procedures to execute in a private transaction.
  • CCFLAGS conditional compilation allows different sections of PL/SQL to be compiled based on context.
  • Vendors can obfuscate precious intellectual property in PL/SQL by wrapping PL/SQL objects such as package bodies using the DBMS_DDL.WRAP and DBMS_DDL.CREATE_WRAPPED functions.

PL/SQL object support

PL/SQL can be used in various different objects that allow procedural logic.

  • Scalar functions including support for the following.
    • INOUT and OUT function parameters
    • Invocation using named parameter association
    • Parameter defaulting
    • Runtime “purity” enforcement
  • Pipelined table functions including support for the following.
    • Invocation using named parameter association
    • Parameter defaulting
  • Before each row or statement multi-action triggers.
  • After each row or statement multi-action triggers.
  • Procedures including support for the following.
    • INOUT and OUT procedure parameters.
    • Invocation using named parameter association.
    • Parameter defaulting.
  • Anonymous blocks.
  • PL/SQL packages.

PL/SQL package support

Most PL/SQL in Oracle applications is contained within so-called PACKAGEs. A PL/SQL package is a collection of individual objects with the ability to differentiate between externally accessible objects and those that are mere helpers for use within the package. The ANSI SQL equivalent of a package is a MODULE. Big SQL now provides support for ANSI SQL modules as well as PL/SQL packages. In particular, the following capabilities are provided:

  • CREATE [OR REPLACE] PACKAGE, which defines prototypes for externally visible routines. It also defines all externally visible, non-procedural objects, such as variables and types.
  • CREATE [OR REPLACE] PACKAGE BODY, which implements all private and public routines as well as all other private objects.
  • Within a package or package body, the following objects can be defined:
    • Variables and constants
    • Data types
    • Exceptions
    • Scalar functions
    • Procedures
    • Cursors
  • Package initialization.
  • Public synonyms on packages.

Oracle Data Type Support

When migrating from Oracle to Big SQL, careful consideration is needed for handling of the Oracle proprietary data types. To enable Oracle applications to run on Big SQL, it is crucial to understand how to handle the non-standard basic types, such as VARCHAR2, NUMBER, and DATE types.

Oracle data type Hive Data type HBase data type Big SQL data type
VARCHAR2(n) VARCHAR(n) VARCHAR(n) VARCHAR(n)
NUMBER DECIMAL or INTEGER DECIMAL or INTEGER DECIMAL or INTEGER
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)

Notes:

  • The NUMBER data type is not a recognized data type, so will need to be mapped to different type. For NUMBER, without a precision or scale, the recommendation would be to map it to INTEGER or DECIMAL depending on the usage of the data type. For NUMBER with a precision, the DECIMAL data type is recommended.
  • The VARCHAR2 data type is not a recognized data type, so will need to be mapped to VARCHAR. As a result, VARCHAR2 semantics will not apply, and an empty string (0-length string) will not be treated as a NULL value. This can change the semantics of some functions; for example CONCAT. With Oracle semantics, ‘A’ CONCAT NULL will return ‘A’. In Big SQL, NULL will be returned.
  • The DATE data type in Oracle is like a TIMESTAMP(0); it includes both a date with a time component. As such, it will need to be mapped to TIMESTAMP(0). In addition to not directly supporting the Oracle DATE type, non-standard handling of DATE / TIMESTAMP arithmetic is also not supported in Big SQL. For example, in Oracle, an expression like SYSDATE + 1/24 would need to be changed to SYSDATE + 1 HOUR.


NOTE: Starting in Db2 Big SQL 5.0.2, VARCHAR2 and NUMBER will be tolerated, and silently mapped to VARCHAR and DECIMAL respectively easing the enablement process.

Beyond the handling of these basic types, there are other more complex types that are commonly used in Oracle’s PL/SQL 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.

Data type Description
BOOLEAN This type can be used in procedural logic, variables, and parameters to routines. It cannot be used in a create table.
VARRAY This type can be used in procedural logic, variables and parameters to routines. It cannot be used a create table.
INDEX BY In addition to regular procedural arrays, Big SQL allows associative arrays to be used in procedures.
ROW TYPE This composite type can be used in variables and parameters, and as an element to arrays and associative arrays.
Ref Cursor type Cursors can be assigned to variables or passed around using parameters.

Oracle-specific JDBC Extensions

JDBC is a standard Java client interface. There are, however, extensions that have been added to Oracle’s JDBC driver in order to support specific non-standard data types.

To maximize the level of compatibility for Java technology-based applications, the Big SQL JDBC driver provides, among other things, support for calling procedures with reference cursor, VARRAY, and ROW parameters.

SQL*PLUS Script Support using CLPPLUS

Oftentimes, DDL scripts and even reports are written using the SQL*Plus command-line processor. To make it easier to transfer these scripts as well as the skills of developers writing them, Big SQL provides an SQL*Plus-compatible command-line processor, called CLPPlus.

The tool provides the following functionality.

  • SQL*Plus-compatible command options
  • Variable substitution
  • Column formatting
  • Reporting functions
  • Control variables

Frequently Asked Questions

Which version of Oracle does Big SQL support?

The coverage provided for the SQL and PL/SQL dialects is strictly based on what is being used by applications and requested by customer and vendors. Some of these features have been introduced in recent releases, while some constructs available in Oracle 8i are not supported. If there is a feature or function that is needed and not currently supported, please submit a request for the functionality.

What are common complications in enabling from Oracle to Big SQL?

The compatibility of Big SQL is obviously not 100 percent, so there will likely be some hiccups when you first enable the application. Many of these challenges are trivial and easy to fix. For example, Big SQL does not support locally declared functions. So you would need to pull declared functions out into the package or replace them with locally declared procedures.

In addition, since the Oracle data types VARCHAR2, NUMBER, Oracle DATE are not supported in HIVE or HBase tables, special care might be needed to map those types to another type that is supported. Also, in addition to not directly supporting the Oracle DATE type, non-standard handling of DATE / TIMESTAMP arithmetic is also not supported in Big SQL. For example, in Oracle, an expression like SYSDATE + 1/24 would need to be changed to SYSDATE + 1 HOUR.

Can I use the DB2_COMPATIBILITY_VECTOR in Big SQL?

It is strongly recommended that the DB2_COMPATIBILITY_VECTOR not be modified when using Big SQL. As part of Big SQL installation, some Oracle compatibility features are enabled by default, and hence changing the setting could result in loss of compatibility features.

Conclusion

Thanks to its native multi-dialect SQL support, Big SQL allows for easy enablement of Oracle applications. Vendors of packaged applications can offer their applications on Oracle and Big SQL at minimal additional cost. Customers can freely choose the vendor that offers the technology they need without being limited by past choices.

2 comments on"Run Oracle Applications on IBM Big SQL"

  1. Head to Head Oracle SQL and Big SQL.
    Question is, once you set the compatibility to Oracle by following command

    set SQL_COMPAT=’ORA’;

    How does then why does Oracle DataType Number, Date, VARCHAR2 is not recognized, wondering why these Datatypes are not similar when everything else made similar ?

    • Kathy McKnight July 28, 2017

      Hi Rizwan,
      The data types mentioned above are very Oracle specific. These data types and their specific semantics are not supported in the Hadoop Ecosystem. As such, when moving data from Oracle to Big SQL, careful consideration is needed for handling of these Oracle proprietary data types. For example, depending on usage, the NUMBER data type can be mapped to INTEGER, BIGINT or DECIMAL. The other important thing that customers need to understand is that the data is not owned by Big SQL, hence the Oracle specific semantics cannot be maintained. For example, an empty string will NOT be treated as a NULL value. This is what is explained in the Oracle Data Tyep Support section above.

      I hope that answers your questions.

Join The Discussion

Your email address will not be published. Required fields are marked *