IBM Support

IC63229: DB2 QUERY REWRITE MAY NOT COMPLETELY OPTIMIZE A QUERY CONTAINING NODENUMBER LOCAL PREDICATE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • DB2 Query ReWrite (QRW) may not completely optimize a query
    containing a nodenumber local predicate on a base table. This
    optimization is only valid if the following conditions are
    satisfied:
    1. There is a pair of Base Tables.
    2. The two Base Tables have the same partitioning map id
    3. The tables have equality join predicate on all the columns of
    the
    partitioning keys of the tables.
    4. Each column of the partitioning key of the first table must
    join to the column of the same order of the partitioning keys of
    the other table
    5. There is a local predicate of the form,
    NODENUMBER(<table>.<col>)=<x> where <x> can be a constant, host
    
    variable, parameter marker or a special register.
    
    Sample Query:
    
    create table T1(c11 int,c12 int,c13 int,c14 int) distribute by
    (c12,c13);
    create table T2(c21 int,c22 int,c23 int,c24 int) distribute by
    (c22,c23);
    
    before fix:
    select * from t1,t2 where nodenumber(t1.c11) = 0 and  t1.c12 =
    t2.c22 and  t1.c13 = t2.c23;
    
    after fix:
    select * from t1,t2 where nodenumber(t1.c11) = 0 and  t1.c12 =
    t2.c22 and  t1.c13 = t2.c23 and nodenumber(t2.<any col>) = 0;
    

Local fix

  • Manually rewrite query with additional local predicate
    nodenumber(<table>.<col>)=<x> on other table.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Please see Problem Description above.                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Version 9.7 Fix Pack 3                            *
    ****************************************************************
    

Problem conclusion

  • First fixed in Version 9.7 Fix Pack 3
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC63229

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-09-16

  • Closed date

    2010-09-23

  • Last modified date

    2010-09-23

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

    IC63226

  • 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

  • R970 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
23 September 2010