PM74173: INCORROUT CAN OCCUR WHEN USING OPTIMIZER HINTS FOR A SELECT WITH A CORRELATED SUBQUERY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrect output can occur for a query with a correlated
  subquery that is using optimizer hints to determine the best
  access path.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 for z/OS executing a            *
  *                 query that has a correlated subquery         *
  *                 using optimization plan hints or             *
  *                 APREUSE.                                     *
  ****************************************************************
  * PROBLEM DESCRIPTION: THE PROBLEM THIS FIX ADDRESSES HAS THE  *
  *                      FOLLOWING REPORTED SYMPTOMS:            *
  *                                                              *
  *                         INCORRECT OUTPUT(LESS ROWS FOUND)    *
  *                                                              *
  *                      PROBLEM SCENARIO:                       *
  *                                                              *
  *                      The reported incorrect output occurred  *
  *                      when executing a query that has a       *
  *                      correlated subquery while using         *
  *                      optimization plan hints.                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  SUMMARY OF DB2 EVENTS:
  DB2 was attempting to choose a table from plan table hints to
  use for the desired access path. While searching, DB2
  picked an incorrect match for a correlated subquery
  which caused DB2 to generate output that had zero rows.
  This could also cause a problem when using APREUSE.
  

Problem conclusion

• DB2 has been fixed to make the correct selection when
  searching optimization plan table hints for the best match
  when using a query with correlated subquery. With this fix,
  there is a small possibility that when optimization hints are
  specified for the subquery only and not the parent query block
  that some hints that worked in the past may no longer work,
  causing a +395 SQLCODE.  The +395 is issued to prevent exposure
  to incorrect output for queries with such hints specified.
  
  ADDITIONAL KEYWORDS:
  SQLINCORR INCORROUT SQLSUBQUERY SQLCORRSUBQ DB2INCORR/K APREUSE
  SQLINCORROUT OPTHINT OPTHINTS SQLCODE395
  

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:

  UK90452

Modules/Macros

• DSNXOPCO
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK90452

     UP13/01/01 P F212

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.