IBM Support

DB2_COMPATIBLITY_VECTOR yields unexpected results if turned on/off after the database is created



DB2_COMPATIBLITY_VECTOR changes SQL behaviors and enables compatibility features that DB2 does not normally use. The registry variable set to DB2_COMPATIBLITY_VECTOR=ORA allows the user to take full advantage of the DB2 compatibility features for Oracle applications.


When the DB2_COMPATIBLITY_VECTOR variable is unset after the database is created with DB2_COMPATIBLITY_VECTOR=ORA, it might yield unexpected results.

The same can be said for vice versa: when the database is created with the default DB2_COMPATIBLITY_VECTOR unset, and it is set to ORA afterwards.


When the registry variable DB2_COMPATIBLITY_VECTOR is set to ORA, it will enable compatibility features and also make changes to SQL behaviors that is different from what is documented in the SQL reference information.

When the registry variable is unset (by default), the compatibility features will not be available and SQL will function normally as DB2 is designed.

Therefore, if DB2_COMPATIBILITY_VECTOR is turned on or off after a database is created, the database might run into issues and unusual errors.

1) If the variable was originally set to ORA, you create the database, then you unset it and leave it.
2) If the variable was originally unset, you create the database, then you set it to ORA and leave it.

Diagnosing The Problem

At present, the DB2_COMPATIBILITY_VECTOR variable is not supported if the user runs into issues modifying the variable after the database creation.

If you are unsure whether the database you have created previously is under ORA or not, you can run the following command to verify:

db2 get db cfg for <dbname>

This means it was created with DB2_COMPATIBILITY_VECTOR unset:

Number compatibility = OFF
Varchar2 compatibility = OFF
Date compatibility = OFF

This means it was created with DB2_COMPATIBILITY_VECTOR=ORA:

Number compatibility = ON
Varchar2 compatibility = ON
Date compatibility = ON

As long as DB2_COMPATIBILITY_VECTOR is set to the same parameter when the database was created, the database will function correctly.

1) The variable is set to ORA, you create database, unset, then set it to ORA again.
2) The variable is unset, you create database, set it to ORA, then unset it again.

Resolving The Problem

The value of DB2_COMPATIBILITY_VECTOR must match the way in which the database was created.

If more than one database is required such that one is Oracle compatibility enabled and one that is not, the recommended approach is to create two separate DB2 instances -- one to manage databases with the variable turned off, and the other with the variable turned on.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Database Objects\/Config - Instance","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;10.1;10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018