PK63976: SQLCODE332N FROM CCSID 1200 TO CCSID 65534 DSNXRIHB WHEN USING LIKE IN THE WHERE CLAUSE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE -332 when using LIKE in the where clause for a unicode
  table. Col1 is defined as GRAPHIC(30), Hostvariable is defined
  as GRAPHIC.
  
  WHERE col1 LIKE :HV     --> SQLCODE332N
  

Local fix

• WHERE col1 LIKE CAST (:HV AS GRAPHIC(30) CCSID 1200)
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 users who use the application        *
  *                 encoding scheme.                             *
  ****************************************************************
  * PROBLEM DESCRIPTION: Two related symptoms:                   *
  *                                                              *
  *                      1. SQLCODE332N from CCSID 1200 to CCSID *
  *                         65534 is issued from DSNXRIHB when a *
  *                         LIKE predicate is referenced using a *
  *                         graphic matching expression and a    *
  *                         string host variable or parameter    *
  *                         marker pattern.                      *
  *                                                              *
  *                      2. SQLCODE332N from CCSID 1208 to CCSID *
  *                         65534 is issued from DSNXODTR when a *
  *                         SBCS application encoding scheme is  *
  *                         used on a MIXED = YES system.        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  1. SQLCODE332N from CCSID 1200 to CCSID 65534 is issued from
     DSNXRIHB when a LIKE predicate is referenced using a graphic
     matching expression and a string host variable or parameter
     marker pattern.
  
     The following illustrates an SQLCODE -332 may be issued:
  
       Assumptions:
         1. It is a MEXED = YES system.
         2. The application encoding scheme is EBCDIC CCSID 37
            which does not have the Mixed and DBCS CCSIDs.
         3. The application does not specify its own ENCODING
            bind option and INHV is a CHAR VARYING variable.
  
         CREATE TABLE TE(C1 VARCHAR(10)) CCSID EBCDIC;
         CREATE TABLE TU(C1 VARGRAPHIC(10)) CCSID UNICODE;
  
         SELECT 1 INTO :OUTHV
           FROM TE, TU
           WHERE TU.C1 LIKE :INHV;
  
     The above SELECT statement is a multiple encoding scheme
     statement because it references an EBCDIC table and a
     Unicode table. Therefore, DB2 assigns the application
     encoding scheme to input host variable :INHV. Because the
     matching expression is graphic, DB2 determines to use the
     DBCS CCSID of the application encoding scheme for :INHV. But
     the application encoding scheme does not have a valid DBCS
     CCSID so DB2 uses 65534 to represent the missing DBCS CCSID.
     So during execution time when a Unicode graphic data is sent
     in for :INHV, SQLCODE -332 is issued.
  
  2. SQLCODE332N from CCSID 1208 to CCSID 65534 is issued from
     DSNXODTR when a SET CURRENT SQLID statement is used to set
     a string constant into special register CURRENT SQLID where
     the application encoding scheme is SBCS only but the system
     is a MIXED = YES system.
  
     The following illustrates an SQLCODE -332 may be issued:
  
       SET CURRENT SQLID = 'XYZ';
  
     Starting in V8 Enableing New Function Mode (ENFM), the SET
     special register statement is processed as a multiple
     encoding scheme statement. Therefore, DB2 interprets string
     constant 'XYZ' in applicatin encoding scheme MIXED CCSID.
     But if the application encoding scheme is SBCS only, there
     is no MIXED CCSID, thus, DB2 uses 65534 to represent the
     missing MIXED CCSID. When DB2 converts the string constant
     to 65534, SQLCODE -332 is issued.
  

Problem conclusion

• DB2 is fixed to use the Unicode DBCS CCSID for the input host
  variable or parameter marker described in the above example.
  DB2 now detects the missing MIXED CCSID in the application
  encoding scheme and uses the SBCS CCSID instead.
  
  Additional keywords: SQLCODE332N SQLLIKE SQLENCODING SQLMIXED
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK36596 UK36597

Modules/Macros

• DSNXOBFB DSNXODTR
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK36596

     UP08/06/04 P F806

• R910 PSY UK36597

     UP08/06/04 P F806

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.