PM52922: ABND04E 00E20016 IN DSNSVSTK OFFSET0656 STORAGE INCREASE WHEN HAVING A SELECT WITH A HUGE IN-LIST OF LITERAL

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DSNXOP0  03/08/11 UK65597 +00183C seens in Save area trace over
  and over (more than 100+ times).
  
  Query used:
  SELECT * FROM T1 WHERE C1 IN
  
  ('25070061010020334','25070055010022557','25070062010064507',
  ...);
  
  
  DSNXOP0 has allocated and logical freed the stack storage used.
  
  DB2 may use a large amount of stack storage when preparing an
  SQL statement with IN list predicate containing a large number
  of literal constants, host variables, parameter markers
  
  Keywords:
  
  
  DB2STGLK/K SQLINLIST INLIST
  

Local fix

• rewrite query
  
  SELECT * FROM T1 WHERE CAST(C1 AS VARCHAR(19)) IN
  
  ('25070061010020334','25070055010022557','25070062010064507',
  ...);
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 for z/OS users of an SQL statement *
  *                 with an IN list predicate containing a large *
  *                 number of host variables, parameter markers  *
  *                 or literal constants in the type of big      *
  *                 integer, binary string or decimal float.     *
  ****************************************************************
  * PROBLEM DESCRIPTION: ABEND04E RC00E20016 at the location     *
  *                      DSNSLD1.DSNSVSTK:+0656 could happen for *
  *                      an SQL statement with IN list predicate *
  *                      containing a large number of host       *
  *                      variables, parameter markers or literal *
  *                      constants in the type of big integer,   *
  *                      binary string or decimal float.         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  ABEND04E RC00E20016 at the location DSNSLD1.DSNSVSTK:+0656 could
  happen for an SQL statement with IN list predicate containing a
  large number of host variables, parameter markers or literal
  constants in the type of big integer, binary string or decimal
  float.
  
  DB2 did not correctly process the aforementioned IN list
  predicate, which caused the ABEND.
  

Problem conclusion

• DB2 has been modified to correctly process the aforementioned IN
  list predicate, so there will be no ABEND.
  
  Additional keywords: SQLIN SQLDECFLOAT SQLHOSTVAR
                       PARAMETERMARKER SQLPMARKER SQLPMK
  

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:

  UK75160

Modules/Macros

• DSNXOP0
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK75160

     UP12/01/19 P F201

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.