Objects with different CCSIDs in the same SQL statement

You can reference data with different CCSIDs from the same SQL statement. This ability is useful if you use table objects such as tables, views, temporary tables, query tables, and user-defined functions with different CCSIDs. However, you should understand how Db2 for z/OS® processes these queries so that you can code them correctly.

Although the data that the statement references can have different CCSIDs, the SQL statement, including string constants, is written in only one CCSID. The CCSID that the SQL statement is written in is the source CCSID for your application.

Db2 for z/OS considers any SQL statement that satisfies at least one of the following criteria to be a statement that references objects with multiple CCSIDs:Begin general-use programming interface information.
  • References table objects with different CCSIDs
  • Contains any of the following functions:
    • ASCII_CHR
    • ASCII_STR
    • ASCIISTR
    • EBCDIC_CHR
    • EBCDIC_STR
    • CAST with the CCSID clause
    • CHR
    • DECRYPT_BIT
    • DECRYPT_CHAR
    • DECRYPT_DB
    • GETVARIABLE
    • GX
    • NORMALIZE_STRING
    • UNICODE_STR
    • UNISTR
    • UX
    • XML2CLOB
    • XMLSERIALIZE
    • Start of changeXMLTABLEEnd of change
    • A table user-defined function
  • Is one of the following SQL statements:
    • CALL
    • SET host-variable assignment
    • SET special register
    • VALUES
    • VALUES INTO
End general-use programming interface information.

If a statement references objects with multiple CCSIDs, Db2 processes the statement as follows:

  1. Db2 first determines the CCSID for each item that the statement references. Db2 uses the rules in the table that describes the operand types in Conversion rules for comparisons.
  2. Db2 then evaluates the predicates according to the rules that are listed in the Operand that supplies the CCSID for character conversion table in Conversion rules for comparisons.

Regardless of the CCSIDs of the referenced data, your application can receive the data in any CCSID that it wants. For example, suppose that your application selects rows from SYSIBM.SYSTABLES. The CCSIDs of the retrieved data are all Unicode CCSIDs. However, when you issue the SELECT statement, the data is returned to your application in your application encoding CCSID. This behavior is evident in the SPUFI application, which uses the EBCDIC encoding scheme. When you run a query against the Db2 catalog in SPUFI, EBCDIC data is returned.

Example statements that reference objects with different CCSIDs

Begin general-use programming interface information.
Example
Assume that EBCDICTABLE is encoded in EBCDIC, and the host variables are encoded in the application encoding scheme. SYSIBM.SYSTABLES is encoded in Unicode. Consider the following statement that references these objects with different CCSIDs:
SELECT A.NAME, A.CREATOR, B.CHARCOL, 'ABC', :hvchar, X'C1C2C3'
FROM SYSIBM.SYSTABLES A, EBCDICTABLE B
WHERE A.NAME = B.NAME AND
      B.NAME > 'B' AND
      A.CREATOR = 'SYSADM'
ORDER BY B.NAME

Db2 uses the following CCSIDs for each item that the statement references:

Part of statement Corresponding CCSID that Db2 uses during evaluation of the statement
A.NAME Unicode CCSID
A.CREATOR Unicode CCSID
B.CHARCOL EBCDIC CCSID
'ABC' Application encoding scheme CCSID1
:hvchar, Application encoding scheme CCSID1
X'C1C2C3' Application encoding scheme CCSID1
B.NAME EBCDIC
Notes:
  1. Application encoding scheme CCSID is the value of the ENCODING bind option.

Db2 then evaluates the statement as follows:

Part of statement Corresponding CCSID that Db2 uses during evaluation of the statement Reason
A.NAME = B.NAME Unicode CCSID Because both operands are columns and the CCSIDs are different, Db2 uses Unicode.
B.NAME > 'B' EBCDIC CCSID Because the first operand is a column and the second operand is a string, Db2 uses the CCSID of the first operand, which is EBCDIC.
A.CREATOR = 'SYSADM' Unicode CCSID Because the first operand is a column and the second operand is a string, Db2 uses the CCSID of the first operand, which is Unicode.

The result of this statement contains multiple CCSIDs. However, your application receives the result of this statement in the application encoding CCSID.

Example
Assume that you issue the following statements to create and populate a Unicode table and EBCDIC table:
CREATE TABLE TCCSIDU (CU1 VARCHAR(12)) CCSID UNICODE;
CREATE TABLE TCCSIDE (CE1 VARCHAR(12)) CCSID EBCDIC;
INSERT INTO TCCSIDU VALUES (‘Jürgen');
INSERT INTO TCCSIDE VALUES ('Jürgen');
The following query joins those two tables. 
SELECT LENGTH(A.CU1) AS L1, HEX(A.CU1) AS H1,
LENGTH(B.CE1) AS L2, HEX(B.CE1) AS H2
FROM TCCSIDU A, TCCSIDE B WHERE A.CU1 = B.CE1;
The WHERE predicate compares two columns with different CCSIDs. Column A.CU1 is encoded in Unicode. Column B.CE1 is encoded in EBCDIC. For this comparison, Db2 promotes B.CE1 to Unicode. Therefore Db2 evaluates the EBCDIC value 'Jürgen' in B.CE1 as equal to the Unicode value 'Jürgen' in A.CU1. This query returns the following result:
L1 H1 L2 H2
7 4AC3BC7267656E 6 D1DC99878595
Even though B.CE1 was promoted to Unicode for the comparison in the WHERE clause, the result still shows the EBCDIC hexadecimal value for B.CE1.
End general-use programming interface information.