The MODIFY Utility

The MODIFY Utility

The MODIFY utility is used to delete rows from DB2 Catalog and DB2 Directory tables. MODIFY is the clean-up utility. When COPY information in the DB2 Catalog or DB2 Directory is no longer relevant or desirable, MODIFY can be used to delete the unwanted rows. The MODIFY RECOVERY utility deletes rows related to data recovery from both the DB2 Catalog and DB2 Directory. The MODIFY STATISTICS utility deletes rows related to database statistics from the DB2 Catalog.

MODIFY Phases

The MODIFY utility uses three phases, regardless of whether recovery or statistical information is being deleted:

UTILINIT	Sets up and initializes the MODIFY utility
MODIFY	Deletes rows from the appropriate DB2 Catalog table(s)
UTILTERM	Performs the final utility cleanup

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 :

SYSIBM.SYSCOLDIST_HIST	SYSIBM.SYSCOLUMNS_HIST
SYSIBM.SYSINDEXES_HIST	SYSIBM.SYSINDEXPART_HIST
SYSIBM.SYSINDEXSTATS_HIST	SYSIBM.SYSLOBSTATS_HIST
SYSIBM.SYSTABLEPART_HIST	SYSIBM.SYSTABSTATS_HIST
SYSIBM.SYSTABLES_HIST

Before a process or user can execute the MODIFY STATISTICS utility, one of the following privileges must already exist or have been granted to the user or process:

• DBADM authority for the database

• DBCTRL authority for the database

• DBMAINT authority for the database

• SYSADM authority

• SYSCTRL authority

• Install SYSOPR (only on DB2 Directory and DB2 Catalog table spaces)

The DELETE Option

The DELETE parameter is used to indicate which rows are to be deleted from which DB2 Catalog tables. There are three options that can be specified on the DELETE parameter:

• ALL ” Deletes all statistical history rows that are related to the specified database object from all of the DB2 Catalog history tables.

• ACCESSPATH ” Deletes only the statistics that are relevant for access path determination for the specified database object from the following DB2 Catalog statistical history tables: SYSIBM.SYSINDEXPART_HIST , SYSIBM.SYSTABLEPART_HIST , and SYSIBM.SYSLOBSTATS_HIST .

• SPACE ” Deletes only the space tuning statistics for the specified database object from the following DB2 Catalog statistical history tables: SYSIBM.SYSINDEXPART_HIST , SYSIBM.SYSTABLEPART_HIST , and SYSIBM.SYSLOBSTATS_HIST .

Sample JCL to execute the MODIFY STATISTICS utility is provided in Listing 34.3. This job will remove the historical access path statistics for all table spaces in the GLDB0010 database that are more than 14 days old.

Listing 34.3. MODIFY STATISTICS JCL

 //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 /* // 

MODIFY STATISTICS Guidelines

When developing your MODIFY STATISTICS plan, consider following these subsequent tips and techniques.

Run MODIFY STATISTICS As Needed

Use MODIFY STATISTICS to pare down the size of the DB2 Catalog. As you execute RUNSTATS , the number of historical statistics rows will increase. Over time, the older historical statistics will cease to be of value to you. Be sure to consistently compare the number of historical statistics rows that exist against the number needed for your analysis purposes. And execute MODIFY STATISTICS to maintain the historical statistics at the proper balance for your needs.

Failure to follow this guideline will increase the size of the DB2 Catalog and potentially degrade the performance of RUNSTATS , DDL, and your catalog queries.

Historical Information Is Not Stored for Manual Catalog Changes

Be aware that when you manually insert, update, or delete information into the DB2 Catalog that DB2 will not store historical information for those modifications in the historical DB2 Catalog tables.

Terminate and Restart Issues

It is possible to terminate the MODIFY STATISTICS utility during any of its three phases. And you can then restart the MODIFY STATISTICS utility, too. However, it will restart from the beginning all over again.