IBM Support

IC61901: ENABLE GENERATION OF IMPLIED IN-LIST PREDICATES FOR OR PREDICATE WHEN QUERY ON A SINGLE TABLE.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • We are enabling generatiuon of implied IN-list predicates for
    the OR predicate set when query on a single table.  The OR
    predicate is restricted to have only equal (=) and inlist (IN)
    predicate subterm therefore only implied IN-list predicate would
    be generated.  With the new IN-list generated, we have new
    opportunity to carry out IN-TO-JOIN transformation to enhance
    query performance.
    
    e.g.
    select a1,a2,a3
    from a
    where (a1 = 1 and a3 in ('aa','bb','cc')) or (a1 = 1 and a3 in
    ('dd','ee')) or (a1 = 2 and a3 in ('ff'));
    
    Optimized query:
    SELECT ALL Q3.A1, Q3.A2, Q3.A3
    FROM A Q3
    WHERE SYSIBM.SINLIST(Q3.A3, 'aa', 'bb', 'cc', 'dd', 'ee', 'ff')
    AND
    SYSIBM.SINLIST(Q3.A1, 1, 2) AND
    (((Q3.A1 = 1) AND SYSIBM.SINLIST(Q3.A3, 'aa', 'bb', 'cc')) OR
    ((Q3.A1 = 1) AND SYSIBM.SINLIST(Q3.A3, 'dd', 'ee'))) OR
    ((Q3.A1 = 2) AND (Q3.A3 = 'ff'));
    
    Note that there are existing condition to carry IN-TO-JOIN
    transfromation, the new IN predicate is expected to comply to
    those existing condition for have IN-TO-JOIN resulted.
    

Local fix

  • Workaround:
    You may manually rewritten the query with the implied IN
    predicate as shown in the example.
    
    defect: wsdbu00554342
    

Problem summary

  • ENABLE GENERATION OF IMPLIED IN-LIST PREDICATES FOR OR PREDICATE
    WHEN QUERY ON A SINGLE TABLE.
    

Problem conclusion

  • Problem was first fixed in Version 9.5 Fix Pack 6
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC61901

  • 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

    2009-07-08

  • Closed date

    2010-05-17

  • Last modified date

    2010-05-17

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

    IC61900

  • 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

  • R950 PSY

       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:
17 May 2010