Statistics Usage

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.

Rebinds

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).

• Changes > 20 percent (NLEAF, NPAGES, NACTIVE).

• Cluster ratio < 80 percent; NLEVELS > 2.

• HIGH2KEY and LOW2KEY ranges change > 10 percent.

• Cardinality and row count change > 20 percent.

For more information on packages and rebinds, refer to Chapter 11.

Other Data Maintenance Utilities

CHECK Utilities

Three utilities are used to check the integrity of data and indexes and are often required to remove restrictive statuses. The utilities are

• CHECK DATA

• CHECK INDEX

• CHECK LOB

CHECK DATA

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.

• UTILINIT is the initialization of the utility.

• SCANTAB extracts the foreign keys. If it exists, the foreign-key index will be used for this; otherwise, a table scan will be performed.

• SORT sorts the foreign keys if not already extracted from the foreign-key index.

• CHECKDAT looks in primary indexes for foreign-key parents and issue messages to report any errors detected. It will report the RID of the row, the table that contained the row, and the constraint name that was violated.

• REPORTCK copies the error rows into exception tables and then deletes these rows from the source table if the DELETE YES option was specified.

• UTILTERM performs any necessary cleanup operations.

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.

CHECK INDEX

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 

CHECK LOB

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.

• UTILINIT is the initialization of the utility.

• CHECKLOB scans all active pages of the LOB table space.

• SORT sorts four types of records from the CHECKLOB phase and reports four times the number of rows sorted.

• REPRTLOB examines records that are produced by the CHECKLOB phase and sorted by the SORT phase and then issues error messages.

• UTILTERM performs any necessary cleanup.

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.

MODIFY Utilities

Both MODIFY RECOVERY and MODIFY STATISTICS are considered maintenance utilities. Both are used to remove unwanted data from the catalog tables.

Modify Recovery

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.

NOTE

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.

1.	Commit the drop.
2.	Run the REORG utility.
3.	Run the COPY utility to make a full image copy of the table space.
4.	Run MODIFY RECOVERY with the DELETE option to delete all previous image copies.

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.

1.	Run the REORG utility on the table space.
2.	Run the COPY utility to make a full-image copy of the table space.
3.	Run MODIFY RECOVERY with the DELETE option to delete all previous image copies.

Modify Statistics

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:

• SYSIBM.SYSCOLDIST_HIST

• SYSIBM.SYSCOLUMNS_HIST

• SYSIBM.SYSINDEXES_HIST

• SYSIBM.SYSINDEXPART_HIST

• SYSIBM.SYSINDEXSTATS_HIST

• SYSIBM.SYSLOBSTATS_HIST

• SYSIBM.SYSTABLEPART_HIST

• SYSIBM.SYSTABSTATS_HIST

These tables are used for collecting historical statistics from a RUNSTATS execution. For more information, refer to the RUNSTATS discussion earlier in this chapter.

NOTE

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 

Repair Utility

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

• Test DBDs

• Repair DBDs

• Reset a pending status, such as COPY pending, on a table space or index

• Verify the contents of data areas in table spaces and indexes

• Replace the contents of data areas in table spaces and indexes

• Delete a single row from a table space

• Produce a hexadecimal dump of an area in a table space or index

• Reset the level ID

• Change the PSID (Page Set Identifier) in the header page

• Delete an entire LOB from a LOB table space

• Dump LOB pages

• Rebuild OBDs (object identifier) for a LOB table space

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.)

NOTE

REPAIR should be used only by a knowledgeable person. Be careful to grant REPAIR authorization only to appropriate people.

Diagnose Utility

The DIAGNOSE utility generates information useful in diagnosing problems. The DIAGNOSE utility can output the following types of information:

• OBD of the table space and/or index space

• Records from SYSIBM.SYSUTIL

• Module entry-point lists (MEPLs)

• Available utilities on the subsystem

• Database exception table (DBET)

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 

Standalone Utilities

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

• Add or delete active or archive log data sets

• Add or delete checkpoint records

• Create a conditional restart control record to control the next start of the DB2 subsystem

• Change the VSAM catalog name entry in the BSDS

• Modify the communication record in the BSDS

• Modify the value for the highest-written log RBA value (relative byte address within the log) or the highest-offloaded RBA value

DSNJU004 (Print Log Map)

This utility lists the following information:

• Log data set name, log RBA association, and log LRSN (log record sequence numberfor both copies of all active and archive log data sets

• Active log data sets that are available for new log data

• Status of all conditional restart control records in the bootstrap data set

• Contents of the queue of checkpoint records in the bootstrap data set

• The communication record of the BSDS, if one exists

• Contents of the quiesce history record

• System and utility timestamps

• Contents of the checkpoint queue

In a data sharing environment, the DSNJU004 utility can list information from any or all BSDSs of a data sharing group.

DSN1CHKR

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.

DSN1COMP

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:

• DB2 full-image-copy data sets

• VSAM data sets that contain DB2 table spaces

• Sequential data sets that contain DB2 table spaces, such as DSN1COPY output

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 

DSN1COPY

The DSN1COPY standalone utility lets you copy

• DB2 VSAM data sets to sequential data sets

• DSN1COPY sequential data sets to DB2 VSAM data sets

• DB2 image-copy data sets to DB2 VSAM data sets

• DB2 VSAM data sets to other DB2 VSAM data sets

• DSN1COPY sequential data sets to other sequential data sets

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

• Print hexadecimal dumps of DB2 data sets and databases

• Check the validity of data or index pages, including dictionary pages for compressed data

• Translate database object identifiers (OBIDs) to enable moving data sets between different systems and resetting to 0 the log RBA that is recorded in each index page or data page

DSN1COPY is compatible with LOB table spaces, when you specify the LOB keyword and omit the SEGMENT and INLCOPY keywords.

DSN1LOGP

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.

DSN1PRNT

With the DSN1PRNT standalone utility, you can print

• DB2 VSAM data sets that contain table spaces or index spaces, including dictionary pages for compressed data

• Image copy data sets

• Sequential data sets that contain DB2 table spaces or index spaces

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.

DSN1SDMP

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.