DB2 Version 9.7 for Linux, UNIX, and Windows

Rules for string conversions

The code page used to perform an operation is determined by rules which are applied to the operands in that operation. This section explains those rules.

These rules apply to:
  • Corresponding string columns in fullselects with set operations (UNION, INTERSECT and EXCEPT)
  • Operands of concatenation
  • Operands of predicates (with the exception of LIKE)
  • Result expressions of a CASE expression and the DECODE scalar function
  • Arguments of the scalar function COALESCE (also NVL and VALUE)
  • Arguments of the scalar functions GREATEST, LEAST, MAX, and MIN
  • The source-string and insert-string arguments of the scalar function OVERLAY (and INSERT)
  • Expression values of the in list of an IN predicate
  • Corresponding expressions of a multiple row VALUES clause.

In each case, the code page of the result is determined at bind time, and the execution of the operation may involve conversion of strings to the code page identified by that code page. A character that has no valid conversion is mapped to the substitution character for the character set and SQLWARN10 is set to 'W' in the SQLCA.

The code page of the result is determined by the code pages of the operands. The code pages of the first two operands determine an intermediate result code page, this code page and the code page of the next operand determine a new intermediate result code page (if applicable), and so on. The last intermediate result code page and the code page of the last operand determine the code page of the result string or column. For each pair of code pages, the result is determined by the sequential application of the following rules:
  • If the code pages are equal, the result is that code page.
  • If either code page is BIT DATA (code page 0), the result code page is BIT DATA.
  • In a Unicode database, if one code page denotes data in an encoding scheme that is different from the other code page, the result is UCS-2 over UTF-8 (that is, the graphic data type over the character data type). (In a non-Unicode database, conversion between different encoding schemes is not supported.)
  • For operands that are host variables (whose code page is not BIT DATA), the result code page is the database code page. Input data from such host variables is converted from the application code page to the database code page before being used.
Conversions to the code page of the result are performed, if necessary, for:
  • An operand of the concatenation operator
  • The selected argument of the COALESCE (also NVL and VALUE) scalar function
  • The selected argument of the scalar functions GREATEST, LEAST, MAX, and MIN
  • The source-string and insert-string arguments of the scalar function OVERLAY (and INSERT)
  • The selected result expression of the CASE expression and the DECODE scalar function
  • The expressions of the in list of the IN predicate
  • The corresponding expressions of a multiple row VALUES clause
  • The corresponding columns involved in set operations.
Character conversion is necessary if all of the following are true:
  • The code pages are different
  • Neither string is BIT DATA
  • The string is neither null nor empty

Examples

Example 1: Given the following in a database created with code page 850:

Expression Type Code Page
COL_1 column 850
HV_2 host variable 437
When evaluating the predicate:
   COL_1 CONCAT :HV_2
the result code page of the two operands is 850, because the host variable data will be converted to the database code page before being used.
Example 2: Using information from the previous example when evaluating the predicate:
   COALESCE(COL_1, :HV_2:NULLIND,)
the result code page is 850; therefore, the result code page for the COALESCE scalar function will be code page 850.