A fix is available
APAR status
Closed as program error.
Error description
SQL statement can fail with SQL0901N error code or produce wrong result when ZZJOIN is chosen in the access plan. To test if you might hit the issue, compile the query in explain mode. If SQL0803N error code is generated during explain, then when you disable ZZJOIN, the explain runs successfully, you likely hit the issue. For example: db2 set current explain mode explain db2 -tf myquery.sql SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "TBSPACEID=2.TABLEID=8" from having duplicate values for the index key. SQLSTATE=23505 db2set -im DB2_REDUCED_OPTIMIZATION="ZZJN OFF" db2 -tf myquery.sql SQL0217W The statement was not executed as only Explain information requests are being processed. SQLSTATE=01604 This problem can cause wrong result, or a SQL0901N error with the following db2diag.log and the stack trace. db2diag.log: 2015-10-09-14.01.26.181820+540 I171391178A890       LEVEL: Severe PID     : 17891478             TID : 97602          PROC : db2sysc 0 INSTANCE: db2inst1             NODE : 000           DB   : DBNAME APPHDL  : 0-34207              APPID: 10.3.70.4.62319.151009050010 AUTHID  : DB2INST1             HOSTNAME: hostname EDUID   : 97602                EDUNAME: db2agent (DBNAME) 0 FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:250 DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes  sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 24  sqlerrmc: qncData.doptqncp is null  sqlerrp : SQLNG099  sqlerrd : (1) 0x00000000      (2) 0x00000000      (3) 0x00000000            (4) 0x000684EA      (5) 0xFFFFECC7      (6) 0x00000000  sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)            (7)      (8)      (9)      (10)        (11)  sqlstate: Stacks: trace: <StackTrace> -------Frame------ ------Function + Offset------ 0x0900000000735F94 pthread_kill + 0xD4 0x0900000009AFF090 sqloDumpEDU + 0xC4 0x090000000D58F4D4 sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2 + 0x110 0x09000000099D5068 sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x724 0x09000000099D46C0 sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x28 0x09000000092653C0 sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc + 0x234 0x09000000088EA1E8 sqlnn_erds__FiN41e + 0x134 0x090000000CE66EF0 sqlng_build_SQLD_VALS_obj__FP9sqlng_blkP14sqlng_scan_blkP9sqlno_ qtblR15sqlngPushdownCBPUiPP8SQLD_VAL + 0x2F88 0x090000000CE36D48 sqlng_build_TA_op__FP9sqlng_blkP14sqlng_scan_blkP9sqlno_qtb + 0x10F4 0x090000000A249338 sqlng_process_scan_op__FP9sqlng_blkP19sqlno_plan_operator + 0x315C 0x090000000A2474E0 sqlng_process_scan_op__FP9sqlng_blkP19sqlno_plan_operator + 0x1304 0x090000000A23A67C sqlng_process_scan_op__FP9sqlng_blkP19sqlno_plan_operator + 0x170 0x0900000008439264 sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x350 0x090000000A23C520 sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x160 0x09000000078A12B0 sqlng_process_mergeJoin_op__FP9sqlng_blkP19sqlno_plan_operator + 0x718 0x090000000847084C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x48 0x0900000008438FAC sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x98 0x090000000CE46C48 sqlng_process_temp_op__FP9sqlng_blkP19sqlno_plan_operator + 0x60C 0x090000000847084C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x48 0x0900000008438FAC sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x98 0x090000000A23C520 sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x160 0x090000000A248E8C sqlng_process_scan_op__FP9sqlng_blkP19sqlno_plan_operator + 0x2CB0 0x090000000A2474E0 sqlng_process_scan_op__FP9sqlng_blkP19sqlno_plan_operator + 0x1304 0x090000000A23B364 sqlng_process_scan_op__FP9sqlng_blkP19sqlno_plan_operator + 0xE58 0x0900000008439264 sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x350 0x090000000A23C520 sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x160 0x090000000C865E10 sqlng_process_zigzag_op__FP9sqlng_blkP19sqlno_plan_operator + 0x1090 Stack traceback unavailable. </StackTrace>
Local fix
Disable ZZJOIN using the following method. db2set -im DB2_REDUCED_OPTIMIZATION="ZZJN OFF"
Problem summary
**************************************************************** * USERS AFFECTED: * * Ann users. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 10.1 Fix Pack 6. * ****************************************************************
Problem conclusion
First fixed in DB2 Version 10.1 Fix Pack 6.
Temporary fix
Comments
APAR Information
APAR number
IT13321
Reported component name
DB2 FOR LUW
Reported component ID
DB2FORLUW
Reported release
A10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2016-01-22
Closed date
2016-12-02
Last modified date
2017-02-28
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
DB2 FOR LUW
Fixed component ID
DB2FORLUW
Applicable component levels
RA10 PSN
UP
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
28 February 2017