IBM Support

IC98361: QUERY USING ZIGZAG IN THE ACCESS PLAN MIGHT PRODUCE WRONG RESULTS WHEN IN2JOIN IS IN THE ACCESS PLAN AS WELL.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Queries logically similar can produce wrong result. The given
    scenario is one example
    Expected result from all the queries:-
    
    Acct  	 Acct 	 3
    2012	           2012	 71
    2013   	   2013	 20
    
    
    
    Example of incorrect results are as follows:-
    
    select test.acct, head.acct, count(*)
    from db2inst1.employee test
    join db2inst1.salary test1 on test1.rid = test.id
    where test.acct =test1.acct and test.acct in ( 2013,
    2012
    ) and test1.code = '102228'
    group by test.acct,test1.acct
    Acct 	         Acct       	 3
    2012	         2012	 71
    
    
    
    
    select test.acct,test1.acct, count(*)
    from db2inst1.employee test
    join db2inst1.salary test1 on test1.rid = test.id
    where test.acct =test1.acct and test.acct in ( 2012,
    2013
    ) and test1.code = '102228'
    group by test.acct,test1.acct
    ;
    
    Acct    	 Acct 	 3
    2013	          2013	 20
    
    Access plans will show zig zag join used with IN2JOIN in bad
    case.
    

Local fix

  • Disable Zig Zag join using :-
    db2set DB2_REDUCED_OPTIMIZATION="ZZJN OFF" -IM
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Queries logically similar can produce wrong result. The      *
    * given                                                        *
    * scenario is one example                                      *
    * Expected result from all the queries:-                       *
    *                                                              *
    * Acct       Acct      3                                       *
    * 2012               2012     71                               *
    * 2013          2013     20                                    *
    *                                                              *
    *                                                              *
    *                                                              *
    * Example of incorrect results are as follows:-                *
    *                                                              *
    * select test.acct, head.acct, count(*)                        *
    * from db2inst1.employee test                                  *
    * join db2inst1.salary test1 on test1.rid = test.id            *
    * where test.acct =test1.acct and test.acct in ( 2013,         *
    * 2012                                                         *
    * ) and test1.code = '102228'                                  *
    * group by test.acct,test1.acct                                *
    * Acct              Acct            3                          *
    * 2012             2012     71                                 *
    *                                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    * select test.acct,test1.acct, count(*)                        *
    * from db2inst1.employee test                                  *
    * join db2inst1.salary test1 on test1.rid = test.id            *
    * where test.acct =test1.acct and test.acct in ( 2012,         *
    * 2013                                                         *
    * ) and test1.code = '102228'                                  *
    * group by test.acct,test1.acct                                *
    * ;                                                            *
    *                                                              *
    * Acct         Acct      3                                     *
    * 2013              2013     20                                *
    *                                                              *
    * Access plans will show zig zag join used with IN2JOIN in bad *
    * case.                                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 10.1 Fix Pack 4                       *
    ****************************************************************
    

Problem conclusion

  • First fixed in Version 10.1 Fix Pack 4
    

Temporary fix

  • Disable Zig Zag join using :-
    db2set DB2_REDUCED_OPTIMIZATION="ZZJN OFF" -IM
    

Comments

APAR Information

  • APAR number

    IC98361

  • 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

    2013-12-17

  • Closed date

    2014-06-16

  • Last modified date

    2016-09-02

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IT16779

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:
02 September 2016