Utility Guidelines

The following topics provide useful guidance for the development and usage of DB2 utilities.

DB2 Online Utility Return Codes

When an online utility runs, a return code is provided indicating the status of the utility execution. If the utility runs to normal completion, the return code is set to .

A return code of 4 indicates that the utility completed running, but with warnings. Review the utility output to determine whether some type of reprocessing is required. A warning often indicates a condition that requires no additional consideration.

A return code of 8 means that the utility did not complete successfully. Determine the cause and execute the utility again.

A return code of 12 is an authorization error, which means that the user is not authorized to execute the utility. Either grant the user the proper authority or have an authorized user execute the utility.

DB2 Utility Work Data Sets

Many DB2 online utilities require the allocation of work data sets to complete the task at hand. These work data sets were presented in the first chapters in Part VI. Because a central reference often is handy, the required and optional work data sets for the DB2 online utilities are presented together in Table 37.1. The data sets used by DB2 utilities are listed along the top of the table. The utilities that use these data sets are listed along the left side of the table. Consult the legend to determine the necessity of coding these data sets in the JCL.

Table 37.1. Required Utility Data Sets

	SORTOUT	SORTWKxx	SYSCOPY	SYSDISC	SYSERR	SYSMAP	SYSREC	SYSUT1	UTPRINT	SYSIN	SYSPRINT	DSSPRINT
CHECK DATA	R	R			R				O	R	R
CHECK INDEX	R	R							O	R	R
CHECK LOB	R	R							O	R	R
COPY			R							R	R	O
LOAD	X/C/K	R	B	O	O	O	R	R	R	R	R
MERGECOPY			R					O		R	R
QUIESCE										R	R
REBUILD INDEX		R						O	R	R	R
RECOVER										R	R
REORG INDEX								R		R	R
REORG TS	X	R	B	D			R	T	R	R	R
B = Required if the COPYDDN and RECOVERYDDN options are used to make image copies during utility processing C = Required if referential constraints exist and the ENFORCE CONSTRAINTS option is used D = Required if the DISCARDDN option is specified to purge data during a REORG K = Required if the SORTKEYS option is specified with no value or a value of zero O = Optional (based on utility parameters) R = Required T = Required for tables with indexes unless the SORTKEYS option is specified X = Required if indexes exist

The COPY utility also requires a filter data set containing a list of VSAM data set names when COPY is run with the CONCURRENT and FILTERDDN options.

You also can specify a SYSPUNCH data set for the REORG utility to generate LOAD statement input cards. Additionally, REORG requires a data set to hold the unloaded data unless NOSYSREC or SHRLEVEL CHANGE is specified.

Of course, as of DB2 Version 7, you can use dynamic data set allocation and allow DB2 to allocate the data sets that are required for each utility. Doing so is simple and easier to manage than explicitly coding each data set allocation for each utility. For more details on dynamic data set allocation, refer to Chapter 30, "An Introduction to DB2 Utilities."

DB2 Utility Catalog Contention

DB2 utilities read and update DB2 Catalog and DB2 Directory tables. This can cause contention when multiple utilities are run concurrently.

Table 37.2 lists the DB2 Catalog tables that are either updated or read by the online DB2 utilities. In addition, DB2 utilities update the SYSIBM.SYSUTILX DB2 Directory table.

DB2 utilities rely on claim and drain processing instead of transaction locks to reduce contention and increase availability. SQL statements take claims on resources and utilities take drains.

Table 37.2. Utility Contention

