DB2 Version 9.7 for Linux, UNIX, and Windows

VARCHAR2 and NVARCHAR2 data types

The VARCHAR2 and NVARCHAR2 data types are introduced to support applications that use the Oracle VARCHAR2 and NVARCHAR2 data type.

Support for VARCHAR2 and NVARCHAR2 (subsequently jointly referred to as VARCHAR2) is at the database level, and must be enabled before creating the database where support is required. This is achieved by setting the DB2_COMPATIBILITY_VECTOR registry variable to the appropriate value. When a database is created with VARCHAR2 support enabled, the database configuration parameter varchar2_compat is set to ON. After a database is created with VARCHAR2 support enabled, it cannot be disabled for that database, even if the DB2_COMPATIBILITY_VECTOR registry variable is reset. Similarly, all databases created with VARCHAR2 support disabled cannot have VARCHAR2 support enabled, even by setting the DB2_COMPATIBILITY_VECTOR registry variable.

Enablement

VARCHAR2 data type support is enabled by setting bit position number 6 (0x20) of the DB2_COMPATIBILITY_VECTOR registry variable before creating a database. A new setting for the registry variable does not take effect until after the instance has been stopped and then restarted.

To make use of the NVARCHAR2 data type, the database must be a Unicode database.

Usage

The following support is enabled for a DB2® database that has the varchar2_compat database configuration parameter set to ON.

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 the same as the maximum length for VARCHAR (that is, 32 672).

When the NVARCHAR2 data type is explicitly encountered in SQL statements, it is implicitly mapped to the VARGRAPHIC data type. The maximum length for NVARCHAR2 is the same as the maximum length for VARGRAPHIC (that is, 16 336).

Character string literals up to 254 bytes in length have a data type of CHAR. Character string literals longer than 254 bytes have a data type of VARCHAR.

Any 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 any 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 involving 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.

The 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. Special considerations apply for some functions when the varchar2_compat database configuration parameter is set to ON, and these are listed here.

CONCAT 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 attributes	Result
CHAR(A) CHAR(B)	<255	CHAR(A+B)
CHAR(A) CHAR(B)	>254	VARCHAR(A+B)
CHAR(A) VARCHAR(B)	-	VARCHAR(MIN(A+B,32672))
VARCHAR(A) VARCHAR(B)	-	VARCHAR(MIN(A+B,32672))
CLOB(A) CHAR(B)	-	CLOB(MIN(A+B, 2G))
CLOB(A) VARCHAR(B)	-	CLOB(MIN(A+B, 2G))
CLOB(A) CLOB(B)		CLOB(MIN(A+B, 2G))
GRAPHIC(A) GRAPHIC(B)	<128	GRAPHIC(A+B)
GRAPHIC(A) GRAPHIC(B)	>128	VARGRAPHIC(A+B)
GRAPHIC(A) VARGRAPHIC(B)	-	VARGRAPHIC(MIN(A+B,16336))
VARGRAPHIC(A) VARGRAPHIC(B)	-	VARGRAPHIC(MIN(A+B,16336))
DBCLOB(A) CHAR(B)	-	DBCLOB(MIN(A+B, 1G))
DBCLOB(A) VARCHAR(B)	-	DBCLOB(MIN(A+B, 1G))
DBCLOB(A) CLOB(B)		DBCLOB(MIN(A+B, 1G))
BLOB(A) BLOB(B)	-	BLOB(MIN(A+B, 2G))

DECODE. 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. If the first expression is CHAR or GRAPHIC, it is promoted to VARCHAR or VARGRAPHIC.
INSERT. 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. If the first expression is CHAR or GRAPHIC, it is promoted to VARCHAR or VARGRAPHIC.
LENGTH. 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. If the first expression is CHAR or GRAPHIC, it is promoted to VARCHAR or VARGRAPHIC.
NVL2. 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. 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: References to SUBSTR which have a character string input for the first argument will be replaced with an invocation to SUBSTRB. References to SUBSTR which have a national character (graphic) string input for the first argument will be replaced with an invocation to SUBSTR2.
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 9.7 Fix Pack 9 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 9.7 Fix Pack 9 and later fix packs.
TRANSLATE. 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. 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 9.7 Fix Pack 9 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.

Note: 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.

Restrictions

The VARCHAR2 data type and associated character string processing support have the following restrictions:

The VARCHAR2 length attribute qualifier CHAR is not accepted.
The LONG VARCHAR and LONG VARGRAPHIC data types are not supported (but are not explicitly blocked) when the varchar2_compat database configuration parameter is set to ON.