PH15783: INEFFICIENT ACCESS PATH CHOSEN WHEN JOIN ORDER OF MULTIPLE SMALLTABLES HAVE A JOIN RELATION WITH A HUGE TABLE AND DATA IS SKEWED

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Inefficient access path chosen when join order of multiple small
  tables have a join relation with a huge table and data is skewed
  on join columns of the large table .
  .
  Additional keywords: SQLJOIN SQLACCESSPATH SQLPERFORMANCE
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of Db2 12 for z/OS who have a                      *
  * query with star join patterns and                            *
  * ZPARM SJTABLES>10 and STARJOIN=DISABLE.                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Db2 may select inefficient join order                        *
  * for a query when two or more small                           *
  * tables have join relationship with a                         *
  * big table, which satisfies star join                         *
  * patterns.                                                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  Db2 may choose to join a big table early for queries with the
  following join patterns.
  (1) Two or more small tables have a join relationship with a
  big table;
  (2) There is data skew on the large table due to a join with a
  certain small table;
  (3) For one join predicate between a small table and the big
  table, it has higher column cardinality on the small table
  side than the other side.
  When the conditions above are met, Db2 may generate a plan
  joining the large table early. It may result in bad
  performance.
  Additional keywords:
  SQLACCESSPATH SQLPERFORMANCE SQLSTARJOIN
  

Problem conclusion

• The performance problem described above is  fixed by
  recognizing the star join pattern and generating an
  access plan more fit for the star-pattern queries.
  

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:

  UI66971

Modules/Macros

• DSNXOQSJ
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI66971

     UP19/12/20 P F912

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.