PI10242: DROP TABLESPACE FAILS WITH -904 RESOURCE NOT AVAILABLE RC00C900BF TYPE 304 NAME DSNDB01.SYSLGRNX.X'NNNNNN'

APAR status

• Closed as documentation error.

Error description

• drop tablespace fails with sqlcode904 resource not available
  rc00c900bf type 304 name dsndb01.syslgrnx.x'nnnnnn'
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS New Function Mode (NFM)  *
  *                 and DB2 11 for z/OS users of DROP            *
  *                 TABLESPACE or DROP DATABASE commands.        *
  ****************************************************************
  * PROBLEM DESCRIPTION: DROP TABLESPACE and/or DROP DATABASE    *
  *                      commands fail with                      *
  *                      SQLCODE = -904, ERROR, UNAVAILABLE      *
  *                      RESOURCE. REASON 00C900BF,              *
  *                      RESOURCE NAME DSNDB01.SYSLGRNX....      *
  *                      or other errors or symptoms resulting   *
  *                      from a failure to obtain a lock on      *
  *                      catalog or directory objects            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
    User occasionally received SQLCODE904 with RC00C900BF when
  issuing DROP DATABASE and/or DROP TABLESPACE commands.
  DROP DATABASE and DROP TABLESPACE commands clean up their
  corresponding entries in SYSCOPY, SYSLGRNX, and various
  other DB2 catalog and directory tables as part of their
  normal processing.
    In DB2 10 for z/OS NFM and above, row level locking is used
  for SYSLGRNX and all DB2 catalog tables to support
  availability and other functional improvements throughout DB2.
  These changes can sometimes result in more locks obtained
  than expected for DROP commands.  If the lock structure size
  cannot accommodate all the lock requests, the DROP fails.
  

Problem conclusion

• Users experiencing this issue should consider increasing
  the size of their lock structures or making other
  operational changes to accommodate the additional locking.
    It is also recommended that users run MODIFY RECOVERY and
  MODIFY STATISTICS utilities on objects prior to dropping
  them as this will perform much of the cleanup that would
  otherwise have to be done by DROP.  Unlike the DROP
  command, MODIFY RECOVERY and MODIFY STATISTICS issue
  periodic commits during the cleanup of catalog and
  directory information, and therefore should not encounter
  this problem.
  
    Updates to the DB2 for z/OS SQL Reference manual and the
  DB2 for z/OS Installation and Migration Guide and online
  documentation will be made similar to the following:
  
    In DB2 for z/OS SQL Reference, under DROP, subsection
  Notes, the paragraph with the header "Deleting SYSLGRNG
  records for dropped table spaces:" is removed because the
  information pertaining to DROP not cleaning up recovery
  information is no longer true.
    In its place the following new header and text are added:
  
  Avoiding DROP failures due to excessive locking:
  
  Dropping a table space, a database, or an index with the
  COPY YES attribute will cause all of the records in
  SYSCOPY and SYSLGRNX for those objects to be deleted.
  The number of locks obtained during DROP processing can
  cause the DROP to fail, and the entire system to be
  impacted, if the lock structure size cannot accommodate
  all of the locks needed.  The likelihood of this
  increases if there are many entries in SYSCOPY, in
  SYSLGRNX, or in catalog statistics tables for the objects
  being dropped.  This is especially true if those objects
  have a large number of partitions and/or were created long ago,
  or if they are copied frequently while MODIFY RECOVERY and
  MODIFY STATISTICS utilities are run relatively infrequently.
  To avoid this problem, run MODIFY RECOVERY and MODIFY
  STATISTICS utilities on objects before dropping them.
  Specifying AGE(*) or DATE(*) is recommended to remove all
  recovery and statistics information regardless of past
  update, copy, or cleanup frequency.  Be aware that using
  AGE(*) or DATE(*) will leave your object unrecoverable
  unless a new COPY or other form of backup is then made.
  
  Also, ensure that your applications commit drops
  frequently, especially for databases containing multiple
  table spaces, and table spaces containing multiple tables.
  
    In DB2 for z/OS Installation and Migration Guide, the chapter
  "Preparing your system to install or migrate DB2," in the
  section "Preparing for DB2 data sharing," subsection
  "Storage estimates for data sharing environments," the Table
  titled "Recommendations for lock structure size" is changed
  as follows:
  
  Recommendations for lock structure size
  INITSIZE SIZE   Condition
  32 MB    64 MB  For light sharing with a low amount of updating,
                  or for a single-member data sharing group
  64 MB    128 MB For medium sharing with a moderate amount of
                  update activity
  128 MB   256 MB For a high amount of sharing with a lot of
                  update activity
  
  The changes reflect all INITSIZE and SIZE values in this table
  being double their previous values.
  

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