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

DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

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 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