PK76100: ENABLE PAIRWISE JOIN FOR STAR JOIN QUALIFIED QUERIES

Obtain the fix for this APAR.

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