PH41307: PERFORMANCE ISSUE WHEN THE JPP IS NOT ALLOWED FOR QUERY USING VIEW WITH UNION ALL INCLUDING A SMALL TABLE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• If there is only a one table
  in the leg of UNION ALL, and the table is a small table,
  disallowing the join predicate pushdown and choosing tablespace
  scan for the query and for all the tables in subquery might
  cause performance regression.
  
  Keywords:
  SQLPERFORMANCE SQLACCESSPATH SQLUNIONALL SQLPUSHDOWN SQLVIEW
  SQLTABLEEXPR
  
  ADDITIONAL SYMPTOMS:
  SQLPERFORMANCE SQLACCESSPATH SQLUNIONALL SQLPUSHDOWN SQLVIEW
  SQLTABLEEXPR
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users with queries                       *
  * joining a table to a table expression or                     *
  * view which is a result from Union, Union                     *
  * All, or any other set operations.                            *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Suboptimal performance may occur                             *
  * when Db2 chooses to materialize a view                       *
  * or table expression which is a result                        *
  * from Union, Union All or other set                           *
  * operations.                                                  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  If a query contains a table joining to a table expression or
  view which is a result from Union, Union All, or any other set
  operations, Db2 may materialize the table expression or view
  when one leg of the set operation is a single table, and it
  can be accessed by an equal unique index before or after the
  join predicate is pushed down. The performance can be better if
  the join predicate is pushed down into the legs of the set
  operation.
  Additional Keywords:
  SQLPERFORMANCE SQLACCESSPATH SQLPREDPUSHDOWN SQLUNIONALL
  SQLUNION SQLINTERSECT SQLINTERSECTALL SQLEXCEPT SQLEXCEPTALL
  

Problem conclusion

• Db2 will pushdown the join predicate into the view or table
  expression for the described scenario.
  

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:

  UI78136

Modules/Macros

• DSNXOCS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI78136

     UP21/11/30 P F111

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.