IBM Support

IZ09197: A VERY COMPLEX QUERY MAY RETURN A WARNING MESSAGE SQL0437W after upgrade.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Due to changes in the DB2 optimizer code that started in Fix
    Pack 10,
    a very complex query that used to work fine before the upgrade,
    it may return the following warning after the upgrade:
    
    SQL0437W Performance of this complex query may be sub-optimal.
    Reason code: "1". SQLSTATE=01602.
    
    Explanation:
    
    The statement may achieve sub-optimal performance since the
    complexity of the query requires resources that are not
    available
    or optimization boundary conditions were encountered.  The
    following is a list of reason codes:
    
                  1
    The join enumeration method was altered due to memory
    constraints. 437rc1 is about switching dynamic programming join
    enumeration to greedy join enumeration.
    

Local fix

  • db2set db2_reduced_optimization=yes
    Alleviate the switch by reducing memory consumption.
    
    
    or
    
    Reduce the current query optimization level
    (e.g. set current query optimization 2)
    
    Avoid the switch as opt level 2 uses greedy vs opt level 5 uses
    dynamic.
    

Problem summary

  • see problem description
    

Problem conclusion

  • First fixed in DB2 UDB Version 9.5, FixPak 1
    

Temporary fix

Comments

APAR Information

  • APAR number

    IZ09197

  • Reported component name

    DB2 UDB ESE SOL

  • Reported component ID

    5765F4102

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2007-11-21

  • Closed date

    2008-05-05

  • Last modified date

    2011-08-29

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

    IY93789

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

Fix information

  • Fixed component name

    DB2 UDB ESE SOL

  • Fixed component ID

    5765F4102

Applicable component levels

  • R910 PSY

       UP

  • R950 PSY

       UP

[{"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":"950","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
01 October 2021