PM90157: SQLCODE404 FROM DSNXRBND LOC 131 FOR A QUERY INVOLVING A UNICODE VARCHAR COLUMN

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE404 was issued from DSNXRBND loc 131 for a query
  involving a Unicode VARCHAR column due to an internal
  miscalculation of the target length.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 V9, 10 or 11 for z/OS users of an    *
  *                 SQL statement that contains a table          *
  *                 expression or view with SET operators, outer *
  *                 joins and multiple CCSIDs.                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: Incorrect output, SQLCODE -404, or      *
  *                      other unexpected behavior due to a      *
  *                      storage overlay could happen for an SQL *
  *                      statement that satisfies all of the     *
  *                      following conditions:                   *
  *                                                              *
  *                      1. It contains a table expression or    *
  *                         view with SET operator.              *
  *                      2. The CCSIDs of the tables in the SET  *
  *                         operation branches are different.    *
  *                      3. A null padding-side column of an     *
  *                         outer join exists in the SELECT list *
  *                         of one of the SET operation          *
  *                         branches.                            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An example is shown below:
  
  CREATE TABLE T1 ( C1 CHAR(4) NOT NULL, C2 CHAR(4))
    CCSID UNICODE;
  CREATE TABLE T2 ( C1 CHAR(4) NOT NULL, C2 CHAR(4))
    CCSID EBCDIC;
  CREATE TABLE T3 ( C1 CHAR(4) NOT NULL, C2 CHAR(4))
    CCSID EBCDIC;
  COMMIT;
  
  INSERT INTO T1 VALUES('C11', 'C21');
  INSERT INTO T1 VALUES('C12', 'C22');
  INSERT INTO T2 VALUES('C11', 'C21');
  INSERT INTO T2 VALUES('C12', 'C22');
  INSERT INTO T3 VALUES('C11', 'C21');
  INSERT INTO T3 VALUES('C12', 'C22');
  COMMIT;
  
  SELECT TX.C1
  FROM (SELECT T1.C1
        FROM T1
        UNION
        SELECT T3.C1
        FROM T2 LEFT OUTER JOIN T3
                ON T2.C1 = T3.C1
        ) TX(C1)
  ;
  
  The query above returns:
  
    +--------------+
    |      C1      |
    +--------------+
  1_| .C11         |
  2_| .C12         |
  3_| C11          |
  4_| C12          |
    +--------------+
  
  While the correct result should be:
  
    +--------------+
    |      C1      |
    +--------------+
  1_| C11          |
  2_| C12          |
    +--------------+
  
  DB2 did not correctly process the aforementioned SQL statements,
  which caused the incorrect output, SQLCODE -404 or other
  unexpected behavior.
  

Problem conclusion

• DB2 has been modified to correctly process the aforementioned
  SQL statement, so there will be no incorrect output, SQLCODE
  -404 or other unexpected behavior.
  
  Additional keywords: SQLTABLEEXPR SQLUNION SQLUNIONALL
                       SQLINTERSECT SQLINTERSECTALL SQLEXCEPT
                       SQLEXCEPTALL SQLCCSID SQLOUTERJOIN
                       SQLFULLJOIN SQLOUTER SQLFULL SQLJOIN
                       SQLCODE404 DB2OVRLAY/K
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UK96323 UK96324 UK96325

Modules/Macros

• DSNXOCT  DSNXONDA
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK96323

     UP13/08/16 P F308 ½

• RB10 PSY UK96324

     UP13/08/16 P F308 ½

• R910 PSY UK96325

     UP13/08/16 P F308 ½

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.