Part VI: DB2 PERFORMANCE UTILITIES AND COMMANDS

< Day Day Up >

Part VI: DB2 PERFORMANCE UTILITIES AND COMMANDS

CHAPTER 30 An Introduction to DB2 Utilities

CHAPTER 31 Data Consistency Utilities

CHAPTER 32 Backup and Recovery Utilities

CHAPTER 33 Data Organization Utilities

CHAPTER 34 Catalog Manipulation Utilities

CHAPTER 35 Stand-alone Utilities and Sample Programs

CHAPTER 36 DB2 Commands

CHAPTER 37 DB2 Utility and Command Guidelines

CHAPTER 38 DB2 Contingency Planning

< Day Day Up >

Chapter 30. An Introduction to DB2 Utilities

IN THIS CHAPTER

Generating Utility JCL
Monitoring DB2 Utilities
Version 7 and the State of IBM DB2 Utilities
Using LISTDEF and TEMPLATE
Issuing SQL Statements in DB2 Utilities

DB2 utility programs are divided into four broad categories:

Online utilities
Offline utilities
Service aids
Sample programs

Each of these categories is defined in Part VI, "DB2 Performance Utilities and Commands." A complete description of every utility that makes up each category is also provided. Sample JCL listings are provided for each utility. The job names, data set names , space allocations , and volumes used in the JCL are only examples. The database and tablespace names are from the DB2 sample tables used throughout this book. These names should be changed to reflect the needs of your application.

The online utilities are referred to as online because they execute under the control of DB2. They are run using the DSNUTILB program, which is supplied with DB2. DSNUTILB uses the Call Attach Facility (CAF) to run as an independent batch program.

Online utilities operate using control card input. DSNUTILB reads the control card input and then executes the proper utility based on the input. The first word in the control card is the name of the utility to be processed , followed by the other parameters required for the utility.

< Day Day Up >

Generating Utility JCL

In this chapter, all the sample JCL for the online utilities use DSNUPROC , a generic utility procedure supplied with DB2.

Recall from Chapter 18, "The Doors to DB2," that online DB2 utilities can be controlled by DB2I option 8. The DB2I utility panels are shown in Figures 30.1 and 30.2. JCL to execute DB2 utilities can be generated by these DB2I panels.

Figure 30.1. DB2I utility JCL generation panel 1.

Figure 30.2. DB2I utility JCL generation panel 2.

The first panel, shown in Figure 30.1, is set to generate JCL for the STOSPACE utility. The second panel, shown in Figure 30.2, provides additional information used by certain DB2 utilities. If the first panel were set to generate JCL for the COPY , LOAD , or REORG utilities, the second panel would prompt the user to enter data set names required for those utilities.

The DB2I utility JCL generation panels provide four basic options:

`SUBMIT`	JCL is automatically built to execute the requested DB2 utility, and it is submitted in batch for processing.
`EDITJCL`	JCL is automatically built and displayed for the user. The user can edit the JCL, if desired, and then submit the JCL.
`DISPLAY`	The status of a utility identified by JOB ID is displayed online.
`TERMINATE`	A utility identified by JOB ID is terminated . This cancels a running utility or removes an inactive utility from the DB2 subsystem, thereby disabling future restartability for the utility.

The DISPLAY and TERMINATE options are merely menu-driven implementations of the DB2 -DISPLAY and - TERM commands. The SUBMIT and EDITJCL options provide automated DB2 utility JCL generation and submission. The DB2I utility program provides only rudimentary DB2 utility JCL, however. It works as follows :

The user specifies either SUBMIT or EDITJCL and a JOB ID that uniquely identifies a utility.
The user specifies one of the supported utilities (see Figure 30.1).
The user then specifies on the panel the data set containing the utility control cards to be used. The data set must be pre-allocated.
As directed by the panel, the user supplies additional data set names, depending on the selected utility.
JCL is generated for the requested utility.

The DB2I utility generator displays the output messages shown in Figure 30.3 when Enter is pressed and the request is processed .

Figure 30.3. DB2I JCL generation output messages.

The JCL generated by DB2I for the STOSPACE utility is shown in Figure 30.4. Generating JCL for a utility each time it is required, however, can be cumbersome. Many users create a partitioned data set containing sample utility JCL that they can modify as needed. The examples in Part VI can be used as templates for the creation of DB2 utility JCL for use in your shop.

Figure 30.4. Generated JCL for the `STOSPACE` utility.

Each online utility is associated with a utility identifier, or UID, that is passed to DSNUTILB as a parameter to uniquely identify the utility to DB2. Two utilities with the same UID cannot execute concurrently.

The DSNUPROC procedure requires the specification of override parameters to function properly. These parameters should be coded as follows:

`LIB`	The DB2 link library assigned to your DB2 system. This can be obtained from the database administrator or the system programmer responsible for DB2.
`SYSTEM`	The DB2 system containing the objects on which the utility will be run.
`UID`	Identifies the utility to the DB2 system. If this value is blank, the `UID` defaults to the job name . This enables an analyst or DBA to quickly identify the job associated with a utility. Also, because two identically named MVS jobs cannot run concurrently, two utilities with the same `UID` cannot run concurrently. This minimizes the possibility of incorrectly restarting or rerunning an abending job.
`UTPROC`	This value initially should be blank (that is, `UTPROC="` ). This parameter is assigned a value only during restart. A value of `'RESTART(PHASE)'` restarts the utility at the beginning of the last executed phase. A value of `'RESTART'` restarts the utility at the last or current commit point. The type of restart, `PHASE` or `COMMIT` , must be determined by analyzing the type of utility and the abend.