PI43424: UNION/UNION ALL WITH FETCH FIRST N ROWS ONLY CLAUSE CAUSES ACCESS PATH REGRESSION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• In V11, nested UNION or UNION ALL statements can cause an access
  path regression if the query also contains a FETCH FIRST n ROWS
  clause. This is due to QST blocking/disabling  the UNION
  distribution.
  
  
  
  
  
  
  
  
  Keywords:
  SQLUNION
  SQLUNIONALL
  SQLFFNR
  SQLACCESSPATH
  SQLPERFORMANCE
  

Local fix

• Customer removed the fETCH FIRST n ROWS clause
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of an SQL          *
  *                 statement containing UNION ALL and FETCH     *
  *                 FIRST clause.                                *
  ****************************************************************
  * PROBLEM DESCRIPTION: Different access path may be chosen     *
  *                      when an SQL statement contains FETCH    *
  *                      FIRST clause and with UNION ALL in the  *
  *                      table expression or view.               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The following example helps to illustrate the problem.
  
  CREATE TABLE TEST.T1(
    C1 INT ,C2 INT
  );
  
  SELECT DISTINCT C
    FROM (
      SELECT C1 AS C FROM TEST.T1
      UNION ALL
      SELECT C2 AS C FROM TEST.T1
  ) AS T
  FETCH FIRST 3 ROWS ONLY;
  
  DB2 does not process the FETCH FIRST clause properly which may
  result in different access path.
  
  After applying this APAR fix, access path or performance may be
  changed.
  

Problem conclusion

• DB2 is modified correctly to process FETCH FIRST clause
  to avoid the problem.
  
  Additional Keywords: SQLUNIONALL SQLGROUPBY SQLDISTINCT
                       SQLEXPLAIN SQLFFNR SQLSETFUNCTION
                       SQLACCESSPATH SQLPERFORMANCE
  

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:

  UI30189

Modules/Macros

• DSNXODSO
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI30189

     UP15/08/28 P F508

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.