DB2 utilities

IBM® DB2® utilities are tools that help maintain data in your DB2 for z/OS® databases. Generally, you use DB2 utilities to perform large-scale operations across one or more table spaces.

For example, you can use utilities to do the following maintenance tasks:

  • Make copies of your data, which can later be used as a basis for recovery.
  • Recover data, either to the current time or some point in the past.
  • Load thousands or even many millions of rows into your database.
  • Reorganize data so that space is used in the most efficient way possible.

Various independent software vendors (ISVs) also provide utilities and tools that you can use to maintain your DB2 databases. However, this topic describes only those DB2 utilities that are provided by IBM.

Where do I get DB2 utilities?

Some utilities are provided with DB2 for z/OS. Other utilities are sold as a separate product called DB2 Utilities Suite for z/OS. For a list of utilities for each of these categories, see DB2 utilities packaging. Your shop might also use utilities that are provided by an ISV. Ask your system programmer which utilities are installed in your shop.

Types of utilities

There are two types of utilities:

  • Online utilities, which require DB2 to be up and running.
  • Stand-alone utilities, which run independently of DB2.

See Types of DB2 utilities for more information about online and stand-alone utilities.

Commonly used utilities

The following tables list some of the commonly used utilities and a brief description of what they do. Use these tables to help you determine the utilities that you might use in your job.

The following table lists the utilities that you can use to load and unload data and to check data structures.

Table 1. Utilities for checking, loading, and unloading data
Utility What the utility does
CHECK DATA Checks table spaces for violations of referential and table check constraints. Also, checks for consistency between a base table space and the corresponding LOB or XML table spaces.
CHECK INDEX Checks whether indexes are consistent with the data that they index.
CHECK LOB Identifies any structural defects in a LOB table space and any invalid LOB values.
EXEC SQL Declares cursors and executes dynamic SQL statements. This utility is typically used as part of the DB2 cross-loader function. The cross-loader function transfers data from one location to another location or from one table to another table at the same location with a single LOAD job.
LOAD Loads data into one or more tables of a table space.
UNLOAD Copies data from table spaces or image copies in a format that can be used by the LOAD utility. The data is not deleted from the source object.

The following table lists the utilities that are used to maintain the data for optimal query performance and storage.

Table 2. Utilities for maintaining data
Utility What the utility does
REORG INDEX Reorganizes an index space to improve access performance and reclaim fragmented space.
REORG TABLESPACE Reorganizes a table space or partitions to improve access performance and to reclaim fragmented space. REORG is also used to materialize pending metadata changes.
RUNSTATS Gathers statistics for table spaces, indexes, and partitions and records these statistics in the DB2 catalog. DB2 uses these statistics to select the most efficient access paths for queries.

The following table lists utilities that are used for recovery and planning for recovery.

Table 3. Utilities for recovery
Utility What the utility does
COPY Copies table spaces, index spaces, partitions, and data sets to be used for recovery. These copies are called image copies. Certain other utilities can also take copies while they run. For example, LOAD can take a copy while it loads data. These copies are called inline image copies.
COPYTOCOPY Copies an image copy or inline image copy.
MERGECOPY Merges image copies or inline image copies.
QUIESCE Establishes a quiesce point for table spaces, partitions, and table space sets. A quiesce point is a point at which data is consistent across the specified objects and can later be used as a recovery point. (A table space set is a set of related objects that should logically be recovered together, for example a base table space and its LOB table spaces.)
RECOVER Recovers data to the current state or to a previous point in time by restoring a copy and then applying log records.
REPORT Provides information about table spaces, tables, and indexes that is useful for recovery. For example, REPORT provides the names of related table spaces that should logically be recovered to the same point in time.

The preceding tables list only a subset of the utilities that IBM provides. IBM also provides more advanced utilities, such as those that are used to backup or restore the entire system or repair damaged data or structures. For a complete list of utilities, see DB2 online utilities and DB2 stand-alone utilities.

How do I run a utility?

The process for running a utility depends on whether you are running an online utility or a stand-alone utility.

