IBM Support

PK76100: ENABLE PAIRWISE JOIN FOR STAR JOIN QUALIFIED QUERIES

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • Enable Pairwise Join for star join qualified queries
    .
    SQLPERFORMANCE SQLACCESSPATH
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 V9 for z/OS star join users who      *
    *                 enable star join on a DB2 subsystem by       *
    *                 setting a DB2 subsystem parameter            *
    *                 STARJOIN = ENABLE or a valid integer         *
    *                 value.                                       *
    ****************************************************************
    * PROBLEM DESCRIPTION: A new subsystem parameter EN_PJSJ is    *
    *                      added to DB2 V9 for z/OS by PK76100     *
    *                      so that "dynamic index ANDing for       *
    *                      star join", also known as "pair-wise    *
    *                      join", can be enabled for the           *
    *                      subsystem, where star join is enabled   *
    *                      by setting an existing parameter        *
    *                      STARJOIN to ENABLE or an acceptable     *
    *                      integer value.                          *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Prior to PK76100, in a star join enabled DB2 V9 subsystem,
    the star join access method "dynamic index ANDing", also
    known as "pair-wise join" was not activated for the subsystem.
    Consequently, star join users were not able to fully exploit
    the feature that can potentially improve the performance of
    star join qualified queries when applicable.
    
    By turning the new DB2 subsystem parameter, introduced by
    PK76100, EN_PJSJ to ON, the new star join method in DB2 V9
    for z/OS, "dynamic index ANDing", will be enabled for the
    subsystem, and the access paths using "dynamic index ANDing"
    will be considered at the query optimization time along with
    the existing star join methods.  If an access path with
    "dynamic index ANDing" is selected by DB2 for a query,
    it is indicated by JOIN_TYPE = 'P' in PLAN_TABLE for the
    corresponding joins when EXPLAIN is applied to the query.
    
    The new join method could improve execution performance of
    star join qualified queries when
    - The fact table has indexes that provide matching for join
      predicates with some dimension tables having local filtering.
      The indexes on the fact table can be single-key indexes (but
      not limited).
    - There is no multi-column index on the fact table that is
      suitable for the existing star join method.
    Therefore, the new star join method will provide more
    flexibility not only in star join access path selection for
    performance but also in the index design on fact tables.
    
    If it is not desirable to enable dynamic index ANDing for a
    star join enabled DB2 subsystem, users can keep the default
    value of the parameter, EN_PJSJ = OFF.  Please refer to
    the attached ++HOLD information for the details of the new
    parameter.
    
    Additional keywords:
    STARSCHEMA STARJOIN SQLSTARJOIN SQLPAIRWISEJOIN
    SQLACCESSPATH
    

Problem conclusion

Temporary fix

Comments

  • For users who enable pairwise starjoin (EN_PJSJ=ON), please
    refer to info APAR II14468 for additional APARs that are
    recommended to be applied.
    ž**** PE09/07/27 FIX IN ERROR. SEE APAR PK92405  FOR DESCRIPTION
    

APAR Information

  • APAR number

    PK76100

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-11-20

  • Closed date

    2009-02-17

  • Last modified date

    2009-09-11

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UK44120

Modules/Macros

  • DSN@ZP   DSNDQWPZ DSNDSPRM DSNTIDXA DSNTIJUZ
    DSNTINST DSNWZP   DSNXOGP  DSNXOMPS DSNXOSJO DSNXOSR  DSNXOTS
    DSNXRPJP DSN6SPRM
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • R910 PSY UK44120

       UP09/03/05 P F903

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 #: PK76100

Modified date: 11 September 2009


Translate this page: