PH38537: PROCESSING OF A QUERY WITH A LARGE IN-LIST MAY END WITH SQLCODE=-954, SQLSTATE=57011

APAR status

• Closed as documentation error.

Error description

• Processing of a query with a large IN-list may fail with
  messages
  DSNX881I ... A SQL exception has occurred. The SQL error code is
  '-954'. The SQL state is: 57011.
  and
  SQL0973N Not enough storage is available in the "application
  shared" heap or stack to process the statement. SQLSTATE=57011
  .
  The processing the the backend database DB2wh needs to be
  improved to prevent out-of-memory issues due to large IN list
  predicates push down to Union branches.
  The corresponding enhancement is covered by Db2wh APAR IT36743.
  A fix will be delivered as part of the Db2wh engine incorporated
  in accelerator maintenance level 7.5.6.
  
  Additional keyowrds:
  TS005215819 INLIST SQLCODE954 SQL0973NAPPLICATION HEAP MEMORY
  OOM IT36743 IT41152
  

Local fix

Problem summary

• Problem Summary:
  See Error Description
  
  Users Affected:
  Users of accelerated queries with a large IN-LIST
  
  Proboem Scenario:
  See Problem Description
  
  Problem Symptoms:
  See Error Description
  

Problem conclusion

• The out of memory-issue described has been fixed using
  - Db2 APAR IT36743 ("PREVENT PUSHING DOWN LARGE IN LIST
    PREDICATES TO UNION BRANCHES.")
  - Db2 APAR IT41152 (""SQL0973N with token 'Applications
    Shared Heap' when very complex sql is run with high degree of
  SMP parallelism.")
  The fix for IT36743 was shipped as part of the DB2wh engine
  incorporated in Accelerator maintenance level 7.5.6.
  The fix for IT41152 was shipped as part of the DB2wh engine
  incorporated in Accelerator maintenance level 7.5.11.
  
  Upgrade your Accelerator maintenance level to 7.5.11 or later.
  

Temporary fix

Comments

APAR Information

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

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

Fix information

Applicable component levels