IBM Support

JR33376: CASE EXPRESSION IN PREDICATES OR LARGE OR PREDICATES MAY CAUSE SQL0101 ERROR WHEN STMTHEAP IS SMALL /INSTANCE MEMORY IS LIMITED

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • CASE expressions containing a large number of WHEN clauses which
    are used in a <, <=, <>, =, >= or > predicate or OR predicates
    containing a very large number of AND subterms can consume a
    large amounts of memory during statement compilation. This can
    cause SQL0101N errors if  STMTHEAP, DATABASE_MEMORY or
    INSTANCE_MEMORY is limited.
    
    Examining the text  for the failing query, if you find a CASE
    expression involving more then 100 WHEN clauses involved in a
    relational predicate or OR predicates with more then 10000
    subterms then this APAR may be applicable.
    

Local fix

  • 1) Increase the STMTHEAP database configuration parameter, or if
    STMTHEAP is already large or set to AUTOMATIC then check the
    DATABASE_MEMORY and/or INSTANCE_MEMORY to ensure those limits
    are not impacting the amount of STMTHEAP which can be use.
    
    2) Modify the query to simplify the CASE or OR predicate.
    
    3) If the query contains a large CASE expression in a predicate
    causing the memory problems then lower the query optimization
    level for this query to 0 or 1.
    

Problem summary

  • Users Affected:
    All users prior to V95 FP5
    
    Problem Description:
    CASE EXPRESSION IN PREDICATES OR LARGE OR PREDICATES MAY CAUSE
    SQL0101 ERROR WHEN STMTHEAP IS SMALL /INSTANCE MEMORY IS LIMITED
    
    LOCAL FIX:
    1) Increase the STMTHEAP database configuration parameter, or if
    STMTHEAP is already large or set to AUTOMATIC then check the
    DATABASE_MEMORY and/or INSTANCE_MEMORY to ensure those limits
    are not impacting the amount of STMTHEAP which can be use.
    
    2) Modify the query to simplify the CASE or OR predicate.
    
    3) If the query contains a large CASE expression in a predicate
    causing the memory problems then lower the query optimization
    level for this query to 0 or 1.
    

Problem conclusion

  • Problem Conclusion:
    First Fixed in DB2 V95 FP5
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR33376

  • Reported component name

    DB2 UDB EXE WIN

  • Reported component ID

    5724E4901

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-06-17

  • Closed date

    2010-03-09

  • Last modified date

    2010-03-09

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

    JR33233

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

    IC64006

Fix information

  • Fixed component name

    DB2 UDB EXE WIN

  • Fixed component ID

    5724E4901

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

Document Information

Modified date:
09 March 2010