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.
- 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
XMLTABLE
- A table user-defined function
- Is one of the following SQL statements:
- CALL
- SET host-variable assignment
- SET special register
- VALUES
- VALUES INTO
If a statement references objects with multiple CCSIDs, Db2 processes the statement as follows:
- 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.
- 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
- 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:- 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:
The following query joins those two tables.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 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: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;
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.L1 H1 L2 H2 7 4AC3BC7267656E 6 D1DC99878595