DB2 Version 10.1 for Linux, UNIX, and Windows

DB2_COMPATIBILITY_VECTOR registry variable

The DB2_COMPATIBILITY_VECTOR registry variable enables one or more DB2® compatibility features. These features ease the task of migrating applications that were written for relational database products other than the DB2 product to DB2 Version 9.5 or later.

This registry variable is supported on Linux, UNIX, and Windows operating systems.

You can enable individual DB2 compatibility features by specify a hexadecimal value for the registry variable. Each bit in the variable value enables a different feature. Values are as follows:
  • NULL (default)
  • 0000 - FFFF
  • ORA, to take full advantage of the DB2 compatibility features for Oracle applications
  • SYB, to take full advantage of the DB2 compatibility features for Sybase applications
  • MYS, to take full advantage of the DB2 compatibility features for MySQL applications
A value setting of ORA, SYB, or MYS is recommended.
Important: Enable these features only if you require them for a specific compatibility purpose. If you enable DB2 compatibility features, some SQL behavior changes from what is documented in the SQL reference information. To determine the potential effects of a compatibility feature on your SQL applications, see the documentation that is associated with the compatibility feature.

Registry variable settings

The following table specifies the setting required to enable each compatibility feature.

Table 1. DB2_COMPATIBILITY_VECTOR registry variable values
Bit position (hexadecimal value) Compatibility feature Description
1 (0x01) ROWNUM pseudocolumn Enables the use of ROWNUM as a synonym for ROW_NUMBER() OVER() and permits ROWNUM to appear in the WHERE clause of SQL statements.
2 (0x02) DUAL table Resolves unqualified references to the DUAL table as SYSIBM.DUAL.
3 (0x04) Outer join operator Enables support for the outer join operator, which is the plus sign (+).
4 (0x08) Hierarchical queries Enables support for hierarchical queries using the CONNECT BY clause.
5 (0x10) NUMBER data type 1 Enables support for the NUMBER data type and associated numeric processing. When you create a database with this support enabled, the number_compat database configuration parameter is set to ON.
6 (0x20) VARCHAR2 data type 1 Enables support for the VARCHAR2 and NVARCHAR2 data types and associated character string processing. When you create a database with this support enabled, the varchar2_compat database configuration parameter is set to ON.
7 (0x40) DATE data type 1 Enables the interpretation of the DATE data type as the TIMESTAMP(0) data type, a combined date and time value. For example, "VALUES CURRENT DATE" in date compatibility mode returns a value such as 2011-02-17-10.43.55. When you create a database with this support enabled, the date_compat database configuration parameter is set to ON.
8 (0x80) TRUNCATE TABLE Enables alternative semantics for the TRUNCATE statement, under which IMMEDIATE is an optional keyword that is assumed to be the default if not specified. An implicit commit operation is performed before the TRUNCATE statement executes if the TRUNCATE statement is not the first statement in the logical unit of work.
9 (0x100) Character literals Enables the assignment of the CHAR or GRAPHIC data type, instead of the VARCHAR or VARGRAPHIC data type, to character and graphic string constants whose byte lengths are less than or equal to 254.
10 (0x200) Collection methods Enables the use of methods to perform operations on arrays, such as first, last, next, and previous. Also enables the use of parentheses in place of square brackets in references to specific elements in an array, for example, array1(i) refers to element i of array1.
11 (0x400) Oracle data dictionary-compatible views 1 Enables the creation of Oracle data dictionary-compatible views.
12 (0x800) PL/SQL compilation 2 Enables the compilation and execution of PL/SQL statements and language elements.
13 (0x1000) Insensitive cursors Enables cursors that are defined with WITH RETURN to be insensitive if the select-statement does not explicitly specify FOR UPDATE.
14 (0x2000) INOUT parameters Enables the specification of DEFAULT for INOUT parameter declarations.
15 (0x4000) LIMIT and OFFSET clauses Enables the use of the MySQL- and PostgreSQL-compatible LIMIT and OFFSET clauses on fullselect, UPDATE, and DELETE statements.
17 (0x10000) SQL data-access-level enforcement Enables routines to enforce SQL data-access levels at run time.
18 (0x20000) Oracle database link syntax Enables Oracle database link syntax for accessing objects in other databases.
  1. This feature is applicable only during database creation. Enabling or disabling this feature after creating a database affects only subsequently created databases.
  2. See "Restrictions on PL/SQL support" Restrictions on PL/SQL support.

Usage

You set and update the DB2_COMPATIBILITY_VECTOR registry variable by using the db2set command.
  • To enable all of the supported Oracle compatibility features, set the registry variable to the value ORA (equivalent to the hexadecimal value 30FFF).
  • To enable all of the supported Sybase compatibility features, set the registry variable to the value SYB (equivalent to the hexadecimal value 3004).
  • Currently, the only supported MySQL compatibility feature is enablement of the LIMIT and OFFSET clauses (0x4000).
A new setting for the registry variable does not take effect until after you stop and restart the instance. Also, you must rebind DB2 packages for the change to take effect. Packages that you do not rebind explicitly will pick up the change at the next implicit rebind.

If you set the DB2_COMPATIBILITY_VECTOR registry variable, create databases as Unicode databases.

Example 1

This example shows how to set the registry variable to enable all of the supported Oracle compatibility features:
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start

Example 2

This example shows how to set the registry variable to enable both ROWNUM (0x01) and DUAL (0x02):
db2set DB2_COMPATIBILITY_VECTOR=03
db2stop
db2start

Example 3

This example shows how to disable all compatibility features by resetting the DB2_COMPATIBILITY_VECTOR registry variable:
db2set DB2_COMPATIBILITY_VECTOR=
db2stop
db2start
If you create a database when any of the following features are enabled, the database will still be enabled for these features after this db2set command executes: