Modify Recovery DELETE using WHERE clause executing Merge/Modify for Db2 for z/OS.
Article ID: 264271
Updated On: 03-13-2025
Products
Issue/Introduction
Requirement to execute MODIFY RECOVERY with DELETE option to purge specific Image Copies based on both AGE and data set names.
The reason to include data set names in the DELETE criteria is that there are two weekly image copies and the requirement is to delete just one.
If utilizing just AGE it will delete both copies.
Reading the documentation, MODIFY RECOVERY Control Statement, it appears possible to do it using a WHERE clause.
Are there any limitations or restrictions?
Resolution
At the bottom of this section there is an example with a WHERE clause discussed:
UPDATE SET—Update Existing SYSCOPY Rows
In this section the WHERE clause is defined:
DELETE—Define the Records to Delete
WHERE
Specifies an SQL WHERE statement to select the SYSCOPY rows for deletion. You can select any of the SYSCOPY columns except DBNAME and TSNAME. These columns are determined by the
TABLESPACE or INDEXSPACE parameter of the MODIFY RECOVERY statement. DELETE WHERE does not rebuild DBDs, nor does it delete associated records from SYSLGRNX. The default is that DELETE WHERE is omitted.
Limits: A valid SQL WHERE clause that does not exceed 6,000 characters in length. To continue an SQL statement, column 72 must contain a non-blank character, and column 1 in the next line must be blank.
DELETE WHERE is valid with MODIFY RECOVERY.
If you specify DELETE WHERE, you cannot specify LIST or OBJ-LIST.
Use WHERE clauses carefully. All SYSIBM.SYSCOPY rows that match your WHERE clause are deleted. Deleting SYSCOPY rows indiscriminately can prevent recovery of your tablespaces or indexspaces.
The following is an example:
SYSIN cards:
This SQL WHERE clause is designed to find one image copy record only based on the timestamp and the dataset name.
//SYSIN DD *
MODIFY RECOVERY TABLESPACE dbname.tsname
DELETE WHERE DSNUM = 0
AND ICTYPE IN ('F','I')
AND DBNAME = 'dbname'
AND TSNAME = 'tsname'
AND TIMESTAMP = '2022-10-27-02.32.25.800001'
AND DSNAME = 'AUTHID1.dbname.tsname.SYSCP101.COPY'
PREVIEW YES
/*
//
SYSOUT........ set it for just PREVIEW so it would not actually delete the IC and the result can be pre-checked.
PMM0000I - CONTROL STATEMENT PARSING COMPLETED SUCCESSULLY
PMM0001I - MODIFY RECOVERY TABLESPACE dbname.tsname DELETE WHERE DSNUM = 0 AND
PMM0001I - ICTYPE IN ('F', 'I') AND DBNAME = 'dbname' AND TSNAME = 'tsname'
PMM0001I - AND TIMESTAMP = '2022-10-27-02.32.25.800001' AND DSNAME =
PMM0001I - 'AUTHID1.dbname.tsname.SYSCP101.COPY' PREVIEW YES
PMM0224I - THE FOLLOWING SQL WILL BE ISSUED:
DELETE FROM SYSIBM.SYSCOPY WHERE (DBNAME = 'dbname' AND TSNAME =
'tsname' AND INSTANCE = 01) AND (DSNUM = 0 AND ICTYPE IN ('F', 'I')
AND DBNAME = 'dbname' AND TSNAME = 'tsname' AND TIMESTAMP =
'2022-10-27-02.32.25.800001' AND DSNAME =
'AUTHID1.dbname.tsname.SYSCP101.COPY')
PMM0212I - PREVIEW *** ROW WOULD BE DELETED FROM SYSIBM.SYSCOPY WITH THE
FOLLOWING VALUES:
COLUMN NAME VALUES
----------- -----------------------------------------------
ICTYPE = 'F',
DSNUM = 0000,
START_RBA = X'00DC50E96B473AF47600',
TIMESTAMP = '2022-10-27-02.32.25.800001',
FILESEQNO = 0000,
DEVTYPE = 3390,
DSNAME = 'AUTHID1.dbname.tsname.SYSCP101.COPY',
SHRLEVEL = 'R',
VOLSER(S) = (CATLG),
ICBACKUP = '',
ICUNIT = 'D',
STYPE = '',
PIT_RBA = X'00000000000000000000',
GROUP_MEMBER = 'SSID',
OTYPE = 'T',
LOWDSNUM = 0001,
HIGHDSNUM = 0001,
COPYPAGESF = +0.5E+01,
NPAGESF = +0.216E+,
CPAGESF = +0.37E+0,
JOBNAME = 'AUTHID1A',
AUTHID = 'AUTHID1',
OLDEST_VERSION = 0000,
LOGICAL_PART = 0000,
LOGGED = 'Y',
TTYPE = '',
INSTANCE = 01,
RELCREATED = 'Q',
MODECREATED = '..'
PMM0214I - MODIFY STATEMENT PROCESSING COMPLETE. HIGHEST RETURN CODE = 0
PUT0136I - Primary restart rows deleted
Then when run with PREVIEW NO
PMM0000I - CONTROL STATEMENT PARSING COMPLETED SUCCESSULLY
PMM0001I - MODIFY RECOVERY TABLESPACE dbname.tsname DELETE WHERE DSNUM = 0 AND
PMM0001I - ICTYPE IN ('F', 'I') AND DBNAME = 'dbname' AND TSNAME = 'tsname'
PMM0001I - AND TIMESTAMP = '2022-10-27-02.32.25.800001' AND DSNAME =
PMM0001I - 'AUTHID1.dbname.tsname.SYSCP101.COPY' PREVIEW NO
PMM0224I - THE FOLLOWING SQL WILL BE ISSUED:
DELETE FROM SYSIBM.SYSCOPY WHERE (DBNAME = 'dbname' AND TSNAME =
'tsname' AND INSTANCE = 01) AND (DSNUM = 0 AND ICTYPE IN ('F', 'I')
AND DBNAME = 'dbname' AND TSNAME = 'tsname' AND TIMESTAMP =
'2022-10-27-02.32.25.800001' AND DSNAME =
'AUTHID1.dbname.tsname.SYSCP101.COPY')
PMM0212I - ROW WAS DELETED FROM SYSIBM.SYSCOPY WITH THE
FOLLOWING VALUES:
COLUMN NAME VALUES
----------- -----------------------------------------------
ICTYPE = 'F',
DSNUM = 0000,
START_RBA = X'00DC50E96B473AF47600',
TIMESTAMP = '2022-10-27-02.32.25.800001',
FILESEQNO = 0000,
DEVTYPE = 3390,
DSNAME = 'AUTHID1.dbname.tsname.SYSCP101.COPY',
SHRLEVEL = 'R',
VOLSER(S) = (CATLG),
ICBACKUP = '',
ICUNIT = 'D',
STYPE = '',
PIT_RBA = X'00000000000000000000',
GROUP_MEMBER = 'SSID',
OTYPE = 'T',
LOWDSNUM = 0001,
HIGHDSNUM = 0001,
COPYPAGESF = +0.5E+01,
NPAGESF = +0.216E+,
CPAGESF = +0.37E+0,
JOBNAME = 'AUTHID1A',
AUTHID = 'AUTHID1',
OLDEST_VERSION = 0000,
LOGICAL_PART = 0000,
LOGGED = 'Y',
TTYPE = '',
INSTANCE = 01,
RELCREATED = 'Q',
MODECREATED = '..'
PMM0214I - MODIFY STATEMENT PROCESSING COMPLETE. HIGHEST RETURN CODE = 0
PUT0136I - Primary restart rows deleted
Feedback
