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
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.
DB2 Version 9.7 for Linux, UNIX, and Windows