Utility	Updates	Reads
CHECK	SYSIBM.SYSCOPY	SYSIBM.SYSCHECKDEP SYSIBM.SYSCHECKS SYSIBM.SYSCOLUMNS SYSIBM.SYSINDEXES SYSIBM.SYSINDEXPART SYSIBM.SYSTABLES SYSIBM.SYSTABLEPART SYSIBM.SYSTABLESPACE
COPY	SYSIBM.SYSCOPY	SYSIBM.SYSCOLUMNS SYSIBM.SYSINDEXES SYSIBM.SYSINDEXPART SYSIBM.SYSTABLES SYSIBM.SYSTABLEPAR SYSIBM.SYSTABLESPACE
LOAD	SYSIBM.SYSCOPY	SYSIBM.SYSCHECKDEP SYSIBM.SYSCHECKS SYSIBM.SYSCOLUMNS SYSIBM.SYSINDEXES SYSIBM.SYSTABLEPART SYSIBM.SYSTABLES SYSIBM.SYSINDEXPART SYSIBM.SYSTABLESPACE
MERGECOPY	SYSIBM.SYSCOPY	SYSIBM.SYSCOPY
MODIFY RECOVERY	SYSIBM.SYSCOPY	SYSIBM.SYSCOPY
MODIFY STATISTICS	SYSIBM.SYSCOLUMNS SYSIBM.SYSCOLDIST SYSIBM.SYSINDEXES SYSIBM.SYSTABLES SYSIBM.SYSINDEXPART SYSIBM.SYSLOBSTATS SYSIBM.SYSTABSTATS SYSIBM.SYSTABLEPART SYSIBM.SYSTABLESPACE
QUIESCE	SYSIBM.SYSCOPY
REBUILD	SYSIBM.SYSCOPY	SYSIBM.SYSCOLUMNS SYSIBM.SYSINDEXES SYSIBM.SYSINDEXPART SYSIBM.SYSTABLES SYSIBM.SYSTABLEPART SYSIBM.SYSTABLESPACE
RECOVER	SYSIBM.SYSCOPY	SYSIBM.SYSCOLUMNS SYSIBM.SYSINDEXES SYSIBM.SYSINDEXPART SYSIBM.SYSTABLES SYSIBM.SYSTABLEPART SYSIBM.SYSTABLESPACE
REORG	SYSIBM.SYSCOPY	SYSIBM.SYSCOLUMNS SYSIBM.SYSINDEXES SYSIBM.SYSINDEXPART SYSIBM.SYSTABLES SYSIBM.SYSTABLEPART SYSIBM.SYSTABLESPACE
REPAIR SET
	NOCHCKPEND	SYSIBM.SYSTABLES SYSIBM.SYSTABLEPART
	NORCVRPEND	DB2 Directory
	NOCOPYPEND	DB2 Directory
RUNSTATS	SYSIBM.SYSCOLDIST SYSIBM.SYSCOLDIST_HIST SYSIBM.SYSCOLDISTSTATS SYSIBM.SYSCOLSTATS SYSIBM.SYSCOLUMNS SYSIBM.SYSCOLUMNS_HIST SYSIBM.SYSINDEXES SYSIBM.SYSINDEXES_HIST SYSIBM.SYSINDEXPART SYSIBM.SYSINDEXPART_HIST SYSIBM.SYSINDEXSTATS SYSIBM.SYSINDEXSTATS_HIST SYSIBM.SYSLOBSTATS SYSIBM.SYSLOBSTATS_HIST SYSIBM.SYSTABLES SYSIBM.SYSTABLEPART SYSIBM.SYSTABLEPART_HIST SYSIBM.SYSTABLESPACE SYSIBM.SYSTABLESPACE_HIST SYSIBM.SYSTABSTATS SYSIBM.SYSTABSTATS_HIST	Table spaces and indexes being analyzed
STOSPACE	SYSIBM.SYSINDEXES SYSIBM.SYSTABLESPACE SYSIBM.SYSSTOGROUP SYSIBM.SYSTABLEPART SYSIBM.SYSINDEXPART
UNLOAD		SYSIBM.SYSTABLES SYSIBM.SYSCOLUMNS Table being unloaded

Partition Level Operation

DB2 online utilities can operate at the table space partition level. The following utilities can be issued for a single partition or for all the partitions of a table space:

• CHECK DATA , CHECK INDEX , and REPAIR data consistency utilities

• COPY , MERGECOPY , QUIESCE , RECOVER , REBUILD INDEX , and REPORT backup and recovery utilities

• LOAD and REORG data organization utilities

• MODIFY and RUNSTATS catalog manipulation utility

Coding Utility Control Cards

All DB2 utility control card input must be contained in 80-character record images. The utility statements must be confined to columns 1 through 72. All input in columns 73 through 80 is ignored by DB2.

Automatically Generate Utility Control Cards