Online utilities
Several methods are available for running an online utility. Most likely, you will create a job control language (JCL) job to run a utility. You can also use the DSNUTILU stored procedure stored procedure to run a utility from your application program. Another alternative is to use the DB2 Utilities panel (in DB2I) or the DSNU CLIST command to generate the JCL.

Prior to running an online utility, you should be proficient with the following concepts:

JCL
If you will use JCL to run utilities and need to learn more about it, use the following resources:
Utility control statements
After you master JCL, you should understand how to write a utility control statement. A utility control statement specifies the utility name, the target object or objects, and any other options for the utility. For example, the following utility control statement is for the CHECK DATA utility:
   CHECK DATA TABLESPACE DBIQUQ01.TPIQUQ01 SCOPE ALL 
         AUXERROR INVALIDATE

The options that are available for each utility are listed in the syntax diagram in the documentation for that utility. For example, the syntax diagram for the CHECK DATA utility is in Syntax and options of the CHECK DATA control statement. If you are not familiar with syntax diagrams, read How to read syntax diagrams.

For detailed rules for writing utility control statements, such as how you continue statements across lines and which encoding schemes are acceptable, read Utility control statements.

Where you create the utility statement depends on the procedure that you use to invoke the utility. For example, if you JCL, the utility statement is specified in the SYSIN DD statement. If you use the DSNUTILU procedure, the utility statement gets passed as a parameter. If you use the DB2 Utilities panel or the DSNU CLIST command, you can put this utility statement in a data set or member. See Invoking DB2 online utilities for detailed instructions.

Data sets
Because many utilities require input data sets or work data sets or allocate output data sets, you should understand how to allocate and use data sets.

Which data sets are required depends on the utility and the options that you specify. For example, suppose you request that the COPY utility take a primary and backup copy at the local site and a primary and backup copy at the recovery site. In this case, COPY allocates four data sets, one for each image copy, and you must tell COPY the parameters to use for these output data sets. For details about which data sets are required for each utility, see the documentation for the utility. For example, the data sets that are required by COPY are described in Data sets that COPY uses uses.

DD statements, DD names, and DD statement parameters
Templates
DB2 online utilities provide a TEMPLATE control statement, which you can use instead of JCL DD statements to define data sets. After a template is defined, its name is passed to a utility by using the same keywords as a DD name. For example, in the following statement, MYCOPY can refer to either a JCL DD name or a template name: 
COPY TABLESPACE A.B COPYDDN MYCOPY
See LISTDEF, OPTIONS, and TEMPLATE utilities for more information about templates.

For complete instructions for running online utilities, see Invoking DB2 online utilities.

Stand-alone utilities
To run a stand-alone utility, you must use JCL. You specify the utility name on the EXEC statement. For certain utilities, you also specify the utility options on the EXEC statement. For other utilities, you specify the options in a utility control statement in an in-stream data set.
Important: Most stand-alone utilities are intended for advanced DB2 users. Be sure that you understand the effects of running a stand-alone utility before you run one.

For the complete instructions for running a stand-alone utility, see Invoking stand-alone utilities.

Required authorizations
For both online and stand-alone utilities, you must have the correct authorization to run them. The documentation for each utility describes the authorization that is required for that utility. They are not all the same. For more information about general authorization for utilities, see Privileges and authorization IDs for DB2 utilities.

LISTDEF, OPTIONS, and TEMPLATE utilities

The LISTDEF, OPTIONS, and TEMPLATE utilities are different from the other utilities in that they do not perform any database administration function. Instead, they provide facilities to the other utilities.

  • The LISTDEF utility provides a facility for producing lists of objects on which to operate.
  • The OPTIONS utility provides a facility for globally setting options that apply to the entire job step.
  • The TEMPLATE utility provides a facility for allocating data sets.
LISTDEF
You can use the LISTDEF utility to create a reusable list of objects. For example, suppose that you have a group of objects that you want to regularly copy, quiesce, or perform any other utility task on. You can use the LISTDEF utility to create a list of those objects. You specify the object names and then give the list a name. For example, the following statement defines a list called MYLIST, which includes three objects:
LISTDEF MYLIST INCLUDE TABLESPACE A            
               INCLUDE TABLESPACE B            
               INCLUDE TABLESPACES TABLE C

