The MODIFY Utility

The MODIFY RECOVERY Utility

The MODIFY RECOVERY utility removes recovery information from SYSIBM.SYSCOPY and DSNDB01.SYSLGRNX . Recovery information can be removed in two ways. You can delete rows that are older than a specified number of days, or before a specified date.

You cannot use MODIFY RECOVERY to explicitly remove index copies from the DB2 Catalog. Index copies are removed implicitly as table space copies are removed. When you run MODIFY RECOVERY on a table space, the utility also removes SYSIBM.SYSCOPY and DSNDB01.SYSLGRNX rows that meet the AGE and DATE criteria for related indexes that were defined with COPY YES .

CAUTION

Records are not removed from SYSLGRNX when you drop a table space. However, if you create a new table space and DB2 reuses the DBID and PSID , then the SYSLGRNX records will be deleted. But it is a good idea to regularly run MODIFY RECOVERY to remove SYSLGRNX records so that you are not stuck with obsolete records after dropping a table space.

The JCL to execute the MODIFY utility with the RECOVERY option is provided in Listing 34.2. Both the AGE and DATE options are shown.

Listing 34.2. MODIFY RECOVERY JCL

//DB2JOBU  JOB (UTILITY),'DB2 MOD RCV',MSGCLASS=X,CLASS=X,

//          NOTIFY=USER

//*

//****************************************************************

//*

//*            DB2 MODIFY RECOVERY UTILITY

//*

//****************************************************************

//*

//UTIL EXEC DSNUPROC,SYSTEM=DSN,UID='MODIRECV',UTPROC="

//*

//*  UTILITY INPUT CONTROL STATEMENTS

//*    1. The first statement deletes all SYSCOPY information

//*       older than 80 days for the named table space.

//*    2. The second statement deletes all SYSCOPY information

//*       with a date before December 31, 1999 for the named

//*       table space.

//*

//DSNUPROC.SYSIN    DD  *

    MODIFY RECOVERY TABLESPACE DSN8D81A.DSN8S81E AGE (80)

    MODIFY RECOVERY TABLESPACE DSN8D81A.DSN8S81D DATE (19991231)

