DB2 10.5 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 the DB2 product.

Registry variable settings

Values are as follows:
  • NULL (default). This mean that no compatibility features are supported.
  • A hexadecimal value of 0000 - FFFF. Each bit in the variable value enables an individual compatibility feature. For the meanings of each bit, see table-1.
  • ORA. This value, which is equivalent to the hexadecimal value 70FFF, enables all the DB2 compatibility features for Oracle applications.
  • SYB. This value, which is equivalent to the hexadecimal value 3004, enables all the DB2 compatibility features for Sybase applications.
  • MYS. This value, which is equivalent to the hexadecimal value 4000, enables all the DB2 compatibility features for MySQL applications.Currently, the only supported MySQL compatibility feature is enablement of the LIMIT and OFFSET clauses.
A setting of ORA, SYB, or MYS can enable all the compatibility features.
Important: Enable compatibility 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.

The following table specifies the settings that you require to enable individual compatibility features.

Table 1. DB2_COMPATIBILITY_VECTOR registry variable values
Bit position (hexadecimal value) Compatibility feature Description
1 (0x01) ROWNUM pseudocolumn Enables the use of the ROWNUM pseudocolumn as a synonym for the ROW_NUMBER() OVER() function and permits the ROWNUM pseudocolumn 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, which use 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 you do not specify it. 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. This value 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-compatible and PostgreSQL-compatible LIMIT and OFFSET clauses in 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.
19 (0x40000) Synonym usage Disables the use of synonyms in some SQL statements. When you set the DB2_COMPATIBILITY_VECTOR registry variable to restrict synonym usage, you cannot issue the alter, drop, rename, or truncate statements with a table synonym as the target. You cannot issue the alter or drop statements with a view synonym as the target. You cannot issue the alter or drop statement with a sequence synonym as the target.
  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.

Usage

You set and update the DB2_COMPATIBILITY_VECTOR registry variable by using the db2set command. You can set the DB2_COMPATIBILITY_VECTOR registry variable with combination of the compatibility features by adding the digits of the hexadecimals values that are associated with the compatibility features. 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 the supported Oracle compatibility features:
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start

Example 2

This example shows how to set the registry variable to provide both the ROWNUM pseudocolumn (0x01) and DUAL table (0x02) support that is specified in the previous table:
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 and then disable all the compatibility features, the database will still be enabled for these features: