Compatibility features for Oracle
Db2® Big SQL provides features that enable applications that were written for an Oracle database to use Db2 Big SQL with only minor modifications.
In the Db2 IBM® Common SQL Engine, support for Oracle compatibility features is enabled by various settings in the DB2_COMPATIBILITY_VECTOR registry variable. In Db2 Big SQL, however, many Oracle compatibility features are enabled by default. For those features that cannot be enabled by default (because they conflict with existing function in syntax or semantics), Oracle compatibility is enabled through the SQL_COMPAT global variable. Do not use the DB2_COMPATIBILITY_VECTOR registry variable in Db2 Big SQL.
For more information, see Run Oracle applications and procedures on Big SQL and Run Oracle Applications on IBM Big SQL.
Features that are supported by default
Several Oracle compatibility features are always active. For information about the features that are always active in the IBM Common SQL Engine, see Compatibility features that are always active.
The following features are also enabled by default in Db2 Big SQL.
- Queries can use the outer join operator (+) as alternate syntax within predicates of the WHERE clause.
- A hierarchical query is a form of recursive query that retrieves a hierarchy, such as a bill of materials, from relational data by using a CONNECT BY clause.
- The ROWNUM pseudocolumn returns a number that indicates the order in which the returned row in a query result set was selected.
- The DUAL table DUAL table is a special one-row default table, similar to the SYSIBM.SYSDUMMY1 table.
- Changed syntax for the TRUNCATE statement causes a truncate operation to be processed immediately, regardless of whether the IMMEDIATE keyword is specified.
- Oracle data dictionary-compatible views are created automatically when you create a database.
Features that are supported when SQL_COMPAT='ORA'
- TRANSLATE scalar function syntax
The syntax and semantics of the TRANSLATE scalar function in ORA compatibility mode are different than those in Db2 Big SQL, but are similar to the syntax and semantics of the TRANSLATE scalar function when the SQL_COMPAT global variable is set to 'NPS®'. 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.
- SQL data-access-level enforcement
- Support for compiling and executing PL/SQL statements and other language elements
- Oracle database link syntax
- Synonym usage