Consider using DB2 Catalog queries to generate utility control card input. By creating standard queries for each utility, you improve the accuracy of the utility input syntax. For example, the following query automatically generates input to the RECOVER utility to invoke full table space recovery for all table spaces in a given database:

 SELECT   'RECOVER TABLESPACE '    DBNAME            '.'    NAME  ' DSNUM ALL' FROM     SYSIBM.SYSTABLESPACE WHERE    DBNAME = '''DSN8D81A'; 

This query generates RECOVER TABLESPACE control cards for every table space in the sample database. You can formulate queries to automatically create control card input for most of the online utilities.

Use LISTDEF and TEMPLATE

In general, the maintenance and execution of DB2 utilities can be greatly simplified by using the LISTDEF and TEMPLATE control statements.

LISTDEF is used to create a database object list such that a DB2 utility can execute against multiple database objects. Wildcards can be used to automatically include objects that conform to specific naming conventions in the list. After a list is created, the utility is run against the list. Using LISTDEF and proper naming conventions, you can create utility jobs that run against all table spaces (for example) in a particular application. When new table spaces are added, if they conform to the naming convention, they will automatically be added to the list ”and thereby are automatically added to the utility jobs using that list.

The TEMPLATE statement supports dynamic data set allocation. It provides DB2 utilities with the basic allocation information necessary to automatically generate and allocate valid data sets that are required for use as DB2 utilities are executed. Used in conjunction with LISTDEF , DB2 utility jobs can be set up to operate on multiple database objects with a single run.

Specify the BUFNO JCL Parameter

Various guidelines in Part VI recommend specific BUFNO JCL parameter settings for different utility work data sets. Each installation defines a default number of buffers adequate for the data sets used by most batch jobs. The DB2 utilities, however, can benefit by increasing the work data set buffers. Therefore, if sufficient memory is available to increase the buffering of DB2 utility work data sets, always do so. As of DB2 V4, the default for BUFNO is 20.

Allocate Sufficient Sort Work Space for DFSORT

The CHECK INDEX , LOAD , RECOVER INDEX , and REORG utilities require an external sort routine. DB2 uses an IBM-supplied sort utility named DFSORT . You can use the SORTDEVT and SORTNUM parameters of these utilities to allow the system to allocate the sort work area dynamically. This way, the sort work specification never needs to be adjusted or sized ”the system manages the required size .

CAUTION

For very large table spaces requiring a large amount of sort work space, consider explicit allocation of sort work data sets because the system might not be able to allocate large amounts of space during the utility execution.

The SORTDEVT parameter is used to specify the device type for temporary data sets to be dynamically allocated by DFSORT . The SORTNUM parameter specifies the number of temporary data sets to be dynamically allocated by the sort program. If you use SORTDEVT and omit SORTNUM , DFSORT will determine how many data sets to allocate on its own.

NOTE

No sort work space is required when reorganizing type 2 indexes. No sort work space is required when loading a table with no indexes or a single index, when the data to be loaded is in order by the index key.

The SORTWK xx DD statement defines the characteristics and location of the intermediate storage data sets used by DFSORT . Multiple data sets can be allocated for the temporary sort work space required by DFSORT . Specify each sort work data set to a different SORTWK xx DD statement. The xx is a two-digit indicator ranging from 00 to 99. In general, begin with 00 and work your way up. No more than 32 SORTWK xx data sets will be used by DFSORT .

All the data sets allocated to the SORTWK xx DD statements must be allocated on the same media type. Although DFSORT permits the allocation of work data sets to a tape unit, avoid doing this for DB2 utilities because it causes severe performance degradation. Additionally, the SORTWK xx DD statements must be allocated on the same type of unit (for example, one SORTWK xx data set cannot be allocated to a 3390 device if the others are allocated to 3380 devices).

Specify the SPACE allocation for the SORTWK xx data sets in cylinder increments . If you don't, DFSORT will reallocate the data sets in cylinder increments anyway.

For performance, specifying one or two large SORTWK xx data sets is preferable to specifying multiple smaller data sets. For more information on DFSORT , consult the IBM DFSORT Application Programming Guide (SC33-4035).

When Loading or Reorganizing, Specify LOG NO

To reduce the overhead associated with the LOAD and REORG job, use LOG NO . DB2 logs every modification to DB2 data, except when the LOAD and REORG utilities run with the LOG NO option. When you use LOG NO , however, an image copy must be taken after the successful completion of the LOAD or REORG job.

When Loading or Reorganizing, Perform Inline Utilities

To eliminate the need to run subsequent RUNSTATS and COPY after a LOAD or REORG , use DB2's capability to generate statistics and make image copies as a part of the LOAD or REORG utility.

Use the STATISTICS clause to indicate that inline statistics are to be generated.

Specify COPYDDN data sets (and RECOVERYDDN data sets if off-site copies are desired) to indicate that inline image copies are to be made.

Back Up Data Using the COPY Utility

Use the COPY utility to back up data rather than DSN1COPY . DSN1COPY operates "behind DB2's back." If you always use the COPY utility, DB2 will have an accurate record of all backup data sets.

REBUILD INDEX Versus CREATE INDEX

For very large existing tables, it is quicker to use the REBUILD INDEX utility to build an index than to simply issue a CREATE INDEX statement. REBUILD INDEX is more efficient because it uses an external sort. The REBUILD INDEX utility is designed to rebuild indexes, not initially build them as part of a CREATE statement.

The CREATE INDEX DDL provides the option to defer index population by specifying DEFER YES . This causes an index to be built as an empty shell. After the index is created, it will be put into a rebuild pending status. The REBUILD INDEX utility can then be executed to populate the index. This process is usually much more efficient for indexes on very large tables.