DB2_COMPATIBILITY_VECTOR registry variable

The Db2® product provides optional features that simplify the task of migrating applications from other relational database products such as Oracle, Sybase, and MySQL. These features are inactive by default, but the DB2_COMPATIBILITY_VECTOR registry variable can be used to activate any subset of them.

Registry variable settings

The following values can be set for the DB2_COMPATIBILITY_VECTOR registry variable:
NULL
No compatibility features are activated. This is the default.
A hexadecimal number in the range 00000000 - FFFFFFFF
A hexadecimal number that represents a binary string. The value (0 or 1) of each bit position in the string indicates whether the corresponding compatibility feature is enabled (1) or disabled (0). Table 1 maps each bit to the feature that it controls.
ORA
This value improves the compatibility of Oracle applications. It activates the compatibility features for which there is a bullet in the ORA column of Table 1. (For more information about the Oracle compatibility features, see Oracle to DB2® Conversion Guide: Compatibility Made Easy.) In addition, it changes the default value of the DB2_DEFERRED_PREPARE_SEMANTICS registry variable to either 'YES' (in a single-byte character set environment) or 'YES_DBCS_GRAPHIC_TO_CHAR' (in a double-byte character set environment).
SYB
This value improves the compatibility of Sybase applications. It activates the compatibility features for which there is a bullet in the SYB column of Table 1. In addition, it changes the default value of the DB2_DEFERRED_PREPARE_SEMANTICS registry variable to 'YES'.
MYS
This value improves the compatibility of MySQL applications. It changes the default value of the DB2_DEFERRED_PREPARE_SEMANTICS registry variable to either 'YES' (in a single-byte character set environment) or 'YES_DBCS_GRAPHIC_TO_CHAR' (in a double-byte character set environment).

For more information about the DB2_DEFERRED_PREPARE_SEMANTICS registry variable, see Query compiler variables.

Important: When you enable a compatibility feature, some SQL behavior will vary from what is documented in the SQL reference information. These behavior differences are described in the documentation for the corresponding features.
Table 1. DB2_COMPATIBILITY_VECTOR bit positions
Bit position Hexadecimal value ORA SYB Compatibility feature Description
1 0x01 •   ROWNUM pseudocolumn This bit 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 This bit resolves unqualified references to the DUAL table as SYSIBM.DUAL.
3 0x04     (obsolete) This bit formerly activated support for the outer join operator. That feature is now always active and now this bit is ignored.
4 0x08 •   Hierarchical queries This bit enables support for hierarchical queries, which use the CONNECT BY clause.
5 0x10 •   NUMBER data type 1 This bit 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 This bit 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 This bit enables the interpretation of the DATE data type as the TIMESTAMP(0) data type so that it includes time information as well as date information. For example, in date compatibility mode, the statement "VALUES CURRENT DATE" returns a value like 2016-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 This bit enables alternative semantics for the TRUNCATE statement so that IMMEDIATE is an optional keyword and is the default. If the TRUNCATE statement is not the first statement in the logical unit of work, an implicit commit operation is carried out before the TRUNCATE statement is executed.
9 0x100 • • Character literals This bit enables the ability to assign a CHAR or GRAPHIC data type instead of a VARCHAR or VARGRAPHIC data type to a character or graphic string constant whose byte length is less than or equal to 254.
10 0x200 •   Collection methods This bit 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 This bit enables the creation of Oracle data dictionary-compatible views.
12 0x800 •   PL/SQL compilation 2 This bit enables the compilation and execution of PL/SQL statements and language elements.
13 0x1000   • Insensitive cursors This bit 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 This bit enables the specification of DEFAULT for INOUT parameter declarations.
15 0x4000     (obsolete) This bit formerly activated LIMIT and OFFSET support, but that feature is now always active and this bit is now ignored.
16 0x8000     (reserved) This bit is currently not used.
17 0x10000 •   SQL data-access-level enforcement This bit enables routines to enforce SQL data-access levels at run time.
18 0x20000 •   Oracle database link syntax This bit enables Oracle database link syntax for accessing objects in other databases.
19 0x40000 •   Synonym usage This bit 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 applies only at the time of database creation. Enabling or disabling this feature does not affect existing databases, but only newly 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 pick up the change at the next implicit rebind.

Example 1

To set the registry variable to enable all the supported Oracle compatibility features:
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start

Example 2

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

To disable all compatibility features by resetting the DB2_COMPATIBILITY_VECTOR registry variable:
db2set DB2_COMPATIBILITY_VECTOR=
db2stop
db2start