IBM Support

IY82829: LIKE PREDICATE HANDLING MAY CAUSE POOR QUERY PERFORMANCE.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • You might encounter situations where handling of certain LIKE
    predicates could cause poor query performance.
    
    Following example describes the situation:
    
    - TAB1 having having columns
       C1 smallint
       C2 int
       C3 char(3)
    
    - column c3 having check constraint
       c3 between 'YES' and 'NON'
    
    - doing select on this table in form of
       select * from TAB1 where ... and C3 LIKE 'AA1234%'
    
    Instead of determining at compilation time that the LIKE
    predicate returns no rows, we end up with a time consuming table
    scan at run time.
    
    In contrast to the above explained example we have better
    behaviour if using another predicate - e.g. C3 LIKE 'AA1'
    Now query rewrite recognizes that the equality selects no rows,
    because 'AA1' falls outside the range allowed by the check
    constraint defined on TAB1.
    

Local fix

  • Create an index on the concering column (column C3 for the
    example above).
    
    This will result in an index scan which returns 0 rows and
    therefore returns very quickly.
    

Problem summary

  • USERS AFFECTED: all
    
    PROBLEM DESCRIPTION:
    Current LIKE predicate handling in DB2 may cause qoor query
    performance as a suboptimal access plan is used.
    
    PROBLEM SUMMARY:
    You might encounter situations where handling of certain LIKE
    predicates could cause poor query performance.
    
    Following example describes the situation:
    
    - TAB1 having having columns
       C1 smallint
       C2 int
       C3 char(3)
    
    - column c3 having check constraint
       c3 between 'YES' and 'NON'
    
    - doing select on this table in form of
       select * from TAB1 where ... and C3 LIKE 'AA1234%'
    
    Instead of determining at compilation time that the LIKE
    predicate returns no rows, we end up with a time consuming table
    scan at run time.
    
    In contrast to the above explained example we have better
    behaviour if using another predicate - e.g. C3 LIKE 'AA1'
    Now query rewrite recognizes that the equality selects no rows,
    because 'AA1' falls outside the range allowed by the check
    constraint defined on TAB1.
    
    LOCAL FIX:
    Create an index on the concering column (column C3 for the
    example above).
    
    This will result in an index scan which returns 0 rows and
    therefore returns very quickly.
    

Problem conclusion

  • First fixed in DB2 UDB Version 8, FixPak 16 (s080111)
    

Temporary fix

  • see LOCAL FIX
    

Comments

APAR Information

  • APAR number

    IY82829

  • Reported component name

    DB2 CEE AIX

  • Reported component ID

    5765F3000

  • Reported release

    820

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2006-03-31

  • Closed date

    2008-02-06

  • Last modified date

    2008-02-06

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

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

    IZ02639

Fix information

  • Fixed component name

    DB2 CEE AIX

  • Fixed component ID

    5765F3000

Applicable component levels

  • R810 PSN

       UP

  • R820 PSN

       UP

  • R910 PSN

       UP

  • R950 PSN

       UP

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"820"}]

Document Information

Modified date:
02 October 2021