IBM Support

IZ15366: FIX TO GENERATE IMPLIED PREDICATE WITH JOIN CLAUSE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • fix to generate implied predicate with JOIN clause
    
    Repro:
    create table a (a1 varchar(25),a2 varchar(25),a3 varchar(25),a4
    varchar(25),a5 varchar(25),a6 varchar(25), a7 varchar(25), a8
    varchar(25));create table b (b1 varchar(25),b2 varchar(25),b3
    varchar(25),b4 varchar(25),b5 varchar(25),b6 varchar(25), b7
    varchar(25), b8 varchar(25));create table c (c1 varchar(25),c2
    varchar(25),c3 varchar(25),c4 varchar(25),c5 varchar(25),c6
    varchar(25), c7 varchar(25), c8 varchar(25));create table d (d1
    varchar(25),d2 varchar(25),d3 varchar(25),d4 varchar(25),d5
    varchar(25),d6 varchar(25), d7 varchar(25), d8
    varchar(25));create table e (e1 varchar(25),e2 varchar(25),e3
    varchar(25),e4 varchar(25),e5 varchar(25),e6 varchar(25), e7
    varchar(25), e8 varchar(25));create table f (f1 varchar(25),f2
    varchar(25),f3 varchar(25),f4 varchar(25),f5 varchar(25),f6
    varchar(25), f7 varchar(25), f8 varchar(25));SELECT *FROM (((A
    left join B on (A.a1 = B.b1)) inner join C on (A.a2 = C.c2))
    left join D on (A.a1 = D.d1)) left join E on (A.a3 = E.e3)WHERE
    ((((((C.c4 != 'ATTRIBUTION') and (D.d5 = 'PRO')) and (e.e6 in
    ('NA SCP SEC','NA CCG MGMT','NA CCG EITF'))) and (A.a7 in
    ('PXUA','PXUE','PXUN','PXSA','PXSE','PXSN','PXRA','PXRN',
    'PXRE','MBSN','COUN','COUE','COUA','FAUN','FAUE','SBUL','QUAN'))
    )or (A.a1 = '08300537')) or (A.a1 = '08800P76')) AND (((A.a8 =
    '2006-04-28') and (((C.c4 = 'TRADING') and (C.c5 = 'DAILY')) and
    (C.c5 not in ('PRIMARY SYNDICATE', 'SECONDARY SYNDICATE')))) OR
    ((A.a8 = '2006-03-31') and ((C.c4 = 'DIVIDENDS') and (C.c5 =
    'DAILY'))) OR ((A.a8 = '2006-02-28') and ((C.c4 = 'COUPON') and
    (C.c5 = 'DAILY'))) OR ((A.a8 = '2006-01-31') and (((C.c4 in
    ('FINANCE','GVFINANCE','DEFINANCE','LTDFINANCE','REPOINT','REPOF
    EE')) and (C.c5 = 'DAILY')) and (C.c5 != 'SEC LENDING FEE'))) OR
    ((A.a8 = '2005-12-30') and (((C.c4 = 'TRADING') and (C.c5 =
    'MONTH TO DATE')) and (C.c5 not in ('PRIMARY
    SYNDICATE','SECONDARY SYNDICATE')))) OR ((A.a8 = '2005-11-30')
    and ((C.c4 = 'DIVIDENDS') and (C.c5 = 'MONTH TO DATE'))) OR
    ((A.a8 = '2005-10-31') and ((C.c4 = 'COUPON') and (C.c5 = 'MONTH
    TO DATE'))) OR ((A.a8 = '2005-09-30') and (((C.c4 in
    ('FINANCE','GVFINANCE','DEFINANCE','LTDFINANCE','REPOINT','REPOF
    EE')) and (C.c5 = 'MONTH TO DATE')) and (C.c5 != 'SEC LENDING
    FEE'))) OR ((A.a8 = '2005-08-31') and (((C.c4 = 'TRADING') and
    (C.c5 = 'YEAR TO DATE')) and (C.c5 not in ('PRIMARY
    SYNDICATE','SECONDARY SYNDICATE')))) OR ((A.a8 = '2005-07-29')
    and ((C.c4 = 'DIVIDENDS') and (C.c5 = 'YEAR TO DATE'))) OR
    ((A.a8 = '2005-06-30') and ((C.c4 = 'COUPON') and (C.c5 = 'YEAR
    TO DATE'))) OR ((A.a8 = '2005-05-31') and ((C.c4 in
    ('FINANCE','GVFINANCE','DEFINANCE',
    'LTDFINANCE','REPOINT','REPOFEE')) and (C.c5 = 'YEAR TO DATE'))
    and (C.c5 != 'SEC LENDING FEE')));
    
    a new mode to set the selectivity, DB2_PRED_FACTORIZE=EXTEND
    

Local fix

  • In short, we only set selectivity=1.0 on the new implied
    predicate as default, if we want to make it applied as existing
    implied predicate in optimizer, do db2set 2_PRE DB
    D_FACTORIZE=EXTEND.
    
    Note that is registery variable control is only for testing and
    special build, we will not be able to suport this in the
    product.
    
    Check CRM for more details.
    

Problem summary

  • Users Affected : All
    Problem Description :
       Fix to generate implied predicate with join clause.
    Problem Summary :
       Performance improvement fix to deliver much strict
       restriction and checking on the fix.
    

Problem conclusion

  • Problem was first fixed in V9.5 FP1 (s080328).
    

Temporary fix

Comments

APAR Information

  • APAR number

    IZ15366

  • Reported component name

    DB2 UDB ESE AIX

  • Reported component ID

    5765F4100

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-02-11

  • Closed date

    2008-05-04

  • Last modified date

    2008-05-04

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

    IY95097

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

Modules/Macros

  • ENG_SQNR
    

Fix information

  • Fixed component name

    DB2 UDB ESE AIX

  • Fixed component ID

    5765F4100

Applicable component levels

  • R950 PSN

       UP

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950"}]

Document Information

Modified date:
04 October 2021