JR28448: DB2 CAN ABEND WHEN DB2_COMPOPT IS SET AND THE QUERY CONTAINS A GROUP BY OPERATION AND THERE ARE VERY FEW FREQVALUES STATISTICS

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

• The error can occur under the following conditions:
  - The DB2_COMPOPT registry variable is set
  - The query contains a GROUP BY as input to an OUTER JOIN, and
  the ON clause has equality predicates.
  - The GROUP BY columns cover the columns of an index and cover
  the columns referenced in the equijoin predicates of the OUTER
  JOIN.
  - The distribution statistics gathered for the index columns
  have very few frequent values as specified by the NUM_FREQVALUES
  attribute in the RUNSTATS command or database configuration.
    Few freqvalues means fewer than the number of columns in the
  index.
  
  
  If all the aforementioned conditions are satisfied then DB2 may
  abend with a SQL1224 error.
  
  A trap file may show the following stack :
  
  sqlnr_estimate_qncs_distinctvalue_basic
  sqlnr_estimate_qncs_distinctvalue
  sqlnr_estimate_gb_card
  sqlnr_estimate_box_card
  sqlnr_estimate_card
  sqlnr_OJ_semi_join
  sqlnr_qrwprep_phase2
  sqlnr_exe
  

Local fix

• - Unset the DB2_COMPOPT registry variable.
  - Rerun the RUNSTATs command and gather higher number of
  frequent value statistics for the columns referenced in the
  GROUP BY clause.
  

Problem summary

• Users affected : Users having DB2_COMPOPT set
  Problem description : possible abend of instance
  Problem Summary : this apar fix avoids the abnormal termination
  

Problem conclusion

• First fixed in DB2 UDB Version 9, Fixpak 5
  

Temporary fix

Comments

APAR Information

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

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

  JR28452

Fix information

• Fixed component name

  DB2 UDB EXE WIN

• Fixed component ID

  5724E4901

Applicable component levels

• R910 PSY

     UP