DB2 FOR LUW / DB2FORLUW / B10 - DB2

During execution of SQL containing JOINs, DB2 may choose 
SEMIJOIN strategy. The problem happens during join pushdown to 
CDE columnar engine. DB2 fails during construction of right 
filtering in case when outer/left side of the join at the top is 
a nested query. Once this happens, SQL returns SQL0901N error: 
 
SQL0901N  The SQL statement or command failed because of a 
database system 
error. (Reason "unexpected error at Parser.cpp:3723".) 
SQLSTATE=58004 
 
In the db2diag.log error is logged in 
ibm_cde::query::Parser::prepareCompositeTree: 
 
2017-02-12-18.14.45.498227-300 I937426A3404         LEVEL: Error 
PID     : 46007304             TID : 189992         PROC : 
db2sysc 0 
INSTANCE: DB2INST1             NODE : 000           DB   : 
SAMPLE 
APPHDL  : 0-45884              APPID: 
10.5.108.200.41560.170212231204 
AUTHID  : DB2INST1             HOSTNAME: node1 
EDUID   : 189992               EDUNAME: db2agent (SAMPLE) 0 
FUNCTION: DB2 UDB, CDE Trace, cdeDiagnosticsFacility, probe:3723 
DATA #1 : String, 235 bytes 
AssertionFailedException: An assertion 
'mKeyJoinMap.find(AuxKeyIdPair(AuxKeyIdPair(queryId,refKey))) != 
mKeyJoinMap.end()' failed. Additional info: query_qref to 
unknown query qid=7005 kentry=1 in query 8014 
Thrown at Parser.cpp : 3723 
DATA #2 : Function name in Library, 64 bytes 
ibm_cde::query::Parser::prepareCompositeTree(SyntaxTree *, bool) 
DATA #3 : File name, 10 bytes 
Parser.cpp 
CALLSTCK: (Static functions may not be resolved correctly, as 
they are resolved to the nearest symbol) 
 
The corresponding stack : 
 
0        pthread_kill 
1        sqloDumpEDU 
2        sqldDumpContext 
3        sqlrr_dump_ffdc 
4        sqlzeDumpFFDC 
5        sqlzeSqlCode 
6        sqlrrSqlCode 
7        ibm_cde::services::AssertionFailedException::done 
8 
ibm_cde::services::AssertionFailedException::constructAndThrow 
9        ibm_cde::query::Parser::prepareCompositeTree 
10       ibm_cde::query::Parser::parserWrapper 
11       ibm_cde::query::Parser::prepareNestedQuery 
12       ibm_cde::query::Parser::parserWrapper 
13       cdeInterface::initializeData 
14       cdeInterface::openTableScan 
15       sqlriCdeOpen 
16       sqlriCdeQuery 
17       sqlriSectInvoke 
18       sqlrr_process_fetch_request 
19       sqlrr_open 
20       sqljsParseRdbAccessed 
21       sqljsSqlam 
22       sqljsSqlam 
23       sqljsSqlam 
24       sqljsDriveRequests 
25       sqljsDrdaAsInnerDriver 
26       sqeAgent::RunEDU 
27       sqeAgent::RunEDU 
28       sqzEDUObj::EDUDriver 
29       sqloEDUEntry

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Db2 11.1 Mod 3 Fix Pack 3 or higher               * 
****************************************************************

The workaround is to disable SEMIJOIN. It can be accomplished by 
optimization guidelines for the query: 
 
 
/* <OPTGUIDELINES> 
 
<REGISTRY><OPTION NAME='DB2_CDE_SEMIJOIN' VALUE='0'/></REGISTRY> 
 
</OPTGUIDELINES>     */ ;

Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
 

First fixed in Db2 11.1 Mod 3 Fix Pack 3

not known / see Local fix

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

11.09.2017
19.03.2018
19.03.2018