DB2 Version 9.7 for Linux, UNIX, and Windows

DB2_COMPATIBILITY_VECTOR registry variable

The DB2_COMPATIBILITY_VECTOR registry variable is used to enable one or more DB2® compatibility features introduced since DB2 Version 9.5.

These features ease the task of migrating applications written for other relational database vendors to DB2 Version 9.5 or later.

Important: Enable these features only if they are required for a specific compatibility purpose. If DB2 compatibility features are enabled, some SQL behavior is changed from what is documented in the SQL reference information. To determine the potential impacts on your SQL applications, see the documentation that is associated with each compatibility setting.
This DB2 registry variable is represented as a hexadecimal value, and each bit in the variable enables one of the DB2 compatibility features.
  • Operating systems: All
  • Default: NULL; Values: NULL or 00 to FFFF. To take full advantage of these DB2 compatibility features, set the value to ORA for Oracle applications, SYB for Sybase applications, and MYS for MySQL applications. These are the recommended settings.

Registry variable settings

Table 1. DB2_COMPATIBILITY_VECTOR values
Bit position Compatibility feature Description
1 (0x01) ROWNUM 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 Resolves unqualified table references to 'DUAL' as SYSIBM.DUAL.
3 (0x04) Outer join operator Enables support for the outer join operator (+).
4 (0x08) Hierarchical queries Enables support for hierarchical queries using the CONNECT BY clause.
5 (0x10) NUMBER data type 1 Enables 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 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 use of the DATE data type as TIMESTAMP(0), a combined date and time value. When you create a database with this support enabled, the date_compat database configuration parameter is set to ON.
8 (0x80) TRUNCATE TABLE Enables alternate 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 length is 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) Data dictionary-compatible views 1 Enables the creation of 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 cursor Enables cursors defined WITH RETURN to be insensitive if the select-statement does not explicitly specify FOR UPDATE
14 (0x2000) INOUT parameter Enables the specification of DEFAULT for INOUT parameter declarations
17 (0x10000) SQL data access level enforcement Enables run-time routine SQL data access level enforcement
  1. Applicable only during database creation. Enabling or disabling this feature only affects subsequently created databases.
  2. See "Restrictions on PL/SQL support".

Usage

The DB2_COMPATIBILITY_VECTOR registry variable is set and updated using the db2set command. Set the DB2_COMPATIBILITY_VECTOR before creating your database:
  • To enable all of the supported Oracle compatibility features, set the registry variable to the value ORA (equivalent to the hexadecimal value 10FFF).
  • To enable all of the supported Sybase compatibility features, set the registry variable to the value SYB (equivalent to the hexadecimal value 3004).
When the DB2_COMPATIBILITY_VECTOR registry variable is set, all databases created should be created as UNICODE databases.

A new setting for the registry variable does not take effect until after the instance has been stopped and then restarted. Existing DB2 packages must be rebound for the change to take effect; packages that are not rebound explicitly will pick up the change on the next implicit rebind.

Example 1

This example sets 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 disable all compatibility features by resetting the DB2_COMPATIBILITY_VECTOR registry variable:
db2set DB2_COMPATIBILITY_VECTOR=
db2stop
db2start
Note that if a database has been created with the NUMBER data type or the VARCHAR2 data type enabled, use of the DATE data type as TIMESTAMP(0) enabled, or the creation of Oracle data dictionary-compatible views enabled, the database will still be enabled for these features after this db2set command executes.