IBM Support

IC76001: A GENERATED COLUMN WHICH IS NOT USED IN THE SQL CAN BE REFERRED IN THE ACCESS PLAN AND MAY CAUSE A SUBOPTIMAL PLAN CHOICE.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When a generated column can be used to derive new predicates in
    an outer join, even it's not referenced in the optimized
    statement, the optimizer may still want to retrieve it, which
    may cause suboptimal plans.
    
    For example:
    Consider two tables that are outer joined as follows:
    create table t1(c1 char(10) not null, c2 char(20) not null, c3
    char(10) not null, c2sub5 char(10) not null generated always as
    (substr(c2,1,5)));
    create unique index i1 on t1(c1, c2) include (c3);
    create table t2(c1 char(10), c2 char(20));
    
    select t1.c3 from t2 left join t1 on (t1.c1=t2.c1 and
    t1.c2=t2.c2);
    
    In this case, c2sub5 can be used to generate a derived join
    predicate based on t1.c2=t2.c2. Even though a perfect index (i1)
    exists, the optimizer may still generate a TBSCAN or
    IXSCAN/FETCH plan in order to retrieve c2sub5.
    This can be worked around by including the unnecessary column
    (c2sub5) in the index definition:
    create unique index i1 on t1(c1, c2) include (c3, c2sub5);
    
    Note this only happens in outer joins.
    

Local fix

  • Include the generated column in the index definition.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error description field for more information.            *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Version 9.5 FixPack 9.                            *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in DB2 UDB Version 9.5 FixPack 9.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC76001

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-04-26

  • Closed date

    2012-03-22

  • Last modified date

    2012-03-22

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

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

    IC76373

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R950 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.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
22 March 2012