DB2 FOR LUW / DB2FORLUW / B10 - DB2

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

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

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:

/*



*/

See LOCAL FIX

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

14.08.2018
04.05.2020
04.05.2020