|
Problem IC91405
|
Status: Closed |
SOMETIMES A QUERY INVOLVING A ZIGZAG JOIN MAY FAIL WITH WITH A SQL0901N ERROR |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
Sometimes a query involving a zigzag join may fail with with an
error as seen below :
SQL0901N The SQL statement or command failed because of a
database system
error. (Reason "unexpected MOV needed for ISCAN TEMP sskey".)
SQLSTATE=58004
<StackTrace>
-------Frame------ ------Function + Offset------
0x09000000007F3C30 pthread_kill + 0xB0
0x09000000053A8C44 sqloDumpEDU + 0xD0
0x0900000006956EF4 sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2 +
0x104
0x09000000062C0C1C sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x52C
0x09000000062BDAF0 sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x28
0x09000000062BDC94 sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc +
0xCC
0x09000000062C1490 sqlnn_erds__FiN41e + 0x134
0x09000000081D2100
sqlng_build_INDX_key__FP9sqlng_blkP14sqlng_scan_blkUsP13sqlno_ke
ycondT4P10sqlz_valueT6PUlT8PP16sqlriIXKeyUpdateT10_PUc + 0x175C
0x0900000007C0A464
sqlng_build_KEY_obj__FP9sqlng_blkP14sqlng_scan_blkP16sqlno_ixkey
condsT313SQLNN_BOOLEANP19sqlng_skeleton_nodeUcPPUcT8PUsT10_PP10S
QLD_IXKEYT12_PUiT14_ + 0xA78
0x0900000007C092D8
sqlng_build_INDX_obj__FP9sqlng_blkP14sqlng_scan_blkPP11SQLD_IXIN
FO + 0x524
0x090000000A1EF108
sqlng_build_TA_op__FP9sqlng_blkP14sqlng_scan_blkP9sqlno_qtb +
0x32F8
0x0900000007C1679C
sqlng_scan_derived_table__FP9sqlng_blkP19sqlno_plan_operatorUcP9
sqlnq_qun + 0x1924
0x09000000054778A0
sqlng_process_scan_op__FP9sqlng_blkP19sqlno_plan_operator +
0x404
0x09000000060BAE2C
sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x128
0x09000000060B9394
sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x628
0x090000000A2533A4
sqlngZigZagJoin__FP9sqlng_blkP19sqlno_plan_operator + 0xF9C
0x0900000007C35704
sqlng_process_zigzag_op__FP9sqlng_blkP19sqlno_plan_operator +
0x4D4
0x09000000060BAF4C
sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x248
0x090000000A204F88
sqlng_process_sort_op__FP9sqlng_blkP19sqlno_plan_operator +
0x714
0x09000000060BAF4C
sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x248
0x09000000060B9394
sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x628
0x090000000A20D3A4
sqlng_process_ioa_op__FP9sqlng_blkP19sqlno_plan_operator + 0x7BC
0x09000000060BAF4C
sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x248
0x090000000A2034B4
sqlng_process_f_ioa__FP9sqlng_blkP19sqlno_plan_operator + 0x598
0x090000000A1FC618
sqlng_process_fetch_op__FP9sqlng_blkP19sqlno_plan_operator +
0x334
0x09000000060BAF4C
sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x248
0x0900000007C35A84
sqlngZZBackJoin__FP9sqlng_blkP19sqlno_plan_operator + 0x2D8
.
.
.
0x0900000005C1C6E0 sqlzRunEDU__FPcUi + 0x10
0x0900000005C2F558 sqloEDUEntry + 0x264
</StackTrace>
This problem is related to a particular access plan that
features a zigzag join under a union. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users on Db2 V10.1 FixPack2 and below *
****************************************************************
* PROBLEM DESCRIPTION: *
* Sometimes a query involving a zigzag join may fail with with *
* an *
* error as seen below : *
* *
* SQL0901N The SQL statement or command failed because of a *
* database system *
* error. (Reason "unexpected MOV needed for ISCAN TEMP *
* sskey".) *
* SQLSTATE=58004 *
* *
* <StackTrace> *
* -------Frame------ ------Function + Offset------ *
* 0x09000000007F3C30 pthread_kill + 0xB0 *
* 0x09000000053A8C44 sqloDumpEDU + 0xD0 *
* 0x0900000006956EF4 sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2 *
* + *
* 0x104 *
* 0x09000000062C0C1C sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x52C *
* 0x09000000062BDAF0 sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + *
* 0x28 *
* 0x09000000062BDC94 *
* sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc + *
* 0xCC *
* 0x09000000062C1490 sqlnn_erds__FiN41e + 0x134 *
* 0x09000000081D2100 *
* sqlng_build_INDX_key__FP9sqlng_blkP14sqlng_scan_blkUsP13sqln *
* o_ke *
* ycondT4P10sqlz_valueT6PUlT8PP16sqlriIXKeyUpdateT10_PUc + *
* 0x175C *
* 0x0900000007C0A464 *
* sqlng_build_KEY_obj__FP9sqlng_blkP14sqlng_scan_blkP16sqlno_i *
* xkey *
* condsT313SQLNN_BOOLEANP19sqlng_skeleton_nodeUcPPUcT8PUsT10_P *
* P10S *
* QLD_IXKEYT12_PUiT14_ + 0xA78 *
* 0x0900000007C092D8 *
* sqlng_build_INDX_obj__FP9sqlng_blkP14sqlng_scan_blkPP11SQLD_ *
* IXIN *
* FO + 0x524 *
* 0x090000000A1EF108 *
* sqlng_build_TA_op__FP9sqlng_blkP14sqlng_scan_blkP9sqlno_qtb *
* + *
* 0x32F8 *
* 0x0900000007C1679C *
* sqlng_scan_derived_table__FP9sqlng_blkP19sqlno_plan_operator *
* UcP9 *
* sqlnq_qun + 0x1924 *
* 0x09000000054778A0 *
* sqlng_process_scan_op__FP9sqlng_blkP19sqlno_plan_operator + *
* 0x404 *
* 0x09000000060BAE2C *
* sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + *
* 0x128 *
* 0x09000000060B9394 *
* sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x628 *
* 0x090000000A2533A4 *
* sqlngZigZagJoin__FP9sqlng_blkP19sqlno_plan_operator + 0xF9C *
* 0x0900000007C35704 *
* sqlng_process_zigzag_op__FP9sqlng_blkP19sqlno_plan_operator *
* + *
* 0x4D4 *
* 0x09000000060BAF4C *
* sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + *
* 0x248 *
* 0x090000000A204F88 *
* sqlng_process_sort_op__FP9sqlng_blkP19sqlno_plan_operator + *
* 0x714 *
* 0x09000000060BAF4C *
* sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + *
* 0x248 *
* 0x09000000060B9394 *
* sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x628 *
* 0x090000000A20D3A4 *
* sqlng_process_ioa_op__FP9sqlng_blkP19sqlno_plan_operator + *
* 0x7BC *
* 0x09000000060BAF4C *
* sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + *
* 0x248 *
* 0x090000000A2034B4 *
* sqlng_process_f_ioa__FP9sqlng_blkP19sqlno_plan_operator + *
* 0x598 *
* 0x090000000A1FC618 *
* sqlng_process_fetch_op__FP9sqlng_blkP19sqlno_plan_operator + *
* 0x334 *
* 0x09000000060BAF4C *
* sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + *
* 0x248 *
* 0x0900000007C35A84 *
* sqlngZZBackJoin__FP9sqlng_blkP19sqlno_plan_operator + 0x2D8 *
* . *
* . *
* . *
* *
* 0x0900000005C1C6E0 sqlzRunEDU__FPcUi + 0x10 *
* 0x0900000005C2F558 sqloEDUEntry + 0x264 *
* </StackTrace> *
* *
* This problem is related to a particular access plan that *
* features a zigzag join under a union. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Db2 V10.1 FixPack 3 or above to fix the problem. *
**************************************************************** |
| Local Fix: |
Set the environment registry variable :
DB2_REDUCED_OPTIMIZATION="ZZJN OFF".
By doing so, we ensure that DB2 will avoid using Zigzag join in
the access plan. |
| available fix packs: |
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
|
| Solution |
Problem first fixed in Db2 V10.1 FicPack 3. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC95259 IC95289
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 08.04.2013
03.10.2013
03.10.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.3
|
 |
| 10.1.0.3
|
|
