DB2 FOR LUW / DB2FORLUW / B10 - DB2

create table t1(key1 int, key2 int, key3 int, vc1
varchar(32000), vc2 varchar(32000)) organize by column;
create table t2(key1 int, key2 int, key3 int, c1 char(100), c2
char(100)) organize by column;
create table t3(key1 int, key2 int, key3 int, c1 char(100), c2
char(100)) organize by column;

select
t2.c1
from t1, t2, t3
where
NOT
  case when (t1.key1 = 2) then 'Group' else t2.c1
  end
is null
and t3.key1 = t1.key1
and t3.key2 = t2.key2
order by t2.c1 asc
/*
    
        
        
            
            
        
    
*/;

linux:hotellnx114$ db2 -tvf test.sql
select t2.c1 from t1, t2, t3 where NOT case when (t1.key1 = 2)
then 'Group' else t2.c1 end is null and t3.key1 = t1.key1 and
t3.key2 = t2.key2 order by t2.c1 asc /* 
      */

C1

----------------------------------------------------------------
------------------------------------
SQL0901N  The SQL statement or command failed because of a
database system
error. (Reason "unexpected error at JoinRewriter.cpp:1274".)
SQLSTATE=58004

Stack:
======================

0x00002B0406CC7ED6
_Z25ossDumpStackTraceInternalmR11OSSTrapFileiP7siginfoPvmm +
0x0356
  0x00002B0406CC7B1B ossDumpStackTraceV98 + 0x002b
  0x00002B0406CC3D07 _ZN11OSSTrapFile6dumpExEmiP7siginfoPvm +
0x00d7
  0x00002B03FE9CFF97 sqlo_trce + 0x03c7
  0x00002B03FEB4572B sqloDumpDiagInfoHandler + 0x020b
  address: 0x00002B03F58126D0 ; dladdress: 0x00002B03F5803000 ;
offset in lib: 0x000000000000F6D0 ;
  0x00002B03F580FA01 pthread_kill + 0x0031
  0x00002B03FEB452E2 sqloDumpEDU + 0x0052
  0x00002B03F831E800 _Z15sqldDumpContextP9sqeBsuEduiiiiiPKcPvi +
0x1260
  0x00002B03FF1ADA65 _Z15sqlrr_dump_ffdcP8sqlrr_cbii + 0x0695
  0x00002B040066C937 _Z13sqlzeDumpFFDCP8sqeAgentjP5sqlcai +
0x00a7
  0x00002B040066C759
_Z12sqlzeSqlCodeP8sqeAgentjmjP5sqlcaitP13__va_list_tag + 0x0199
  0x00002B03FF2C8A2D sqlrrSqlCode + 0x00dd
  0x00002B03F863332E
_ZN7ibm_cde8services24AssertionFailedException4doneEv + 0x08ce
  0x00002B03F8636739
_ZN7ibm_cde8services24AssertionFailedException17constructAndThro
wENS0_11ComponentIDEPKcmS4_S4_RNS0_16CdeOStringStreamE + 0x0149
  0x00002B03FB1F691D
_ZN7ibm_cde5query12JoinRewriter28categorizePredicatesInternalERN
S0_17JoinRewriterEntryEPKNS0_10SyntaxTreeEbb + 0x088d
  0x00002B03FB1F620D
_ZN7ibm_cde5query12JoinRewriter28categorizePredicatesInternalERN
S0_17JoinRewriterEntryEPKNS0_10SyntaxTreeEbb + 0x017d
  0x00002B03FB20829E
_ZN7ibm_cde5query12JoinRewriter8addEntryEPKNS0_10SyntaxTreeE +
0x066e
  0x00002B03FB1FABEA
_ZN7ibm_cde5query12JoinRewriter7rewriteEPKNS0_10SyntaxTreeE +
0x024a
  0x00002B03FB178736
_ZN7ibm_cde5query6Parser13parserWrapperERKSbIcSt11char_traitsIcE
NS_8services9AllocatorIcEEERKNS_4data8DataBaseERKNS0_14QueryReso
urcesERNSA_22TableAccessorAllocatorERNS0_18QueryInfoContainerEP1
3SQLO_MEM_POOLPS1_ + 0x1696
  0x00002B03FB14F725
_ZN12cdeInterface14initializeDataEP8sqlrr_cbP17sqlriCdeStatement
+ 0x2335
  0x00002B03FB12348C
_ZN12cdeInterface13openTableScanEP8sqlrr_cbP17sqlriCdeStatement
+ 0x005c
  0x00002B03FF74D0D9
_Z15sqlriCdeSmpOpenP8sqlrr_cbP17sqlriCdeStatement + 0x0319
  0x00002B03FF744CED
_Z12sqlriCdeOpenP8sqlrr_cbP17sqlriCdeStatement + 0x043d
  0x00002B03FF744450 _Z13sqlriCdeQueryP8sqlrr_cb + 0x0280
  0x00002B03FF7E165D
_Z15sqlriSectInvokeP8sqlrr_cbP12sqlri_opparm + 0x04bd
  0x00002B03FF1EEE37
_Z21sqlrr_subagent_routerP8sqeAgentP12SQLE_DB2RA_T + 0x0e37
  address: 0x00002B03FCC286D7 ; dladdress: 0x00002B03F5DCA000 ;
offset in lib: 0x0000000006E5E6D7 ;
  0x00002B03FCC26FD0 _Z21sqleProcessSubRequestP8sqeAgent +
0x03e0
  0x00002B03FCC5C137 _ZN8sqeAgent6RunEDUEv + 0x05d7
  0x00002B040066CF3E _ZN9sqzEDUObj9EDUDriverEv + 0x01be
  0x00002B03FEB4319A sqloEDUEntry + 0x057a
  address: 0x00002B03F580AE25 ; dladdress: 0x00002B03F5803000 ;
offset in lib: 0x0000000000007E25 ;
  0x00002B0407DE1BAD clone + 0x006d

****************************************************************
* USERS AFFECTED:                                              *
* db2luw                                                       *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade                                                      *
* Or                                                           *
* Option 1:-                                                   *
* setting registry as instance level.                          *
* >db2set -im DB2_EXTENDED_OPTIMIZATION='COL_HSJN_WITH_RESID   *
* OFF'                                                         *
* Option 2 :-                                                  *
* Setting registry value at SQL level.                         *
* /*                                            *
*                                                              *
*                                                    *
*                                                              *
*                                      *
*                                                              *
*                                                   *
*                                                              *
*  */                                          *
*                                                              *
* Option 2 is recommended as this will impact only the current *
* SQL                                                          *
* to which opt guide is set.                                   *
****************************************************************

Option 1:-
setting registry as instance level.
>db2set -im DB2_EXTENDED_OPTIMIZATION='COL_HSJN_WITH_RESID OFF'
Option 2 :-
Setting registry value at SQL level.
/*

     

         

     

 */

Option 2 is recommended as this will impact only the current SQL
to which opt guide is set.

not known / see Local fix

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

07.05.2019
10.07.2019
04.11.2019