IBM Support

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

A fix is available

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

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
            

            
                      

        


        

        


      

    

  



    

  

  
    
            

          
Page Feedback