PM52724: MASS DELETES ENDS UP WITH LOCK ESCALATION ON SYSCOPY IN V10. BECAUSE PM30991 INTALLED CODE INSERTING L 12/01/04 PTF PECHANGE

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Mass deletes ends up with lock escalation on SYSCOPY in V10.
    Because PM30991 intalled code inserting L records into SYSCOPY.
    ***                                    ***
    ***Additional information for customers***
    It is fine to apply the PTF for PM30991 (UK66327) even if it is
    marked as PE, if your user applications:
     - do not issue any SQL mass deletes (DELETE FROM TABLE without
       a WHEN clause)   -or-
     - issue SQL mass deletes in a controlled manner with commits.
    The problem described in PM52724 occurs for
    applications issuing SQL mass deletes.  An example of this
    is an application that issues millions of SQL mass delete
    statements without any commits.
    

Local fix

  • This WHERE clause may be added to the SQL mass
    delete statement:  WHERE 1=1;
    to avoid the insert of the syscopy records,
    because DB2 will no longer treat the SQL statement
    as a mass delete.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: DB2 10 for z/OS users of SQL mass delete     *
    *                 and the RECOVER utility with the BACKOUT     *
    *                 YES option.                                  *
    ****************************************************************
    * PROBLEM DESCRIPTION: After the application of PTF UK66327    *
    *                      (APAR PM30991), an application issuing  *
    *                      millions of SQL mass deletes on a       *
    *                      user table without commits caused       *
    *                      lock escalation to occur on catalog     *
    *                      table space DSNDB06.SYSCOPY resulting   *
    *                      in contention for COPY utility jobs     *
    *                      executing at the same time.             *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
      After the application of PTF UK66327 (APAR PM30991), an
    application issuing millions of SQL mass deletes on a user
    table without any commits caused lock escalation to occur
    on catalog table space DSNDB06.SYSCOPY resulting in timeouts
    for COPY utility jobs executing at the same time.
      PTF UK66327/APAR PM30991, added code to insert a
    SYSIBM.SYSCOPY record with ICTYPE='L' and STYPE='M' (L-M) when
    a SQL mass delete, TRUNCATE TABLE, DROP TABLE, or ALTER
    TABLE ROTATE PARTITION is executed on a segmented or universal
    table space.  This SYSCOPY record was used to prohibit a
    subsequent RECOVER BACKOUT YES since the deleted data is not
    logged for these activities.  See the closing text of PM30991
    for more details.
      The insert of the L-M SYSCOPY record can cause problems
    for some customers, if SQL mass deletes are heavily used
    without commits.  In addition to contention on SYSCOPY, the
    large number of SYSCOPY records inserted will cause the
    SYSCOPY table space to grow.
    

Problem conclusion

  •   The L-M SYSCOPY record will no longer be inserted for SQL
    mass delete, TRUNCATE TABLE, DROP TABLE, or ALTER TABLE
    ROTATE PARTITION on segmented or UTS tables spaces.  The
    RECOVER utility will continue to check for the L-M SYSCOPY
    record and issue msgDSNU556I (RECOVER CANNOT PROCEED FOR...
    BECAUSE A SYSCOPY RECORD HAS BEEN ENCOUNTERED...) if found.
      A new diagnostic log record is written for SQL mass delete,
    TRUNCATE TABLE, and DROP TABLE.  DSN1LOGP has been modified
    to format the new diagnostic log record.
      The RECOVER utility with the BACKOUT YES option will issue
    a new message, msgDSNU1544I, if the diagnostic log record is
    encountered during the LOGCSR or LOGUNDO phase:
    ..
    DSNU1544I csect RECOVER BACKOUT YES CANNOT PROCEED ON obj-type
                    obj-qual.obj-nm DUE TO SQL MASS DELETE OR OTHER
                    TABLE ACTIVITY
    ..
    Explanation:  BACKOUT YES was specified for the RECOVER utility,
                  but it cannot proceed because a prior SQL mass
                  DELETE, TRUNCATE TABLE, or DROP TABLE occurred
                  on the object.  The deleted data for these
                  activities are not logged, hence cannot be backed
                  out.  The recovery is prohibited when this kind of
                  activity has occurred between the recovery point
                  (specified by TOLOGPOINT or TORBA) and the current
                  time.
    ..
    System action:  The utility terminates processing.
    ..
    User response:  Remove the BACKOUT YES option from the RECOVER
                    utility statement and resubmit the job.  This
                    will recover the list of objects by restoring a
                    backup then applying the logs forward to the
                    recovery point.
    ..
    Severity:  8 (error)
    ..
      Note that if the table space has indexes or auxiliary objects
    (LOB or XML table spaces and all auxiliary indexes) RECOVER
    BACKOUT YES is not supported on these objects when SQL mass
    delete, TRUNCATE TABLE, DROP TABLE, or ALTER TABLE ROTATE
    PARTITION has occurred on the base table space.
      The new diagnostic log record is not needed for the ALTER
    TABLE ROTATE PARTITION case because a SYSCOPY record with
    ICTYPE='A' and STYPE='R' is already inserted.  In this case,
    RECOVER with the BACKOUT YES option will be prohibited with
    msgDSNU556I.
      A change has been made to the RECOVER utility with the
    BACKOUT YES option to delay the setting of RECP on the target
    objects.  The setting of RECP on the target objects has been
    moved from the LOGCSR phase to the beginning of the LOGUNDO
    phase, because the objects are not updated until the LOGUNDO
    phase.
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PM52724

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    YesPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-11-21

  • Closed date

    2012-07-03

  • Last modified date

    2012-08-08

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

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

    UK80113

Modules/Macros

  •    DSNFMDIR DSNFUDRE DSNILDOR DSNIRCSR DSNISEGD
    DSNIUNDO DSNKMDEL DSNODALN DSNUADP1 DSNUBINS DSNUCALA DSNUCALC
    DSNUCALU DSNUCASA DSNUMSGE DSNUYAS2 DSN1LDET
    

Publications Referenced
GC19297905        

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK80113

       UP12/07/24 P F207 ®

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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM52724

Modified date:

2012-08-08

Translate my page

Machine Translation

Content navigation