IT25969: QUERY FAILS WITH SQL0901N REASON "PARENT ENTITY NOT PROVIDED" ORREASON "FOUND ZERO CLOB.RESET"

APAR status

• Closed as program error.

Error description

• SQL901N or trap might occur when running queries containing
  outer joins and inner joins
  
  Users might observe SQL901N or trap when running a query with
  the following characteristics:
  1. there are 2 or more cascaded outer joins
  2. the lower outer joins are at the row preserving side of the
  upper outer joins
  3. the null producing side of a lower outer join and the null
  producing side of a higher outer join reference the same base
  table
  4. there are base tables inner joined with the cascaded outer
  joins
  
  Reason codes shown can be
  a)
  SQL0901N The SQL statement or command failed because of a
  database system
  error. (Reason "Parent entity not provided".) SQLSTATE=58004
  
  Similar compiler Stack:
  Compiler error stack for rc = -2144272209:
  sqlnn_cmpl[370]
  sqlnr_exe[1140]
  sqlnq_pdb_analysis[40]
  sqlnq_qtb::identify_depend[40]
  sqlnq_qtb::identify_depend[40]
  sqlnq_qtb::identify_depend[40]
  sqlnq_qtb::identify_depend[40]
  sqlnq_qtb::identify_depend[40]
  sqlnq_qtb::identify_depend[30]
  sqlnq_record_dependency[20]
  sqlnq_qunordaftchild::reli[10]
  
  b)
  If using optimization level 0 or 1
  
  SQL0901N The SQL statement or command failed because of a
  database system
  error. (Reason "Found zero CLOB.reset".) SQLSTATE=58004
  

Local fix

• Disable Semi-join feature:
  
  db2set -im DB2COMPOPT=NOOJSJ (to disable this feature for the
  whole instance)
  
  or
  
  attach the guideline shown below at the end of the problematic
  query to only disable this feature for the query:
  
  /*<OPTGUIDELINES>
  <REGISTRY>
  <OPTION NAME='DB2COMPOPT' VALUE='NOOJSJ'/>
  </REGISTRY>
  </OPTGUIDELINES>*/
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 Version 11.1 Mod 4 Fix Pack 6                 *
  ****************************************************************
  

Problem conclusion

• Problem was first fixed in DB2 Version 11.1 Mod 4 Fix Pack 6
  

Temporary fix

• See LOCAL 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