DB2 10.5 for Linux, UNIX, and Windows

VARCHAR2 and NVARCHAR2 data types

The VARCHAR2 and NVARCHAR2 data types support applications that use the Oracle VARCHAR2 and NVARCHAR2 data types.

Enablement

You enable VARCHAR2 and NVARCHAR2 (subsequently jointly referred to as VARCHAR2) support at the database level, before creating the database where you require support. To enable the support, set the DB2_COMPATIBILITY_VECTOR registry variable to hexadecimal value 0x20 (bit position 6), and then stop and restart the instance to have the new setting take effect.
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.

Effects

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.

Comparisons involving varying-length string types use non-padded comparison semantics, and comparisons with only fixed-length string types continue to use blank-padded comparison semantics, with two exceptions:
  • Comparisons involving string column information from catalog views always use the IDENTITY collation with blank-padded comparison semantics, regardless of the database collation.
  • String comparisons involving a data type with the FOR BIT DATA attribute always use the IDENTITY collation with blank-padded comparison semantics.
The rules for result data types are modified as follows:
Table 1. Modified rules for result data types that involve character strings
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.

Functions that return character string arguments, or that are based on parameters with character string data types, also treat empty string CHAR, NCHAR, VARCHAR, or NVARCHAR values as null values. Therefore, the result of some built-in functions and casts that return character or graphic string can be null even when all of the arguments are not null. Special considerations apply for some functions when the varchar2_compat database configuration parameter is set to ON, as follows:
  • CONCAT function and the concatenation operator. A null or empty string value is ignored in the concatenated result. The result type of the concatenation is shown in the following table.
    Table 2. Data Type and lengths of concatenated operands
    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.
    Table 3. The italicized variables in the previous table have the following values
    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
  • DECODE function. If the first result expression is an untyped null it is assumed to be VARCHAR(0). If the first result expression is CHAR or GRAPHIC, it is promoted to VARCHAR or VARGRAPHIC.
  • GREATEST function. If the first expression is CHAR or GRAPHIC, it is promoted to VARCHAR or VARGRAPHIC.
  • INSERT function. A null value or empty string as the fourth argument results in deletion of the number of bytes indicated by the third argument, beginning at the byte position indicated by the second argument from the first argument.
  • LEAST function. If the first expression is CHAR or GRAPHIC, it is promoted to VARCHAR or VARGRAPHIC.
  • LENGTH function. The value returned by the LENGTH function is the number of bytes in the character string. An empty string value returns the null value.
  • NVL function. If the first expression is CHAR or GRAPHIC, it is promoted to VARCHAR or VARGRAPHIC.
  • NVL2 function. If the result expression is an untyped null it is assumed to be VARCHAR(0). If the result expression is CHAR or GRAPHIC, it is promoted to VARCHAR or VARGRAPHIC.
  • REPLACE function. If all of the argument values have a data type of CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC, then:
    • A null value or empty string as the second argument is treated as an empty string, and consequently the first argument is returned as the result
    • A null value or empty string as the third argument is treated as an empty string, and nothing replaces the string that is removed from the source string by the second argument.

    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.

  • SUBSTR function. References to SUBSTR are replaced with the following function invocation based on the first argument:
    • SUBSTRB when the first argument is a character string with string units defined as OCTETS.
    • SUBSTR2 when the first argument is a graphic string with string units defined as CODEUNITS16.
    • SUBSTR4 when the first argument is a character string or graphic string with string units defined as CODEUNITS32.
  • TO_CHAR function. If two arguments are specified and the first argument is a string, the first argument is cast to a decimal floating point. This behavior applies to Version 10.5 Fix Pack 3 and later fix packs.
  • TO_NCHAR function. If two arguments are specified and the first argument is a string, the first argument is cast to a decimal floating point. This behavior applies to Version 10.5 Fix Pack 3 and later fix packs.
  • TRANSLATE function. The from-string-exp is the second argument, and the to-string-exp is the third argument. If the to-string-exp is shorter than the from-string-exp, the extra characters in the from-string-exp that are found in the char-string-exp (the first argument) are removed; that is, the default pad-char argument is effectively an empty string, unless a different pad character is specified in the fourth argument.
  • TRIM function. If the trim character argument of a TRIM function invocation is a null value or an empty string, the function returns a null value.
  • VARCHAR_FORMAT function. If two arguments are specified and the first argument is a string, the first argument is cast to a decimal floating point. This behavior applies to Version 10.5 Fix Pack 3 and later fix packs.

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.

Empty strings in catalog view columns are converted to a blank character when the database configuration parameter varchar2_compat is set to ON. For example:
  • SYSCAT.DATAPARTITIONS.STATUS has a single blank character when the data partition is visible.
  • SYSCAT.PACKAGES.PKGVERSION has a single blank character when the package version has not been explicitly set.
  • SYSCAT.ROUTINES.COMPILE_OPTIONS has a null value when compile options have not been set.

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.

Restrictions

The VARCHAR2 data type and associated character string processing support have the following restrictions:
  • The VARCHAR2 length attribute qualifier CHAR is accepted only in a Unicode database as a synonym for CODEUNITS32.
  • The LONG VARCHAR and LONG VARGRAPHIC data types are not supported (but are not explicitly blocked) when the database configuration parameter varchar2_compat is set to ON.
  • Without specifying the maximum length for a VARCHAR2 parameter, the default is 4000 bytes.