This chapter is from the book
This chapter is from the book
This chapter is from the book
Utilities
DB2 utilities either execute within the subsystem (online) or standalone and run outside the subsystem (offline). The activities performed by the utilities—loading and reorganizing data, recovering data and indexes, rebuilding indexes, gathering statistics, quiescing data, and repairing data—are discussed in detail in Chapters 7 and 8.
Executing Utilities
The most common way to execute the DB2 utilities is to create JCL with the appropriate control cards. The utilities must be executed on the subsystem where the objects reside. (Chapter 9 provides more information on how to do so in a data sharing environment.) However, other options for utility execution are available, as discussed next.
DB2I
The DB2I has a panel that can be used to generated and run utilities. This option does not require a great deal of JCL knowledge, and the jobs can be saved for future execution and editing. gives an example of the DB2I panel for DB2 utilities.
Example 2-8. DB2 utilities panel
DB2 UTILITIES SSID: DSN
===>
Select from the following:
1 FUNCTION ===> EDITJCL (SUBMIT job, EDITJCL, DISPLAY, TERMINATE)
2 JOB ID ===> TEMP (A unique job identifier string)
3 UTILITY ===> (CHECK DATA, CHECK INDEX, CHECK LOB,
COPY, DIAGNOSE, LOAD, MERGE, MODIFY,
QUIESCE, REBUILD, RECOVER, REORG INDEX,
REORG LOB, REORG TABLESPACE, REPORT,
REPAIR, RUNSTATS, STOSPACE, UNLOAD)
4 STATEMENT DATA SET ===> UTIL
Specify restart or preview option, otherwise enter NO.
5 RESTART ===> NO (NO, CURRENT, PHASE or PREVIEW)
6 LISTDEF? (YES|NO) ===> TEMPLATE? (YES|NO) ===>
* The data set names panel will be displayed when required by a utility.
PRESS: ENTER to process END to exit HELP for more information
DSNU Command
A DB2 online utility can be executed by invoking the DSNU CLIST command under TSO. The CLIST command generates the JCL data set required to execute the DSNUPROC procedure and execute online utilities as batch jobs. When you use the CLIST command, you need not be concerned about details of the JCL data set.
The CLIST command will create a job to perform one utility operation. The command can be issued for each utility operation necessary; then the issuer can edit and merge the outputs into one job or step.
Control Center
Utility execution is also supported in the DB2 Control Center, which also supports utility wildcarding: the ability to execute utilities against a list of objects matching a specified pattern of matching characters. A utility procedure could be created that would permit running, with one command, a mixture of several utilities against several objects, making maintenance much easier for the DBA. The Control Center also supports restarting utilities from the last committed phase or the last committed point. This support is available only for utilities started in the Control Center. The restart is accessible through the Display Utility dialog.
Support is also available for utility IDs. The Tools Settings notebook has an option to create a utility ID template using a variety of variables, such as USERID and UTILNAME. Before execution of the utility, the utility ID can be edited to make it more meaningful. The execution of utilities from the Control Center requires the DSNUTILS stored procedure described below.
DSNUTILS and DSNUTILU
DSNUTILS is a DB2-supplied stored procedure for executing utilities from a local or remote application via an SQL CALL statement. The client application calls in DSNUTILS with appropriate parameters. DSNUTILS then analyzes them to create a SYSIN stream and allocate all necessary data sets. After the data sets are allocated, DSNUTILS calls DSNUTILB, which then executes the appropriate utility. The utility statements are then processed, and DSNUTILS retrieves the data (execution results) in the SYSPRINT file, puts the data in the SYSIBM.SYSPRINT temporary table, and then opens a cursor on the table and passes control back to the client application. The client application then fetches all rows from the result set. Figure 2-9 shows this flow.
)
Figure 2-9 Execution of DSNUTILS
DB2 for z/OS Version 8 also includes the new DSNUTILU stored procedure which provides the same functions as DSNUTILS, but allows the control cards to be specified in Unicode (UTF-8).
Chapter 13 has more information on stored procedures.
Utility Templates
Some DB2 utilities produce data sets as a by-product or as an end result of utility execution. These data sets are referenced on utility control statements by a set of data definition (DD) name keywords and are specified in detail on the corresponding JCL DD cards.
These DD cards must be coded for each utility, as required, and maintained over time as the structure of data changes. Database administrators establish data set policies, refer to them on utility control statements, and let DB2 utilities administer those policies at execution time. Many DB2 utilities will accept a template construct instead of DD cards to dynamically allocate utility data sets.
Templates contain
-
The data set naming convention
-
DFSMS parameters
-
DASD or TAPE allocation parameters
A TEMPLATE can be specified in the SYSIN data set, preceding the utility control statement that references it, or in one or more TEMPLATE data sets. The TEMPLATE data set DD name is specified on the OPTIONS utility control statement by TEMPLATEDD(ddname) and applies to all subsequent utility control statements until the end of input or until DB2 encounters a new OPTIONS TEMPLATEDD(ddname) specification. The default TEMPLATE data set DD name is SYSTEMPL.
TEMPLATE data sets may contain only TEMPLATE utility control statements. Any TEMPLATE defined within SYSIN overrides another TEMPLATE definition of the same name found in a TEMPLATE data set.
With this functionality, database administrators can standardize data set allocation and the utility control statements that refer to those data sets. This functionality reduces the need to customize and alter utility job streams.
Templates cannot be used with the REPAIR utility.
The TEMPLATE specification can be used for both DASD and TAPE data set allocation, including support for data set stacking on tape and Generation Data Group (GDG) base definition. TEMPLATE syntax also allows user-specified DASD SPACE parameters. If the SPACE keyword is not specified, the size of the data set will be estimated on the basis of formulas that vary by utility and by data set.
The TEMPLATE statement required for a COPY example might look something like this:
TEMPLATE TMP1 DSNAME(DB2.&TS..D&JDATE..COPY&ICTYPE.&LOCREM.&PRIBAK.) VOLUMES(Vol1,Vol2,Vol3) TEMPLATE TMP2 DSNAME(DB2.&TS..D&JDATE..COPY&ICTYPE.&LOCREM.&PRIBAK.) VOLUMES(Vol4,Vol5,Vol6) LISTDEF PAYROLL INCLUDE TABLESPACE CERTTS.* INCLUDE INDEXSPACE CERTTS.*IX EXCLUDE TABLESPACE CERTTS.TEMP* EXCLUDE INDEXSPACE CERTTS.TMPIX* COPY LIST PAYROLL ...COPYDDN(TMP1,TMP1)RECOVERYDDN(TMP2,TMP2)
Database administrators can check their utility control statements without execution, using the PREVIEW function. In PREVIEW mode, DB2 expands all TEMPLATE data set names appearing in the SYSIN DD, as well as any from the TEMPLATE DD that are referenced on a utility control statement. DB2 then prints the information to the SYSPRINT data set to halt execution. You can specify PREVIEW either as a JCL PARM or on the OPTIONS PREVIEW utility control statement.
For more information on templates and the DB2 utilities, refer to the IBM DB2 UDB for z/OS Version 8 Utility Guide and Reference.
Displaying Utilities
Utilities can be displayed in order to see important runtime information about the jobs. This includes jobs running in a data sharing group. The output has information about the
-
Type of utility
-
How much of the processing the utility has completed
-
Status of the utility
-
Member on which the utility is executing
-
Phase the utility is in