PM43583: SQLCODE203 IS INCORRECTLY ISSUED FOR COLUMN REFERENCE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Sqlcode -203 may be incorrectly issued when query contains
  column reference in V10. It can be reproduced by following
  simple testcase.
  --------------------------------
  CREATE TABLE T1 (C1 INTEGER);
  SELECT C1 FROM
  (SELECT C1+1 AS C1
  FROM MYTB ORDER BY MYTB.C1) T1;
  --------------------------------
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS users of table           *
  *                 expression with order by clause.             *
  ****************************************************************
  * PROBLEM DESCRIPTION: Unexpected SQLCODE -203 could be issued *
  *                      when a query satisfies the following    *
  *                      conditions:                             *
  *                      1. It contains a table expression       *
  *                      2. The table expression contains an     *
  *                         order by clause                      *
  *                      3. order by sort key contains a same    *
  *                         column name as a column in table     *
  *                         expression select list               *
  *                      or                                      *
  *                      Missing an expected SQLCODE -206 when   *
  *                      a query satisfies the following         *
  *                      conditions:                             *
  *                      1. It contains a table expression with  *
  *                         order by clause                      *
  *                      2. order by sort key is a column but    *
  *                         not in table expression select list  *
  *                      3. select the column occured in order   *
  *                         by sort key from the table           *
  *                         expression                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Unexpected SQLCODE -203 could be issued when a query satisfies
  the following conditions:
  1. It contains a table expression,
  2. The table expression contains an order by clause
  3. order by sort key contains a same column name as a column
     in table expression select list
  
  The following example recreates the SQLCODE:
  CREATE TABLE T2 (C1 INTEGER);
  SELECT C1 FROM
  (SELECT C1+1 AS C1 FROM T2 ORDER BY T2.C1) T1;
  SQLCODE -203 issued for above query.
  or
  Missing an expected SQLCODE -206 when a query satisfies the
  following conditions:
  1. It contains a table expression with order by clause
  2. order by sort key is a column but not in table expression
     select list
  3. select the column occured in order by sort key from the
     table expression
   The following examples could recreate this problem:
   CREATE TABLE T1 (C1 INTEGER, C2 CHAR(5));
   SELECT C2 FROM (SELECT C1 FROM T1 ORDER BY C2) A;
   SQLCODE -206 expected for above query, but it is not.
  

Problem conclusion

• DB2 is updated to handle the order by clause in table expression
  correctly.
  Additional Keywords: SQLORDERBY SQLTABLEEXPR SQLCODE203
                       SQLCODE206
  

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:

  UK71115

Modules/Macros

• DSNXOP0
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK71115

     UP11/09/07 P F109

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.