As an experienced DBA and long time user of BMC’s DB2 products, you’re probably well versed in the many features and functions of BMC’s utility, COPY PLUS for DB2.  It is the linchpin of your backup strategy and continues to meet your SLA requirements while minimizing CPU and elapsed times.

But did you know that COPY PLUS also contains the BMC MODIFY utility?

If you didn’t, you are not alone and that’s the purpose of this post: to tell you about BMC MODIFY and why you want to use it instead of the IBM MODIFY utility.

BACKGROUND

Generally, DBAs use a MODIFY utility with the RECOVERY option to remove outdated information from SYSCOPY and SYSLGRNX.  These tables can become very large and take up a considerable amount of space. By deleting outdated information from these tables, you can help improve performance for processes that access data from these tables. 

In practice, you should regularly run a MODIFY RECOVERY to clean up ‘old’ image copy information.  That ‘old’ point will vary based on your image copy frequency and recovery requirements.  For example, if you make daily Full Image Copies (FICs) of your data, there comes a point at which the SYSCOPY entries about those FICs becomes ‘old’ and of no use in your recovery strategy.

Both BMC MODIFY RECOVERY and IBM MODIFY RECOVERY remove this ‘old’ information by allowing you to delete:

• all records older than a specific date,

MODIFY RECOVERY TABLESPACE DSN8D10A.DSN8S10E DELETE DATE(20020910)

• all records older than a specific age (days),

MODIFY RECOVERY TABLESPACE DSN8D10A.DSN8S10E DELETE AGE(90)

BMC MODIFY

As mentioned earlier, BMC MODIFY is a built-in ‘feature’ of COPY PLUS.  In addition to running faster than IBM MODIFY, the BMC MODIFY utility provides the following functions not available in IBM MODIFY:

• More flexibility in specifying what ‘old’ information to DELETE:
  • DELETE based on the maximum number of copies you want to retain (full or any type copy).

  MODIFY RECOVERY TABLESPACE DSN8D10A.DSN8S10E DELETE MAXFULLCOPIES(3)

  MODIFY RECOVERY TABLESPACE DSN8D10A.DSN8S10E DELETE MAXCOPIES(3)

• DELETE based on when the image copy dataset is not found in the ICF catalog

  MODIFY RECOVERY TABLESPACE DSN8D10A.DSN8S10E DELETE DSNOTFOUND

• DELETE based on specific values of the SYSCOPY columns

  MODIFY RECOVERY TABLESPACE DSN8D10A.DSN8S10E

  DELETE WHERE ICDATE=130321 AND ICTYPE=F

NOTE: After any DELETE operation, COPY-pending status is set if:

• the space is not recoverable to the current state
• no copy entries remain in SYSCOPY and at least one row was deleted
• no copy entries exist after an unrecoverable event, such as a REORG LOG NO or a LOAD LOG NO

However, BMC MODIFY allows you to specify NOCOPYPEND to indicate that you do not want to have COPY-pending status set after the deletions are processed.  This is especially useful when table spaces are loaded, but never copied.

MODIFY RECOVERY TABLESPACE DSN8D10A.DSN8S10E DELETE AGE(90)NOCOPYPEND

• Ability to verify recoverability of the object based on 3 criteria – minimum copies, maximum logs since last copy or maximum days since last copy.  If any criteria are true, you have the option to be warned (ON NOTRECOVERABLE WARN) or to trigger an image copy (ON NOTRECOVERABLE COPY).

• minimum copies (full or any type)

  MODIFY RECOVERY TABLESPACE DSN8D10A.DSN8S10E VERIFY MINIMUM FULLCOPIES 5

  ON NOTRECOVERABLE WARN

  MODIFY RECOVERY TABLESPACE DSN8D10A.DSN8S10E VERIFY MINIMUM COPIES 5

  ON NOTRECOVERABLE WARN

• maximum DB2 LOG datasets since last image copy

  MODIFY RECOVERY TABLESPACE DSN8D10A.DSN8S10E VERIFY MAXIMUM LOGS 7

  ON NOTRECOVERABLE WARN

• maximum days since last image copy

  MODIFY RECOVERY TABLESPACE DSN8D10A.DSN8S10E VERIFY MAXIMUM DAYS 7

  ON NOTRECOVERABLE COPY USING TEMPLATE COPYDS

  OUTPUT LOCALP UNIT SYSDA

         DSNAME SLSJLK.LP.&DB.&TS.D&DATE.T&TIME        

  TEMPLATE COPYDS

           COPY TABLESPACE *.*                         

           COPYDDN(LOCALP)                             

           SHRLEVEL REFERENCE                          

           RESETMOD NO                                 

• Ability to use SQL like syntax to INSERT SYSCOPY rows

MODIFY RECOVERY TABLESPACE DSN8D10A.DSN8S10E

INSERT

START_RBA = X'0003632123F6'

ICTYPE = F

SHRLEVEL = R

DSNAME = BMCCOPY.DSN8D10A.DSN8S10E.D970110.T180000

DEVTYPE = VCRT

ICUNIT = T

ICDATE = 050331

ICTIME = 180000

• Ability to use SQL like syntax to UPDATE SYSCOPY rows

MODIFY RECOVERY TABLESPACE DSN8D10A.DSN8S10E

UPDATE

SET DEVTYPE = RCART

WHERE ICBACKUP = RP

AND DEVTYPE = 3490

Hopefully this post was a helpful reminder to COPY PLUS users that you also have access to the BMC MODIFY utility.  If you have additional questions on using COPY PLUS or BMC MODIFY, please contact your local BMC DB2 Software consultant or BMC Support (800-537-1813).