/*

//

MODIFY RECOVERY Locking Considerations

The MODIFY RECOVERY utility can run concurrently on the same object with all utilities except the following:

• COPY TABLESPACE

• LOAD

• MERGECOPY

• MODIFY RECOVERY

• RECOVER

• REORG

The MODIFY RECOVERY utility will drain write claim classes for the table space or partition being operated upon.

MODIFY RECOVERY Guidelines

When running MODIFY RECOVERY you should consider using the following tips and techniques.

Run MODIFY RECOVERY Regularly

The MODIFY RECOVERY utility should be run monthly to eliminate old recovery information stored in SYSIBM.SYSCOPY and DSNDB01.SYSLGRNX . Running this utility more frequently is usually difficult to administer. Running it less frequently causes the recovery tables to grow, affecting the performance of the DB2 CHECK , COPY , LOAD , MERGECOPY , RECOVER , and REORG utilities. Access to other DB2 Catalog tables on the same DASD volumes as these tables also may be degraded.

CAUTION

The MODIFY RECOVERY utility places an X lock on the SYSCOPY table space. As such, run MODIFY RECOVERY when there is little or no concurrent SYSCOPY activity.

O ld recovery information must be defined on an application-by-application basis. Usually, DB2 applications run the COPY utility for all table spaces at a consistent time. Sometimes, however, the definition of what should be deleted must be made on a tablespace-by-tablespace basis. One way to define "old recovery information" is anything that is older than the oldest archive log.

CAUTION

MODIFY RECOVERY will delete records from SYSLGRNX only if there are SYSCOPY records to delete. So, if you never took an image copy, SYSLGRNX records were never deleted.

To resolve this situation, start by making image copies of the table spaces in question. The next day, take another image copy of the same table spaces and then run MODIFY RECOVERY specifying DELETE AGE(0) . This will delete all but the most recent image copy information from SYSCOPY and SYSLGRNX .

Ensure That Two Full Copies Are Always Available

As a general rule, leave at least two full image copy data sets for each table space in the SYSIBM.SYSCOPY table. In this way, DB2 can use a previous image copy if the most recent one is damaged or unavailable. Additionally, if the full image copy data sets are SHRLEVEL CHANGE , ensure that the log is older than the oldest image copy. If the log does not predate the oldest image, the image copy is not very useful.

Synchronize MODIFY RECOVERY Execution with the Deletion of Log and Copy Data Sets

The MODIFY RECOVERY utility deletes rows from only the SYSIBM.SYSCOPY and DSNDB01.SYSLGRNX tables. It does not physically delete the image copy data sets corresponding to the deleted SYSIBM.SYSCOPY rows, nor does it physically delete the log data sets associated with the deleted DSNDB01.SYSLGRNX log ranges. To delete these data sets, run separate jobs ”at the same time that MODIFY RECOVERY is run ”using IEFBR14 or IDCAMS . Alternatively, assign an expiration date to the log data sets.

Be Aware of Copy Pending Ramifications

If MODIFY RECOVERY deletes recovery information for a table space such that full recovery cannot be accomplished, the table space is placed in copy pending status.

Be Aware of the Nonstandard DATE Format

Be careful when specifying the DATE option of the MODIFY RECOVERY utility. The data is in the format YYYYMMDD , rather than the standard DB2 date format. If you want October 16, 2002, for example, you must specify it as 20021016 rather than as 2002-10-16.

The MODIFY STATISTICS Utility

The purpose of the MODIFY STATISTICS utility is to remove unwanted, or outdated , statistics history records from the DB2 system catalog tables.

NOTE

Long-time DB2 users might remember the MODIFY STATISTICS utility. It existed in older releases of DB2 to remove non-uniform distribution statistics. With DB2 V7, MODIFY STATISTICS isreintroduced, but with enhanced functionality.

Using MODIFY STATISTICS you can remove rows based on date or age. By specifying a date, MODIFY STATISTICS will remove any historical statistics that are older than that date; by specifying an age, MODIFY STATISTICS will remove any historical statistics that are at least that old. Furthermore, you can target the effects of MODIFY STATISTICS to a table space, an index space, or an index.

It is a good idea to run the MODIFY STATISTICS utility on a regular basis to get rid of old and outdated statistical information in the DB2 Catalog. By deleting outdated information, you can improve performance for processes that access data from the historical statistics tables in the DB2 Catalog.

CAUTION

Be careful not to delete historical statistics that you want to maintain for performance analysis purposes. It can be useful to keep historical statistics to compare the database size and structure information from one period to another. But if you use MODIFY STATISTICS to delete the historical statistics, that information is forever lost and you can no longer use it for such purposes.

The DB2 Catalog tables that contain historical statistics are as follows :

//DB2JOBU  JOB (UTILITY),'DB2 MOD RCV',MSGCLASS=X,CLASS=X,

//          NOTIFY=USER

//*

//****************************************************************

//*

//*            DB2 MODIFY RECOVERY UTILITY

//*

//****************************************************************

//*

//UTIL EXEC DSNUPROC,SYSTEM=DSN,UID='MODISTAT',UTPROC="

//*

//*  UTILITY INPUT CONTROL STATEMENTS

//*    1. Remove all historical stats over 14 days old

//*       for all table spaces in the GLDB0010 database.

//*

//DSNUPROC.SYSUT1 DD DSN=DB2JOBU. MODISTAT.STEP1.SYSUT1,

//       DISP=(MOD,DELETE,CATLG),

//       UNIT=SYSDA,SPACE=(8000,(200,20),,,ROUND)

//DSNUPROC.SYSERR DD DSN=DB2JOBU. MODISTAT.STEP1.SYSERR,

//       DISP=(MOD,DELETE,CATLG),

//       UNIT=SYSDA,SPACE=(6000,(20,20),,,ROUND)

//DSNUPROC.SORTOUT DD DSN=DB2JOBU. MODISTAT.STEP1.SORTOUT,

//       DISP=(MOD,DELETE,CATLG),

//       UNIT=SYSDA,SPACE=(6000,(20,20),,,ROUND)

//DSNUPROC.SYSIN    DD  *

   LISTDEF GLDB INCLUDE TABLESPACE GLDB0010.*

   MODIFY STATISTICS LIST GLDB

       DELETE ACCESSPATH AGE 14

/*

//