A fix is available
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
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
08 August 2012