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.
The following table specifies the settings that you require to enable individual compatibility features.
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. |
|
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.
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start
db2set DB2_COMPATIBILITY_VECTOR=03
db2stop
db2start
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: