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'

In addition to the Oracle compatibility features that are now generally available in Db2 Big SQL, you can use the SQL_COMPAT global variable to activate the following custom Oracle compatibility features. Unlike the IBM Common SQL Engine, which uses the DB2_COMPATIBILITY_VECTOR registry variable to enable these features, Db2 Big SQL uses the SQL_COMPAT global variable instead. The functionality, however, is the same.