PK82356: VERY LONG PREPARE TIME AND BAD ACCESS PATH FOR A STAR JOIN QUERY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Two issues about a star join query:
  1.very long prepare time because a internal field is not set
  correctly
  2.bad access path in which snowflake group is not materialized
  where it should be
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: For all DB2 for z/OS star join users.        *
  ****************************************************************
  * PROBLEM DESCRIPTION: There are two problems to be resolved:  *
  *                      1. A star join query can have a long    *
  *                         prepare time when a snowflake group  *
  *                         is not materialized and includes     *
  *                         many tables.                         *
  *                      2. A star join query can have an        *
  *                         inefficient access path since a      *
  *                         filtering snowflake group is not     *
  *                         materialized.                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  1. Incorrect table dependency setting causes a long prepare time
  for a star join query when a snowflake group is not materialized
  and it includes many tables.
  
  2. An overestimated join size causes a filtering snowflake group
  to not become materialized; therefore, an inefficient access
  path can be chosen for a star join query.
  
  Additional Keywords:
  SQLSTARJOIN SQLACCESSPATH PERFM
  

Problem conclusion

• With the star join logic corrected, the long prepare time
  problem is resolved, and DB2 has a better chance to
  choose a more efficient access path.
  

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:

  UK45908

Modules/Macros

• DSNXOPKN DSNXOQSJ
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK45908

     UP09/05/06 P F905

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.