IY95097: FIX TO GENERATE IMPLIED PREDICATE WITH JOIN CLAUSE

DB2 Version 9.1 Fix Pack 4a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 4 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

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

• FIX TO GENERATE IMPLIED PREDICATE WITH JOIN CLAUSE
  

Problem conclusion

• APAR is fixed in v9.1
  

Temporary fix

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

Comments

APAR Information

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

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

  IZ15361 IZ15366

Fix information

• Fixed component name

  DB2 DPF

• Fixed component ID

  5724N7400

Applicable component levels

• R910 PSY

     UP