PM10274: ABEND04E RC00E20001 OCCURRED AT DSNXGXTB.DSNSVBK +1286 WHEN THE SELECT STATEMENT HAS A HUGE IN-LIST WITH LITERALS DB2OVRLAY/J

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Abend04E RC00E20001 occurred at DSNXGXTB.DSNSVBK +1286
  when the SELECT statement being processed contain a huge
  IN-list with literals.
  DB2STGLK/K
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 for z/OS V8 and DB2 9 for z/OS users *
  *                 of a SELECT statement that has more than     *
  *                 32000 IN list elements (or entries) that are *
  *                 all literals.                                *
  ****************************************************************
  * PROBLEM DESCRIPTION: An ABEND04E RC00E20001 can occur at     *
  *                      DSNXGXTB DSNSVBK +1286 for a SELECT     *
  *                      statement that has more than 32000      *
  *                      entries combined in all IN list         *
  *                      elements.  All IN list elements are     *
  *                      literals.                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An ABEND04E RC00E20001 can occur at DSNXGXTB DSNSVBK +1286 when
  a SELECT statement has more than 32000 entries combined in all
  IN list elements.  All IN list elements are literals.
  
  The abend is caused by the overflow of a DB2 internal counter.
  
  
  The following example helps to illustrate a failing case.
  
  1. Create table, index, and view.
  
    CREATE TABLE T3(C1 VARCHAR(8));
  
    CREATE INDEX IX3 ON T3(C1);
  
    CREATE VIEW V3 AS (
    SELECT C1 FROM T3 UNION ALL
    SELECT C1 FROM T3 UNION ALL
    SELECT C1 FROM T3 UNION ALL
    SELECT C1 FROM T3 UNION ALL
    SELECT C1 FROM T3 UNION ALL
    SELECT C1 FROM T3 UNION ALL
    SELECT C1 FROM T3
    );
  
  
  2. SELECT from V3 to select from T3.
  
    SELECT * FROM V3 WHERE C1 IN (
    '19199863','86771728','42116823','80171104',
    .....
    '4805914612','8003439612', '51266649','47573348');
  
  If the above SELECT statement has 5000 IN list elements,
  with the V3's UNION ALL above, there will be more than
  32000 entries combined in all IN list elements. All the
  IN list elements are literals. This query could result
  in the ABEND.
  

Problem conclusion

• The bindtime code in DB2 is modified to correct the failing
  condition.
  
  Additional keyword: SQLINLIST SQLSTORAGE
  

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:

  UK58767 UK58768

Modules/Macros

• DSNXGINL DSNXGRTS DSNXINL
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK58767

     UP10/07/28 P F007

• R910 PSY UK58768

     UP10/07/28 P F007

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.