Your lists will likely include many more objects. If the objects have a similar naming pattern, you can use wildcard characters for portions of the object names.

After you define the list, you can then reference that list in subsequent utility control statements. For example, the following COPY statement requests that all of the objects in your list (MYLIST) be copied:

COPY LIST MYLIST …

Using lists is helpful when you need to run utilities on many objects. Instead of looking up all of the object names in the catalog and writing each object name in a control statement, you can define a list. You can then reference that list in any subsequent utility control statements.

You can either include the LISTDEF statement in the SYSIN DD statement directly before the utility control statements that reference it, or you can create libraries of LISTDEF statements. LISTDEF libraries are just data sets that contain LISTDEF statements. The advantage of creating libraries is that these lists are then stored and available for many different utility jobs.

To learn more about the LISTDEF utility, see LISTDEF.

OPTIONS
You can use the OPTIONS utility to specify utility processing options for the job step. Consider the following examples:
  • You can use the OPTIONS utility to specify a LISTDEF library or TEMPLATE library that any subsequent utilities are to use.
  • You can specify OPTIONS PREVIEW, which causes the utility to run in preview mode. Preview mode is useful to view what the utility job will do before it is actually done. For example, if you specify OPTIONS PREVIEW and then a LISTDEF statement, you can see the expanded list of all objects that are included in the list. Such an expanded list is useful if your list contains wildcard characters, and you want to see which objects will be included in the list.
  • You can also use the OPTIONS utility to specify what the utility will do if an error occurs for an item in the list. You can specify whether the utility is to stop processing or skip the item in error and continue processing the next item.

To learn more about the OPTIONS utility, see OPTIONS.

TEMPLATE
You can use the TEMPLATE utility to allocate data sets without using JCL DD statements. The TEMPLATE utility defines data set templates. For each template, you specify the template name, the data set naming convention, and any allocation parameters that you want. Then, you can tell subsequent utilities to use that template when they allocate certain data sets.

One advantage of using this utility is that you do not have to specify data set allocation parameters. For example, TEMPLATE can determine the appropriate data set size for you.

Templates are also useful for lists of objects. For example, suppose that you have a long list of objects to copy. Instead of using a DD statement to specify a data set for each image copy, you can specify the template name where you otherwise would specify a DD name.

To learn more about the TEMPLATE utility, see TEMPLATE.

See Simplifying utilities with wildcarding and templates (Using the Utilities Suite) for more information about templates, wildcards, and options.

How do I monitor a utility job?

You can monitor the progress of one or more utility jobs by using the DISPLAY UTILITY command. For example, you can see what phase the utility is in and the object that it last processed.

If you need to stop an online utility job, use the TERM UTILITY command.

If a utility job stopped before it completed processing, you can restart it after you fix the error that caused the utility job to stop. You can restart a utility either from the last checkpoint or from the beginning of the phase in which it was currently processing.

For more information about monitoring, stopping, and restarting utilities, see Monitoring and controlling online utilities.

Tools

You can use tools that are provided by IBM or various ISVs to perform the tasks that are required to control and maintain your DB2 subsystem. For example, you can use the following IBM DB2 Tools for z/OS tools to manage utilities and perform utility tasks:

DB2 Automation Tool for z/OS
A tool that enables database administrators to focus on database optimization. This tool automates maintenance tasks. See DB2 Automation Tool for z/OS.
DB2 High Performance Unload for z/OS
A high-speed DB2 utility that unloads DB2 tables from either a table space or a backup of the database. See DB2 High Performance Unload for z/OS.
DB2 Cloning Tool for z/OS
A tool that quickly clones a DB2 subsystem or data sets, creating the equivalent of a production environment that you can use to test new features and functions. See DB2 Cloning Tool for z/OS.
DB2 Sort for z/OS
A tool that reduces elapsed time and CPU cost for utility sort processing. See DB2 Sort for z/OS.

Other learning resources

For more detailed training in DB2 utilities, use the following resources: