DB2 FOR LUW / DB2FORLUW / A50 - 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:                                              *
* CDE                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 Version 10.5 Fix Pack 10                      *
****************************************************************

The workaround is to disable SEMIJOIN. It can be accomplished by
optimization guidelines for the query:


/* 



     */ ;

not known / see Local fix

forerunner  : 
follow-up : IT22344 

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

31.03.2017
16.07.2018
16.07.2018