PK85482: SQLCODE407 WHEN RUNNING AN UPDATE WHERE A VIEW AND INLIST PRECESSING IS INVOLVED

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE407 when running an UPDATE where a view and inlist
  precessing is involved  DB2INCORR/K
  

Local fix

• split the update into 2 updates with only 1 entry in the inlist
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 for z/OS V8 and DB2 9 for z/OS       *
  *                 users of left or right outer join            *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE407 or incorrect number of rows  *
  *                      returned on an SQL statement with       *
  *                      outer join in a subquery or table       *
  *                      expression                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An SQL statement that contains a left outer join or right outer
  join in a table expression of an UPDATE statement may
  incorrectly return SQLCODE -407.  The table expression needs to
  be executed more than once and contain columns in its result set
  that are defined as NOT NULL but are nullable because of the
  outer join operation.  The last row of the table expression's
  result set must have null values.
  
  The null values are not cleared before the second execution
  of the table expression.  Because the values are not cleared,
  the wrong result set is returned which may cause an SQLCODE
  -407 or incorrect output.  The access path for the outer join
  must be sort merge join.  The problem does not occur for nested
  loop join.
  For example,
  
  * note that the columns are defined as NOT NULL
  CREATE TABLE TA
         (TA_ID1 INTEGER NOT NULL
          TA_ID2 INTEGER NOT NULL  WITH DEFAULT);
  
  CREATE TABLE TB
         (TB_ID1 INTEGER NOT NULL
          TB_CREF INTEGER NOT NULL
          TB_COL3 INTEGER NOT NULL WITH DEFAULT );
  
  CREATE TABLE TC
        (TC_COL1  INTEGER NOT NULL
        ,TC_COL2  INTEGER WITH DEFAULT NULL);
  
  CREATE TABLE TS  (
      COL2 INTEGER NOT NULL
      ID1 INTEGER NOT NULL );
  
  * The problem occurs evaluating the result set of the
  * table expression with the outer join.   OPTHINTS can
  * be used to get a sort merge join access path for the
  * outer join.
  UPDATE TB B
  SET B.TB_COL3 = (
      SELECT C.TC_COL2
      FROM TC C
        INNER JOIN TA A   ON A.TA_ID2 = C.TC_COL1
        INNER JOIN ( SELECT A.TA_ID1 AS V1_ID1 ,
                     COALESCE ( S.COL2, A.TA_ID1 ) AS V1_LOJID
                     FROM  TA A
                     LEFT OUTER JOIN TS S
                       ON S.ID1 = A.TA_ID1 ) V
                 ON   V.V1_LOJID = A.TA_ID1
        INNER JOIN TB B3  ON B3.TB_ID1 = V.V1_ID1
      WHERE B.TB_CREF = B3.TB_CREF
      )
  WHERE B.TB_CREF IN (3503,3899)
  
  SQLCODE -407 is incorrectly issued because the table expression
  returns the wrong result set when it is executed the second
  time.
  This problem can occur for queries with table expressions or
  subqueries involving outer join.  For a query, the number of
  rows returned for the query is incorrect.
  

Problem conclusion

• DB2 has been changed to correctly initialize the null byte for
  the outer join result buffers when processing the inner table
  for sort merge join.
  
  Additional keywords : SQLINCORROUT SQLOUTERJOIN SQLCODE407
                        SQLCORRSUBQ SQLLEFTJOIN SQLRIGHTJOIN
  

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:

  UK50461 UK50462

Modules/Macros

• DSNXROJL
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK50461

     UP09/10/10 P F910

• R910 PSY UK50462

     UP09/10/10 P F910

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.