IBM Support

IT13321: SQL STATEMENT CAN FAIL WITH SQL0901N OR PRODUCE WRONG RESULT WHEN ZZJOIN IS CHOSEN IN THE ACCESS PLAN

Subscribe

You can track all active APARs for this component.

 

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:

    IT12532

  • 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