|
Problem IC95259
|
Status: Closed |
SOMETIMES A QUERY INVOLVING A ZIGZAG JOIN MAY FAIL WITH WITH A SQL0901N ERROR |
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - 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 below V10.5 FixPack 3 *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Update to 10.5.0.3 *
**************************************************************** |
| 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.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
|
| Solution |
Problem Fixed In 10.5.0.3 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.08.2013
02.04.2014
02.04.2014 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.3
|
 |
| 10.5.0.3
|
|
