IT36320: QUERY EXECUTION MAY FAIL FOR OUTER JOIN QUERIES WITH PREDICATES WITH CAST FUNCTIONS

Db2 Version 11.5 Mod 6 Fix Pack 0 for Linux, UNIX, and Windows
Db2 Version 11.5 Mod 7 Fix Pack 0 for Linux, UNIX, and Windows
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 DPF system and a query that meets the following
  conditions :
  - query references multiple Outer joins
  - predicates in the ON clause reference Cast functions
  it may trap in sqlnr_OJ_semi_join()
  
  Example query:
  SELECT T1.C1
  FROM T1 LEFT OUTER JOIN T2 ON T2.C1 = 'ITRA'
       LEFT OUTER JOIN T3 ON T3.C1 = CAST(T1.C1 AS VARCHAR(200)) ;
  
  
  Example stack trace for the failing query:
  sqlnr_OJ_semi_join
  sqlnr_qrwprep_phase2
  sqlnr_prep2_action
  sqlnr_exe
  sqlnn_cmpl
  

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.5.6 or later                                   *
  ****************************************************************
  

Problem conclusion

• Upgrade to 11.5.6 or later
  

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:

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB50 PSN

     UP