IT38495: DB2 MIGHT TERMINATE ABNORMALLY FOR QUERIES WITH MULTIPLE OUTER JOINS

Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• If you have a a query that meets the following characteristics:
  1. Query with multiple Outer join and equi-join predicates
  2. Null producing side is a complex view
  3. Where clause with equality predicates referencing the join
  columns and constants
  4. Cost based
  
  it may trap in sqlnr_OJ_semi_join() with the following stack:
  
  sqlnr_OJ_semi_join
  sqlnr_qrwprep_phase2
  sqlnr_prep2_action
  sqlnr_comp
  sqlnr_seq
  sqlnr_rcci
  sqlnr_exe
  
  This problem starts to happen on v11.1.4.6, doesn't happen on
  previous versions.
  

Local fix

• Set the following registry
  - with the optimization guideline to the affected queries:
  /* <OPTGUIDELINES>
          <REGISTRY>
             <OPTION NAME='DB2COMPOPT' VALUE='NOOJSJ'/>
          </REGISTRY>
  </OPTGUIDELINES> */
  or
  - at the instance level:
  db2set DB2COMPOPT=NOOJSJ -im
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * all                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to 11.1.4.7                                          *
  ****************************************************************
  

Problem conclusion

• Upgrade to 11.1.4.7
  

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:

  IT38518

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSN

     UP