DB2 FOR LUW / DB2FORLUW / A10 - DB2

A ZIG ZAG JOIN executed with multiple subagents can hang or 
fail, when some of the subagents exit early with no row found 
error during dimension processing. Subagents, which do not exit 
early, will hang in the following stacktrace: 
 
0x09000000000EDF78 thread_wait + 0x98 
0x09000000561181D0 sqloWaitEDUWaitPost + 0x254 
0x0900000059804E18 
IntrptWaitLock__19@52@276650@sqeAgentFP16SQLO_EDUWAITPOSTiPUiT2 
+ 0x538 
0x0900000056734D9C sqlrittb__FP8sqlrr_cb + 0x530 
0x09000000589928C0 sqlriZZOpenDims__FP8sqlrr_cbP10sqlri_zzob + 
0x7FC 
0x090000005898EA98 sqlriZZInit__FP8sqlrr_cbP10sqlri_zzob + 0x8C 
0x090000005898E198 sqlriZigZagJoin__FP8sqlrr_cb + 0xD8 
0x090000005609A268 sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm + 
0x30 
0x0900000054AB6B20 sqlrr_smp_router__FP8sqlrr_cb + 0x738 
0x090000005458D748 
sqlrr_subagent_router__FP8sqeAgentP12SQLE_DB2RA_T + 0x864 
0x090000005458C1C8 @82@sqleSubRequestRouter__FP8sqeAgentPUiPUl + 
0x3B4 
0x09000000545850B8 sqleProcessSubRequest__FP8sqeAgent + 0x48C 
0x0900000055D4BF8C RunEDU__8sqeAgentFv + 0x6A0 
0x0900000055D4797C EDUDriver__9sqzEDUObjFv + 0x13C 
0x0900000055D47804 sqlzRunEDU__FPcUi + 0x10 
0x0900000055D5864C sqloEDUEntry + 0x264 
 
The hang as described above is the most likely symptom of this 
problem, however given that the root cause of this problem is an 
smp agent prematurely exiting the runtime execution, there may 
be other possible effects as well, depending on the nature of 
the access plan, such as hangs in other codepaths, sql0901 
errors or other unexpected behaviour. 
 
The coordinator agent will wait on the table queue with this 
stacktrace, when subagents exit early: 
 
0x09000000000EDF78 thread_wait + 0x98 
0x09000000561181D0 sqloWaitEDUWaitPost + 0x254 
0x09000000565F37C4 WaitRecvReady__11sqkfChannelFiT1 + 0x230 
0x09000000573A0620 
ReceiveAnyBuffer__11sqkfChannelFPP10sqkfBufferiPi + 0x660 
0x090000005739F4F8 
sqlkqrcv__FP8SQLKQ_CBP8sqeAgentPiPP10sqkfBufferT3 + 0x4A4 
0x090000005739ECE4 sqlktrcv__FP9sqlri_taoPi + 0xB0 
0x090000005739D308 
sqlkt_unpack_tuple__FP9sqlri_taoPiPbP5sqlcaPUiPUlP8sqeAgent + 
0x648 
0x09000000573A2C20 sqlktftc__FP8sqeAgentP9sqlri_taoP5sqlcaPUiPUl 
+ 0x6DC 
0x0900000057396D30 sqlritqa__FP8sqlrr_cb + 0x238 
0x090000005609A268 sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm + 
0x30 
0x09000000560382AC 
sqlrr_process_fetch_request__FP14db2UCinterface + 0x18C 
0x0900000055FA3554 
sqlrr_open__FP14db2UCinterfaceP15db2UCCursorInfo + 0x99C 
0x0900000055FA1D98 
sqljs_ddm_opnqry__FP14db2UCinterfaceP13sqljDDMObject + 0x1D74 
0x0900000055FD4138 
sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC 
interface + 0x1C4 
...

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.1 Fix Pack 3.                      * 
****************************************************************

There are several ways to overcome the situation: 
* set QUERY DEGREE to 1 for the SQL statement in question 
* set INTRA_PARALLEL to NO in the DBM CFG 
* db2set DB2_REDUCED_OPTIMIZATION="ZZJN OFF"

DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

First fixed in DB2 Version 10.1 Fix Pack 3.

keiner bekannt / siehe Local-Fix

Vorgänger  : APAR is sysrouted TO one or more of the following: IC95440 
Nachfolger : 

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

12.03.2013
02.10.2013
02.10.2013