PI27485: SQLCODE -20005 SQLCODE20005 DSNXICQ1 ON CREATE SEQUENCE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• in cases of massive create sequence objects, SQLCODE -20005
  is issued due to serialisation problems with misleading
  information on reached internal limit of 2147483391.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10, 11 for z/OS users of CREATE      *
  *                 SEQUENCE                                     *
  ****************************************************************
  * PROBLEM DESCRIPTION: Due to massive number of concurrent     *
  *                      requests of sequence creation, CREATE   *
  *                      SEQUENCE may misleadingly result in     *
  *                      SQLCODE20005, the internal ID limit of  *
  *                      2147483391 of sequence has been         *
  *                      exceeded                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  
  On massively concurrent requests of sequence creation, DB2
  will issue SQLCODE20005 on generating a unique sequence ID
  when a max 100 times of retries has been reached. This may be
  a misleading message as the internal ID limit may have not
  been reached.
  

Problem conclusion

• DB2 has been modified to improve the retry process such that
  it correctly issues SQLCODE20005 when the internal limit of
  available sequence IDs has been actually reached. The new logic
  also allows DB2 to reuse dropped sequence IDs.
  
  
  Additional Keywords
  SQLCREATE
  SQLSEQUENCE
  SQLCODE20005
  

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:

  UI23423 UI23424

Modules/Macros

•    DSNXICQ1 DSNXISB8
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI23423

     UP14/12/11 P F412

• RB10 PSY UI23424

     UP14/12/11 P F412

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.