Data maintenance should initially start with the RUNSTATS utility to collect the base statistics. After performing RUNSTATS, you can analyze the statistics to determine whether a REORG is necessary, or the REORG utility can review the statistics collected and, by using the REORG triggers, determine whether a REORG is needed.
If reorganization is needed, use the REORG utility on the selected objects, and then do RUNSTATS and REBIND. You must perform a REBIND on any packages affected by the preceding operations so that they can take advantage of the benefits of the new physical organization and updated statistics. After performing subsequent update, insert, and delete operations as part of the data maintenance process, repeat by first executing the RUNSTATS utility.
Establish a routine for RUNSTATS and the REBIND processes. Updated statistics will give you precise information about your database state.
A number of columns in the DB2 catalog can be updated in order for a DBA to model a production environment so that the access paths against production data can be determined even if the data does not physically exist. Refer to Chapter 2 for more information on the catalog tables, and refer to Chapter 17 for updating catalog columns to help predict access paths.
Packages should be rebound in order to have the access paths reevaluated when any of the following are true, based on current statistics from the catalog (for additional information on package binds, refer to Chapter 11).
For more information on packages and rebinds, refer to Chapter 11.
Other Data Maintenance Utilities
Three utilities are used to check the integrity of data and indexes and are often required to remove restrictive statuses. The utilities are
This online utility checks tables in table spaces for violations of referential and table-check constraints. It then reports information about any violations that are detected during its execution. The CHECK DATA utility will need to be executed after a conditional restart or a PIT recovery on all table spaces where parent and dependent tables might not be synchronized. (See Chapter 8 for more information on recovery.) The CHECK DATA utility can be executed against a base table space only, not a LOB table space.
Rows that violate referential or table-check constraints can be optionally deleted by the CHECK DATA utility. Any row that violates one or more constraints can also be copied, once, to an exception table. If any violation of constraints is found, CHECK DATA puts the table space being checked into the CHECK-pending status.
CHECK DATA has the following phases.
An example of using the CHECK DATA utility to check for and delete all constraint violations in table space DB2CERT.CERTTS follows:
CHECK DATA TABLESPACE DB2CERT.CERTTS FOR EXCEPTION IN DB2USER1.TEST_TAKEN USE DB2USER1.EXCP_TT DELETE YES
After a successful execution, CHECK DATA will reset the CHECK-pending (CHKP) status.
This online utility tests whether indexes are consistent with the data on which the index is created. The utility then issues warning messages when an inconsistency is found. The CHECK INDEX utility should be executed after a conditional restart or a PIT recovery on all table spaces whose indexes may not be consistent with the data.
The CHECK INDEX utility should also be used before CHECK DATA to ensure that the indexes used by CHECK DATA are valid. This is especially important before using CHECK DATA with DELETE YES. When checking an auxiliary table index, CHECK INDEX verifies that each LOB is represented by an index entry and that an index entry exists for every LOB.
The CHECK INDEX utility generates several messages that show whether the indexes are consistent with the data. For unique indexes, any two null values are taken to be equal unless the index was created with the UNIQUE WHERE NOT NULL clause. In that case, if it is a single column, the key can contain any number of null values, and CHECK INDEX does not issue an error message. CHECK INDEX issues an error message if there are two or more null values and the unique index was not created with the UNIQUE WHERE NOT NULL clause.
The phases of execution of the CHECK INDEX utility are very simple. It starts with the initialization and setup; then it unloads the index entries, sorts them and performs a scan of data to validate index entries, and performs any necessary cleanup.
An example of using the CHECK INDEX utility for all indexes in table space CERTTS follows:
CHECK INDEX (ALL)TABLESPACE CERTTS SORTDEVT 3380
This online utility can be run against a LOB table space to identify any structural defects in the LOB table space and any invalid LOB values. Run the CHECK LOB online utility against a LOB table space that is marked CHECK-pending (CHKP) to identify structural defects. If none is found, the CHECK LOB utility turns off the CHKP status.
Run the CHECK LOB online utility against a LOB table space that is in auxiliary warning (AUXW) status to identify invalid LOBs. If none exists, the CHECK LOB utility turns AUXW status off. Run CHECK LOB after a conditional restart or a PIT recovery on all table spaces where LOB table spaces might not be synchronized.
The execution phases of the CHECK LOB utility are as follows.
An example of executing the CHECK LOB utility against the CERTLBTS checks for structural defects or invalid LOBs:
CHECK LOB TABLESPACE DB2CERT.CERTLBTS EXCEPTIONS 3 WORKDDN SYSUT1, SORTOUT SORTDEVT SYSDA SORTNUM 4
After a successful execution of this utility, the CHECK-pending (CHKP) and auxiliary warning (AUXW) statuses will be reset.
Both MODIFY RECOVERY and MODIFY STATISTICS are considered maintenance utilities. Both are used to remove unwanted data from the catalog tables.
The MODIFY online utility with the RECOVERY option deletes records from the SYSIBM.SYSCOPY catalog table, related log records from the SYSIBM.SYSLGRNX directory table, and entries from the DBD. Records can be removed if they were written before a specific date or if they are of a specific age. Records can be deleted for an entire table space, partition, or data set.
This MODIFY utility should be run regularly to clear outdated information from SYSIBM.SYSCOPY and SYSIBM.SYSLGRNX. These tables, particularly SYSIBM.SYSLGRNX, can become very large and take up considerable amounts of space. For processes that access data from these tables, performance can be improved by deleting outdated information.
The MODIFY RECOVERY utility automatically removes the SYSIBM.SYSCOPY and SYSIBM.SYSLGRNX recovery records that meet the age and date criteria for all indexes over the table space that were defined with the COPY YES attribute. MODIFY RECOVERY deletes image-copy rows from SYSIBM.SYSCOPY and SYSIBM.SYSLGRNX. For each full and incremental SYSCOPY record deleted from SYSCOPY, the utility returns a message giving the name of the copy data set.
If MODIFY RECOVERY deletes all the SYSCOPY records, causing the target table space or partition to not be recoverable, the target object is placed in COPY-pending status.
Three MODIFY RECOVERY phases are UTILINIT for initialization and setup, MODIFY for deleting records, and UTILTERM for performing cleanup. The following example of the MODIFY RECOVERY utility shows how to delete SYSCOPY records by date for a specific table space.
MODIFY RECOVERY TABLESPACE DB2CERT.CERTTS DELETE DATE(20000414)
The MODIFY RECOVERY utility can also be used to reclaim space in the DBD after a drop of a user table has been performed. The following steps need to be performed to reclaim DBD space.
MODIFY RECOVERY can also be used to improve REORG performance after adding a column to a table. After you add a column to a table space, the next REORG of the table space materializes default values for the added column by decompressing all rows of the table space during the UNLOAD phase and then compressing them again during the RELOAD phase. Subsequently, each REORG job for the table space repeats this processing in the UNLOAD and RELOAD phases. The following procedure helps to avoid repeating the compression cycle with each REORG.
This online utility deletes unwanted statistics-history records from the corresponding catalog tables. Records written before a specific date can be removed, or records of a specific age can be removed. Records to be deleted can be specified for an entire table space, index space, or index.
The MODIFY STATISTICS utility should be run regularly to clear outdated information from the statistics-history catalog tables. Deleting outdated information from these tables can improve performance for processes that access data from these tables.
MODIFY STATISTICS deletes rows from the following catalog tables:
These tables are used for collecting historical statistics from a RUNSTATS execution. For more information, refer to the RUNSTATS discussion earlier in this chapter.
The DELETE ALL option must be specified to delete rows from the SYSIBM.SYSTABLES_HIST catalog table.
The phases for MODIFY STATISTICS are the same as for MODIFY RECOVERY: UTILINT, MODIFY, and UTILTERM.
An example of MODIFY STATISTICS follows. This example removes rows that are older than 90 days from the CERTTS table space:
MODIFY STATISTICS TABLESPACE DB2CERT.CERTTS DELETE ALL AGE 90
The REPAIR online utility repairs data: either your own data or data you would not normally access, such as space map pages and index entries. REPAIR is intended as a means of replacing invalid data with valid data.
Be extremely careful using REPAIR. Improper use can damage the data even further. You can use the REPAIR utility to
The potential output from the REPAIR utility consists of a modified page or pages in the specified DB2 table space or index, as well as a dump of the contents. Execution phases of REPAIR are simply initialize, perform the repair, and then terminate the utility. The REPAIR utility cannot be restarted or used at a tracker site. (For more information on tracker sites, refer to Chapter 8.)
REPAIR should be used only by a knowledgeable person. Be careful to grant REPAIR authorization only to appropriate people.
The DIAGNOSE utility generates information useful in diagnosing problems. The DIAGNOSE utility can output the following types of information:
In the following example, a DIAGNOSE utility views MEPLs that can be used to find the service level, including most recent APAR (authorized program analysis report) and PTF (program temporary fix), and when they were installed, of a specific DB2 module:
DIAGNOSE DISPLAY MEPL
A number of utilities can be executed outside of DB2. These utilities are often referred to as standalone utilities, or offline utilities.
DSNJLOGF (Preformat Active Log)
When writing to an active log data set for the first time, DB2 must preformat a VSAM control area before writing the log records. The DSNJLOGF utility avoids this delay by preformatting the active log data sets before bringing them online to DB2.
DSNJU003 (Change Log Inventory)
The DSNJU003 standalone utility changes the bootstrap data sets (BSDSs).You can use the utility to
DSNJU004 (Print Log Map)
This utility lists the following information:
In a data sharing environment, the DSNJU004 utility can list information from any or all BSDSs of a data sharing group.
The DSN1CHKR utility verifies the integrity of DB2 directory and catalog table spaces and DSN1CHKR scans the specified table space for broken links, broken hash chains, and records that are not part of any link or chain. Use DSN1CHKR on a regular basis to promptly detect any damage to the catalog and directory.
This utility estimates space savings to be achieved by DB2 data compression in table spaces. For more information on compression, refer to Chapter 4.
This utility can be run on the following types of data sets containing uncompressed data:
DSN1COMP does not estimate savings for data sets that contain LOB table spaces or index spaces.
Following is an example of the type of output from a DSN1COMP utility:
DSN194 I DSN1COMP COMPRESSION REPORT 301 KB WITHOUT COMPRESSION 224 KB WITH COMPRESSION 25 PERCENT OF THE BYTES WOULD BE SAVED 1,975 ROWS SCANNED TO BUILD DICTIONARY 4,665 ROWS SCANNED TO PROVIDE COMPRESSION ESTIMATE 4,096 DICTIONARY ENTRIES 81 BYTES FOR AVERAGE UNCOMPRESSED ROW LENGTH 52 BYTES FOR AVERAGE COMPRESSED ROW LENGTH 16 DICTIONARY PAGES REQUIRED 110 PAGES REQUIRED WITHOUT COMPRESSION 99 PAGES REQUIRED WITH COMPRESSION 10 PERCENT OF THE DB2 DATA PAGES WOULD BE SAVED
The DSN1COPY standalone utility lets you copy
These copies can then be used to restore data. The restore can occur on the same DB2 or another DB2 subsystem. This is one way to be able to move data between subsystems.
DSN1COPY also provides the ability to
DSN1COPY is compatible with LOB table spaces, when you specify the LOB keyword and omit the SEGMENT and INLCOPY keywords.
The DSN1LOGP utility formats the contents of the recovery log for display. The formats can be either a detail report of individual log records or a summary report. The detail report helps IBM Support Center personnel analyze the log in detail. (This book does not include a full description of the detail report.) The summary report helps you perform a conditional restart, resolve in-doubt threads with a remote site, and detect problems with data propagation.
You can specify the range of the log to process and select criteria within the range to limit the records in the detail report. For example, you can specify one or more units of recovery identified by URID (unit of recovery ID) or a single database.
By specifying a URID and a database, you can display recovery-log records that correspond to the use of one database by a single unit of recovery.
With the DSN1PRNT standalone utility, you can print
Using DSN1PRNT, you can print hexadecimal dumps of DB2 data sets and databases. If you specify the FORMAT option, DSN1PRNT formats the data and indexes for any page that does not contain an error that would prevent formatting. If it detects such an error, DSN1PRNT prints an error message just before the page and dumps the page without formatting. Formatting resumes with the next page. Compressed records are printed in compressed format.
DSN1PRNT is especially useful when you want to identify the contents of a table space or an index. You can run DSN1PRNT on image copy data sets, as well as on table spaces and indexes. DSN1PRNT accepts an index image copy as input when you specify the FULLCOPY option.
DSN1PRNT is compatible with LOB table spaces, when you specify the LOB keyword and omit the INLCOPY keyword.
Under the direction of the IBM Support Center, use the IFC Selective Dump (DSN1SDMP) utility to force dumps when selected DB2 trace events occur and to write DB2 trace records to a user-defined MVS data set.