PI66302: INCORROUT (MORE ROWS RETURNED) ON QUERY USING EXCEPT SET OPERATOR

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• INCORROUT (MORE ROWS RETURNED) ON QUERY USING EXCEPT SET
  OPERATOR.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All DB2 12 for z/OS users of the EXCEPT and INTERSECT        *
  * operators.                                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * When using the EXCEPT operator in a query that accesses data *
  * via hybrid  join in the first leg, the result can contain    *
  * more rows than expected.                                     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When using the EXCEPT operator in a query that accesses data via
  hybrid join for the first leg, the returned result can contain
  'more rows than expected'.
  
  
  
  The problem occurs because DB2 references an internal flag
  
  (before it was set) whose state can be incorrect.  This flag is
  
  referenced when removing duplicates and can affect the result
  
  set by keeping an unmatched or unqualified row.
  
  
  
  The following query provides a failing example.
  
  
    SELECT
      T1_DATA_DECLARE AS DATA_DECLARE
      FROM T1_CONTROL_DATA_DECLARE
          ,T2_CONTROL_DATA_ENTITY
     WHERE T1_DATA_DECLARE = T2_SITUATION
       AND T2_FPROCESS          >= T1_FPROCESS_INI
       AND T2_CENTIDAD           = 0229
       AND T2_CUSTSIT_DAT NOT IN ('NA', 'NO', 'PE')
    EXCEPT
    SELECT
     T3_SITUATION AS DATA_DECLARE
      FROM T3_DATA_DECLARE
     WHERE T3_CENTIDAD = 0229 ;
  
  For this example, the first leg uses a hybrid join access path
  and returns two rows.
  
     +--------------------+
     |    DATA_DECLARE    |
     +--------------------+
   1_| OPE                |
   2_| OPE                |
     +--------------------+
  
   The second leg returns a single row.
  
  
  
      +--------------------+
  
      |    DATA_DECLARE    |
  
      +--------------------+
  
    1_| OPE                |
  
      +--------------------+
  
  
  
   The query will incorrectly return a single row using the EXCEPT
  
   operator when none are expected.
  
  
  
     +--------------------+
  
     |    DATA_DECLARE    |
  
     +--------------------+
  
   1_| OPE                |
  
     +--------------------+
  
  
  
  
  
   Please note:  An incorrect result, likely 'less rows than
  
   expected', can similarly be returned for a query that uses the
  
   INTERSECT operator although it is less likely to occur.
  

Problem conclusion

• The code in DB2 is modified which supports both the EXCEPT and
  INTERSECT operators when the first leg contains hybrid join or
  access types that could be vulnerable to the first fetch
  internal flag checking.  This flag will no longer be referenced
  before it is set.  So, as a result 'matched' or 'unmatched' rows
  will not be kept that should not be.
  
  
  Additional Keywords: SQLINCORR SQLINCORROUT INCORROUT
  
  DB2INCORR/K SQLHYBRID HYBRIDJOIN SQLHYBRIDJOIN SQLEXCEPT
  
  SQLEXCEPTALL SQLINTERSECT SQLINTERSECTALL
  

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:

  UI39656

Modules/Macros

• DSNXRECP DSNXRITC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI39656

     UP16/08/18 P F608

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.