IBM Support

IT27875: DB2 MAY COPY SELECTIVITY WHEN A SELECTIVITY CLAUSE IS USED ON A PREDICATE THAT'S COLUMN EQUIVALENT (C1=C2 AND C1=C3)

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • This behavior may happen when the following conditions are met;
    
    - Registry variable DB2_SELECTIVITY has been set to YES
    - There are more than 1 equivalent predicates using the same
    column on the left so it's eligible for column equivalency
    
    Furthermore, DB2 may under-estimate cost when the following
    addditonal conditions are satisfied:
    - Registry variable DB2_CORRELATED_PREDICATES=FORCE_CGS. This is
    the default setting in 11.1 
    - Column group statics are collected on the columns in the join
    equality predicates such that DB2 optimizer may adjust filter
    factor.
    
    To reproduce,
    
    create table t1(c1 int);
    create table t2(c1 int);
    create table t3(c1 int);
    
    And get a plan and check the optimized query;
    
    
    Original Statement:
    ------------------
    select
      *
    from
      t1,
      t2,
      t3
    where
      t1.c1=t2.c1 selectivity .12 and
      t1.c1=t3.c1 selectivity .13
    
    
    Optimized Statement
    -------------------
    SELECT
      T1.C1 AS "C1",
      T2.C1 AS "C1",
      T3.C1 AS "C1"
    FROM
      JINHYON1.T3 AS T3,
      JINHYON1.T2 AS T2,
      JINHYON1.T1 AS T1
    WHERE
      (T1.C1 = T3.C1 SELECTIVITY 0.130000) AND
      (T3.C1 = T2.C1 SELECTIVITY 0.120000)
    
    The SELECTIVITY .12 is copied to T3.C1 = T2.C1 which is
    incorrect
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to 11.1.4.7 and issue DB2_SELECTIVITY='CGSSEL1'      *
    ****************************************************************
    

Problem conclusion

  • Fix is included in 11.1.4.7. To enable the fix, issue
    DB2_SELECTIVITY='CGSSEL1'
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT27875

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-01-24

  • Closed date

    2022-04-17

  • Last modified date

    2022-04-17

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

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

    IT27876 IT39702

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
04 May 2022