PI56056: INCORROUT (less rows returned) when a query has JOIN predicate, combination of AND/OR predicates and multiple SUBSTR functions

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrect output (less rows returned) can happen on a SQL
  statement that satisfies ALL of the following conditions:
  
  - JOIN
  - combination of AND/OR predicates
  - SUBSTR functions in:
      IN predicates with more than one IN-list item, or
      BETWEEN predicates, or
      equal predicates
  - SUBSTR is on a column declared as VARCHAR (or VARGRAPHIC) 254
    or larger
  - SUBSTR has to start from position 1
  
  Additional keywords to use when searching:
  INLIST SQLINLIST
  DB2INCORR/K
  INCORROUT
  SQLINCORR
  SQLINCORROUT
  

Local fix

• Disable query re-write hidden zparm
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1. All users of DB2 10 and DB2 11 for z/OS   *
  *                 who use local predicates with literal        *
  *                 strings that are 254 bytes long or longer    *
  *                                                              *
  *                 2. All users of DB2 11 for z/OS who use      *
  *                 queries use SUBSTR is on a column declared   *
  *                 as VARCHAR or VARGRAPHIC with length         *
  *                 of 254 or longer                             *
  ****************************************************************
  * PROBLEM DESCRIPTION: On both DB2 10 and DB2 11, incorrect    *
  *                      output (too few rows returned) can      *
  *                      occur for a query meeting all of the    *
  *                      following conditions:                   *
  *                      - involves at least 2 tables            *
  *                      - has a combination of AND/OR           *
  *                        predicates                            *
  *                      - has local predicates containing       *
  *                        literal strings of 254 bytes long     *
  *                        or longer                             *
  *                                                              *
  *                      On DB2 11, incorrect output (too few    *
  *                      rows returned) can occur for a query    *
  *                      meeting all of the following            *
  *                      conditions:                             *
  *                      - involves at least 2 tables            *
  *                      - has a combination of AND/OR           *
  *                        predicates                            *
  *                      - SUBSTR functions in local predicates  *
  *                      - SUBSTR is on a column declared as     *
  *                        VARCHAR or VARGRAPHIC with length     *
  *                        of 254 or longer                      *
  *                      - SUBSTR has a starting position of 1   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  On both DB2 10 and DB2 11, incorrect output (too few rows
  returned) can occur for a query meeting all of the
  following conditions:
  - involves at least 2 tables
  - has a combination of AND/OR predicates
  - has local predicates containing literal strings of 254 bytes
    long or longer
  
  On DB2 11, incorrect output (too few rows returned) can occur
  for a query meeting all of the following conditions:
  - involves at least 2 tables
  - has a combination of AND/OR predicates
  - SUBSTR functions in local predicates
  - SUBSTR is on a column declared as VARCHAR or VARGRAPHIC with
    length of 254 or longer
  - SUBSTR has a starting position of 1
  
  An incorrect set of added predicates are generated for the
  query, which leads to incorrect output.
  
  Additional Keywords:
  SQLINCORROUT SQLINCORR INCORROUT DB2INCORR/K
  SQLSUBSTR SQLBETWEEN SQLIN SQLVARCHAR SQLVARGRAPHIC
  

Problem conclusion

• DB2 code is updated to generate the correct added predicates
  or the correct predicate structure for queries matching the
  conditions above.
  

Temporary fix

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

Comments

• ×**** PE16/06/10 FIX IN ERROR. SEE APAR PI63512  FOR DESCRIPTION
  ×**** PE16/06/10 FIX IN ERROR. SEE APAR PI63512  FOR DESCRIPTION
  

APAR Information

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

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

  UI35552 UI35553

Modules/Macros

• DSNXOPPD
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI35552

     UP16/03/08 P F603 Ž

• RB10 PSY UI35553

     UP16/03/08 P F603 Ž

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.