The VARCHAR2 and NVARCHAR2 data types support applications that use the Oracle VARCHAR2 and NVARCHAR2 data types.
db2set DB2_COMPATIBILITY_VECTOR=20
db2stop
db2start
To take full advantage of the DB2 compatibility
features for Oracle applications, the recommended setting for the
DB2_COMPATIBILITY_VECTOR is ORA, which sets all of the compatibility
bits.When you create a database with VARCHAR2 support enabled, the varchar2_compat database configuration parameter is set to ON.
If you create a database with VARCHAR2 support enabled, you cannot disable VARCHAR2 support for that database, even if you reset the DB2_COMPATIBILITY_VECTOR registry variable. Similarly, if you create a database with VARCHAR2 support disabled, you cannot enable VARCHAR2 support for that database later, even by setting the DB2_COMPATIBILITY_VECTOR registry variable.
To use the NVARCHAR2 data type, a database must be a Unicode database.
The effects of setting the varchar2_compat database configuration parameter to ON are as follows.
When the VARCHAR2 data type is explicitly encountered in SQL statements, it is implicitly mapped to the VARCHAR data type. The maximum length for VARCHAR2 is 32672 BYTE or 8168 CHAR which is the same as the maximum length for VARCHAR of 32672 OCTETS or 8168 CODEUNITS32. Similarly, when the NVARCHAR2 data type is explicitly encountered in SQL statements, it is implicitly mapped following the same rules as the NVARCHAR data type.
Character string literals can have a data type of CHAR or VARCHAR, depending on the length and the string units of the environment. Character string literals up to the maximum length of a CHAR in the string units of the environment (254 OCTETS or 63 CODEUNITS32) have a data type of CHAR. Character string literals longer than the maximum length of a CHAR in the string units of the environment have a data type of VARCHAR.
If one operand is... | And the other operand is... | The data type of the result is... |
---|---|---|
CHAR(x) | CHAR(x) | CHAR(x) |
CHAR(x) | CHAR(y) | VARCHAR(z), where x != y and z = max(x,y) |
GRAPHIC(x) | GRAPHIC(x) | GRAPHIC(x) |
GRAPHIC(x) | GRAPHIC(y) | VARGRAPHIC(z), where x != y and z = max(x,y) |
GRAPHIC(x) | CHAR(y) | VARGRAPHIC(z), where z = max(x,y) |
If the result type for the IN list of an IN predicate would resolve to a fixed-length string data type and the left operand of the IN predicate is a varying-length string data type, the IN list expressions are treated as having a varying-length string data type.
Character string values (other than LOB values) with a length of zero are generally treated as null values. An assignment or cast of an empty string value to CHAR, NCHAR, VARCHAR, or NVARCHAR produces a null value.
Operands | Combined length attributes1 | Result1 |
---|---|---|
CHAR(A) CHAR(B) | <=S | CHAR(A+B) |
CHAR(A) CHAR(B) | >S | VARCHAR(A+B) |
CHAR(A) VARCHAR(B) | - | VARCHAR(MIN(A+B, W)) |
VARCHAR(A) VARCHAR(B) | - | VARCHAR(MIN(A+B, W)) |
CLOB(A) CHAR(B) | - | CLOB(MIN(A+B, X)) |
CLOB(A) VARCHAR(B) | - | CLOB(MIN(A+B X)) |
CLOB(A) CLOB(B) | CLOB(MIN(A+B, X)) | |
GRAPHIC(A) GRAPHIC(B) | <=T | GRAPHIC(A+B) |
GRAPHIC(A) GRAPHIC(B) | >T | VARGRAPHIC(A+B) |
GRAPHIC(A) VARGRAPHIC(B) | - | VARGRAPHIC(MIN(A+B, Y)) |
VARGRAPHIC(A) VARGRAPHIC(B) | - | VARGRAPHIC(MIN(A+B, Y)) |
DBCLOB(A) CHAR(B) | - | DBCLOB(MIN(A+B, Z)) |
DBCLOB(A) VARCHAR(B) | - | DBCLOB(MIN(A+B, Z)) |
DBCLOB(A) DCLOB(B) | DBCLOB(MIN(A+B, Z)) | |
1. See the following table for values for italicized variables. |
Variable | If no operand has string units of CHAR (or CODEUNITS32) |
If either operand has string units of CHAR (or CODEUNITS32 |
---|---|---|
S | 254 | 63 |
T | 127 | 63 |
W | 32672 | 8168 |
X | 2G | 536870911 |
Y | 16336 | 8168 |
Z | 1G | 536870911 |
If any argument value has a data type of CLOB or BLOB and any argument is the null value, the result is the null value. All three arguments of the REPLACE function must be specified.
In the ALTER TABLE statement or the CREATE TABLE statement, when a DEFAULT clause is specified without an explicit value for a column defined with the VARCHAR or the VARGRAPHIC data type, the default value is a blank character.
If SQL statements use parameter markers, a data type conversion that affects VARCHAR2 usage can occur. For example, if the input value is a VARCHAR of length zero and it is converted to a LOB, the result will be a null value. However, if the input value is a LOB of length zero and it is converted to a LOB, the result will be a LOB of length zero. The data type of the input value can be affected by deferred prepare.
When defining a data type, CHAR can be used as a synonym for CODEUNITS32, and BYTE can be used as a synonym for OCTETS.