IBM Support

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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PK82356

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-03-11

  • Closed date

    2009-04-21

  • Last modified date

    2009-06-01

  • 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.



Document information

More support for: DB2 for z/OS

Software version: 9.1

Reference #: PK82356

Modified date: 01 June 2009


Translate this page: