COPY The COPY online utility creates up to four image copies of any of the following objects:  table space,  table space partition,  data set of a linear table space, index space,  or index space partition. The two types of image copies are: 1. A full image copy, which is a copy of all pages in a table space, partition, data set, or index space. 2. An incremental image copy, which is a copy only of those data pages that have been modified since the last use of the COPY utility and system pages. The RECOVER utility uses these copies when recovering a table space or index space to the most recent time or to a previous time.  Copies can also be used by the MERGECOPY, RECOVER, COPYTOCOPY, and UNLOAD utilities. You can copy a list of objects in parallel to improve performance.  Specifying a list of objects along with the SHRLEVEL REFERENCE option creates a single recoverypoint for that list of objects.  Specifying the PARALLEL keyword allows you to copy a list of objects in parallel, rather than serially. To calculate the number of threads you need when you specify the PARALLEL keyword, use the formula (n * 2 + 1), where n is the number of objects that are to be processed in parallel, regardless of the total number of objects in the list.  If you do not use the PARALLEL keyword, n is one and COPY uses three threads for asingle-object COPY job.

RECOVER

The online RECOVER utility recovers data to the current state or to a previous point in time by restoring a copy and then applying log records. The largest unit of data recovery is the table space or index space; the smallest is the page.  You can recover a single object, system-level backups, or a list of objects. 

The RECOVER utility recovers an  entire table space,  index space,  a partition or data set,  pages within an error range,  or a single page. You can recover data from image copies of an object or from a system-level backup records that contain changes to the object. Point in time recovery with consistency automatically detects the uncommitted transactions running at the recover point in time and will roll back their changes on the recovered objects. So after recover, objects will be left in their transactionally consistent state.  COPYTOCOPY The COPYTOCOPY utility makes image copies from an image copy that was taken by the COPY utility, including inline copies that the REORG or LOAD utilities make. Starting with either the local primary or recovery-site primary copy, COPYTOCOPY can make up to three copies of one or more of the following types of copies: Local primary Local backup Recovery site primary Recovery site backup You cannot run COPYTOCOPY on concurrent copies. The RECOVER utility uses the copies when recovering a table space or index space to the most recent time or to a previous time. These copies can also be used by MERGECOPY, UNLOAD, and possibly a subsequent COPYTOCOPY execution. 

MERGECOPY The MERGECOPY online utility merges  image copies that the COPY utility produces,  image copies that the COPYTOCOPY utility produces,

 or inline copies that the LOAD or REORG utilities produce. The utility can merge several incremental copies of a table space to make oneincremental copy.  It can also merge incremental copies with a full image copy to make a new full image copy.  You cannot run MERGECOPY on concurrent copies. MERGECOPY operates on the image copy data sets of a table space, and not on the table space itself. 

UNLOAD The online UNLOAD utility unloads data from one or more source objects to oneor more BSAM sequential data sets in external formats.  The source can be DB2 table spaces or DB2 image copy data sets.  The source cannot be a concurrent copy. UNLOAD must be run on the system where the definitions of the table space andthe table exists. UNLOAD is an enhancement of the REORG UNLOAD EXTERNAL function.  With UNLOAD, you can unload rows from an entire table space or select specificpartitions or tables to unload.  You can also select columns by using the field  specification list.  If a table space is partitioned, you can unload all of the selected partitions into a single data set, or you can unload each partition in parallel into physically distinct data sets. The output records that the UNLOAD utility writes are compatible as input to theLOAD utility; as a result, you can reload the original table or different tables.

 LOAD Use LOAD to load one or more tables of a table space.  The LOAD utility loads records into the tables and builds or extends any indexes that are defined on them. If the table space already contains data, you can choose whether you want to addthe new data to the existing data or replace the existing data. The loaded data is processed by any edit or validation routine that is associated

with the table, and any field procedure that is associated with any column of the table.  The LOAD utility ignores and does not enforce informational referentialconstraints.

 

CHECK DATA The CHECK DATA utility checks table spaces for violations of referential and table check constraints, and reports information about violations that it detects.  CHECK DATA checks for consistency between a base table space and the corresponding LOB or XML table spaces. CHECK DATA does not check LOB table spaces. The utility does not check informational referential constraints. Run CHECK DATA after a conditional restart or a point-in-time recovery on all table spaces where parent and dependent tables might not be synchronized or where base tables and auxiliary tables might not be synchronized. Restriction: Do not run CHECK DATA on encrypted data. Because CHECK DATA does not decrypt the data, the utility might produce unpredictable results.  CHECK INDEX The CHECK INDEX online utility tests whether indexes are consistent with the data that they index, and issues warning messages when it finds an inconsistency. Run the CHECK INDEX utility after a conditional restart or a point-in-time recovery on all table spaces whose indexes might not be consistent with the data. Also run CHECK INDEX before running CHECK DATA, especially if you specify DELETE YES.  Running CHECK INDEX before CHECK DATA ensures that the indexes that CHECK DATA uses are valid. 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. Important: Inaccurate statistics for tables, table spaces, or indexes can result in asort failure during CHECK INDEX. Running CHECK INDEX when the index has a VARBINARYcolumn If you run CHECK INDEX against the index with the following characteristics, CHECK INDEX fails: The index was created on a VARBINARY column or a column with a distinct type that is based on a VARBINARY data type.

 The index column has the DESC attribute. To fix the problem, alter the column data type to BINARY, and then rebuild the index. 

CHECK LOB You can run the CHECK LOB online utility on a LOB table space to identify any structural defects in the LOB table space and any invalid LOB values. The CHECK LOB utility is useful in a variety of circumstances: Run the utility on a LOB table space that is in CHECK-pending (CHKP) status to identify structural defects. If none are found, the CHECK LOB utility turns the CHKP status off. Run the utility on a LOB table space that is in auxiliary-warning (AUXW) status to identify invalid LOBs. If none exist, the CHECK LOB utility turns AUXW status off. Run the utility after a conditional restart or a point-in-time recovery on all table spaces where LOB table spaces might not be synchronized. Run the utility before you run the CHECK DATA utility on a table space that contains at least one LOB column.  QUIESCE The online QUIESCE utility establishes a quiesce point for a table space, partition, table space set, or list of table spaces and table space sets. REORG TABLESPACE The online REORG TABLESPACE utility reorganizes a table space to improve access performance and to reclaim fragmented space.  In addition, the utility can reorganize a single partition or range of partitions of a partitioned table space. You can specify the degree of access to your data during reorganization, and you can collect inline statistics by using the STATISTICS keyword. If you specify REORG TABLESPACE UNLOAD EXTERNAL, the data is unloaded in a format that is acceptable to the LOAD utility of any DB2 subsystem.  You can also delete rows during the REORG job by specifying the DISCARD option. You can determine when to run REORG for non-LOB table spaces by using the OFFPOSLIMIT or INDREFLIMIT catalog query options. If you specify the REPORTONLY option, REORG produces a report that indicates whether a REORG is recommended without actually performing the REORG.  These options are not applicable and are disregarded if the target object is a directory table space.

 Run the REORG TABLESPACE utility on a LOB table space to help increase the effectiveness of prefetch. For a LOB table space, REORG TABLESPACE performs these actions: Removes imbedded free space Attempts to make LOB pages contiguous If you specify SHRLEVEL REFERENCE, a REORG of a LOB table space will make LOB pages continuous, remove imbedded free space, and reclaim physical space if applicable. Do not execute REORG on an object if another DB2 holds retained locks on the object or has long-running noncommitting applications that use the object.  You can use the DISPLAY GROUP command to determine whether a member’s status is failed. You can use the DISPLAY DATABASE command with the LOCKS option todetermine if locks are held.

 REORG INDEX The online REORG INDEX utility reorganizes an index space to improve access performance and reclaim fragmented space.  You can specify the degree of access to your data during reorganization, and you can collect inline statistics by using the STATISTICS keyword. You can determine when to run REORG INDEX by using the LEAFDISTLIMIT catalog query option.  If you specify the REPORTONLY option, REORG INDEX produces a report that indicates whether a REORG is recommended; in this case, a REORG is not performed. These options are not available for indexes on the directory. REBUILD INDEX The REBUILD INDEX utility reconstructs indexes or index partitions from the table that they reference. Restriction: REBUILD INDEX SHRLEVEL CHANGE should only be used to fix a broken or restricted index, or to build an index after DEFER.  You should not use the REBUILD INDEX SHRLEVEL CHANGE utility to move an index to different volumes; instead you should use the online REORG utility.  REBUILD INDEX SHRLEVEL CHANGE on a unique index will not allow the INSERT option, the DELETE option, or updates that affect the unique index.  MODIFY RECOVERY The MODIFY 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, and recycles DB2 version numbers for reuse.

 You can remove records that were written before a specific date, you can remove records of a specific age, or you can ensure that a specified number of records is retained. You can delete records for an entire table space, partition, or data set. You should run MODIFY regularly to remove outdated information from SYSIBM.SYSCOPY and SYSIBM.SYSLGRNX. These tables, and particularly SYSIBM.SYSLGRNX, can become very large and take up a considerable amount of space.  By deleting outdated information from these tables, you can help improve performance for processes that access data from these tables. The MODIFY RECOVERY utility automatically removes the SYSCOPY and SYSLGRNX recovery records that meet the specified criteria for all indexes on the table space that were defined with the COPY YES attribute. MODIFY STATISTICS The online MODIFY STATISTICS utility deletes unwanted statistics history recordsfrom the corresponding catalog tables.  You can remove statistics history records that were written  before a specific date,  or you can remove records of a specific age.  You can delete records for an entire table space, index space, or index. Run MODIFY STATISTICS regularly to clear outdated information from the statistics history catalog tables.  By deleting outdated information from those tables, you can improve performance for processes that access data from those tables. Restriction: MODIFY STATISTICS does not delete statistics history records for clone tables because statistics are not collected for these tables. REPAIR The online REPAIR utility repairs data.  The data can be your own data or data that you would not normally access, such as space map pages and index entries. You use REPAIR to replace invalid data with valid data.  Be extremely careful when using REPAIR.  Improper use can damage the data even further. You can use the REPAIR utility to: Test database definitions (DBDs)

 Repair DBDs Reset a pending status 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 Delete an entire LOB from a LOB table space Dump LOB pages Rebuild object descriptors (OBDs) for a LOB table space 

Manage version numbers

 REPORT The REPORT utility provides information about table spaces, tables, and indexes. in a referential structure, including LOB table spaces.  The REPORT utility also provides information about the LOB table spaces that are associated with a base table space.  Use REPORT RECOVERY to find information that is necessary for recovering a table space, index, or a table space and all of its indexes. 

  RUNSTATS The RUNSTATS utility gathers summary information about the characteristics of data in table spaces, indexes, and partitions.  DB2 records these statistics in the DB2 catalog and uses them to select access paths to data during the bind process.  You can use these statistics to evaluate the database design and determine when table spaces or indexes must be reorganized.  To obtain the updated statistics, you can query the catalog tables. The two formats for the RUNSTATS utility are  RUNSTATS TABLESPACE and RUNSTATS INDEX.  

RUNSTATS TABLESPACE gathers statistics on a table space and, optionally, on tables, indexes or columns;  RUNSTATS INDEX gathers statistics only on indexes. RUNSTATS does not collect statistics for clone tables or index spaces. RUNSTATS can collect statistics on any single column or set of columns. RUNSTATS collects the following two types of distribution statistics: FrequencyThe percentage of rows in the table that contain a value for a column orcombination of values for a set of columns. CardinalityThe number of distinct values in the column or set of columns. When you run RUNSTATS TABLESPACE, you can use the COLGROUP option tocollect frequency and cardinality statistics on any column group. You can alsocollect frequency and cardinality statistics on any single column.  When you run RUNSTATS INDEX, you can collect frequency statistics on the leading column ofan index and multi-column frequency and cardinality statistics on the leading concatenated columns of an index. When you run RUNSTATS TABLESPACE, you can use the HISTOGRAM option,with the COLGROUP option, to indicate that histogram statistics are to begathered for the specified group of columns. RUNSTATS TABLESPACE will ignoreHISTOGRAM when processing XML table spaces and indexes.  When you run RUNSTATS INDEX, histogram statistics can only be collected on the prefixcolumns with the same order. Key columns with a mixed order are not allowed for histogram statistics.  RUNSTATS INDEX will ignore HISTOGRAM when processing XML NODEID or VALUES indexes.   DIAGNOSE The DIAGNOSE online utility generates information that is useful in diagnosing problems.  Use this utility only under the direction of IBM Software Support.

 

EXEC SQL The EXEC SQL utility control statement declares cursors or executes dynamic SQL statements.  

You can use this utility as part of the DB2 cross-loader function of the LOAD utility. The cross-loader function enables you to use a single LOAD job to transfer data from one location to another location or from one table to another table at the same location.  You can use either a local server or any DRDA-compliant remote server as a data input source for populating your tables. Your input can even come from other sources besides DB2 for z/OS; you can use IBM Information Integrator Federation feature for access to data from sources as diverse as Oracle and Sybase, as well as the entire DB2 family of database servers. 

LISTDEF The LISTDEF utility enables you to group database objects into reusable lists.  You can then specify these lists in other utility control statements to indicate that the utility is to process all of the items in the list. You can use LISTDEF to standardize object lists and the utility control statements that refer to them. This standardization reduces the need to customize or alter utility job streams. If you do not use lists and you want to run a utility on multiple objects, you must run the utility multiple times or specify an itemized list of objects in the utility control statement. 

OPTIONS The online OPTIONS utility control statement specifies processing options that are applicable across many utility executions in a job step  By specifying various options, you can: Preview utility control statements Preview LISTDEF or TEMPLATE definitions Override library names for LISTDEF lists or TEMPLATE definitions Specify how to handle errors during list processing Alter the return code for warning messages Restore all default options You can repeat an OPTIONS control statement within the SYSIN DD statement.  If you repeat the control statement, it entirely replaces any prior OPTIONS control statement. STOSPACE 

The STOSPACE utility updates DB2 catalog columns that indicate how much space is allocated for storage groups and related table spaces and indexes. For user-defined spaces, STOSPACE does not record any statistics.

 

TEMPLATE The TEMPLATE utility control statement lets you allocate data sets, without using JCL DD statements, during the processing of a LISTDEF list. In its simplest form, the TEMPLATE control statement defines the data set naming convention.  You can also write TEMPLATE control statements so that they contain allocation parameters that define data set size, location, and attributes. Templates enable you to standardize data set names across the DB2 subsystem and to easily identify the data set type when you use variables in the data set name. The TEMPLATE control statement uses the z/OS DYNALLOC macro (SVC 99) to perform data set allocation. Therefore, the facility is constrained by the limitations of this macro and by the subset of DYNALLOC that is supported by TEMPLATE.  BACKUP SYSTEM The online BACKUP SYSTEM utility invokes z/OS DFSMShsm™ (Version 1 Release 7 or above) to copy the volumes on which the DB2 data and log information resides for either a DB2 subsystem or data sharing group. You can use BACKUP SYSTEM to copy all data for a single application (for example, when DB2 is the database server for a resource planning solution).  All data sets that you want to copy must be SMS-managed data sets.  You can subsequently run the RESTORE SYSTEM utility to recover the entire system.  In a data sharing environment, if any failed or abnormally quiesced members exist, the BACKUP SYSTEM request fails. The BACKUP SYSTEM utility uses copy pools. A copy pool is a defined set of storage groups that contain data that DFSMShsm can backup and recover collectively.  Each DB2 subsystem can have up to two copy pools, one for databases and one for logs.  BACKUP SYSTEM copies the volumes that are associated with these copy pools at the time of the copy. With the BACKUP SYSTEM utility you can manage the dumping of system-level backups (copy of the database, the log copy pools, or both) to tape.  

To use this functionality, you need to have z/OS DFSMShsm V1R8 or above. To use the DISPLAY UTILITY command for BACKUP SYSTEM on a data sharing group, you must issue the command from the member on which the BACKUP SYSTEM utility is invoked. Otherwise, the current utility information is not displayed.  RESTORE SYSTEM The RESTORE SYSTEM utility invokes z/OS DFSMShsm (Version 1 Release 7 or above) to recover a DB2 subsystem or a data sharing group to a previous point in time. To perform the recovery, the utility uses data that is copied by the BACKUP SYSTEM utility. All data sets that you want to recover must be SMS-managed data sets. The RESTORE SYSTEM utility can be run from any member in a data sharing group, even one that is normally quiesced when any backups are taken. Any member in the data sharing group that is active at or beyond the log truncation point must be restarted, and its logs are truncated to the SYSPITR LRSN point. You can specify the SYSPITR LRSN point in the CRESTART control statement of theDSNJU003 (Change Log Inventory) utility. Any data sharing group member that is normally quiesced at the time the backups are taken and is not active at or beyond the log truncation point does not need to be restarted. To be able to use system-level backups that have been dumped to tape, the level of DFSMShsm must be V1R8 or higher. By default, RESTORE SYSTEM recovers the data from the database copy pool during the RESTORE phase and then applies logs to the point in time at which the existing logs were truncated during the LOGAPPLY phase. The RESTORE utility never restores logs from the log copy pool. Restriction: RESTORE SYSTEM does not restore logs; the utility only applies the logs. If you specified BACKUP SYSTEM FULL to create copies of both the data and the logs, you can restore the logs by another method.  CATENFM The CATENFM utility enables a DB2 subsystem to enter DB2 Version 9.1 enabling-new-function mode and Version 9.1 new-function mode.  It also enables a DB2 subsystem to return to enabling-new-function mode from new-function mode.  All new Version 9.1 functions are unavailable when the subsystem is in conversion mode or enabling-new-function mode. 

 CATMAINT The CATMAINT utility updates the catalog; run this utility during migration to a new release of DB2 or when IBM Software Support instructs you to do so. 

 

 

 

Stand alone utilities

DSNJCNVBThe DSNJCNVB conversion utility converts the bootstrap data set (BSDS) so that it can support up to 10 000 archive log volumes and 93 active log data sets per log copy. Running DSNJCNVB is mandatory when migrating from Version 8. DB2 Version 9.1 for z/OS will not start if the BSDS is in the old format. DSNJCNVB can be run any time after a Version 8 system has migrated to new function mode. Prior to converting the BSDS to the new format, it can manage only 1 000 archive log volumes and 31 active log data sets per log copy. Converting the BSDS is optionalup until the migration to DB2 Version 9.1 for z/OS. DB2 must be stopped when running DSNJCNVB.. 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. The following EXEC statement is used to invoke DSNJLOGF: 

//stepname EXEC PGM=DSNJLOGF

 

DSNJU003 (change log inventory) The DSNJU003 stand-alone 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) 

The DSNJU004 (print log map) utility lists a variety of information. The print log map (DSNJU004) utility lists the following information: Log data set name, log RBA association, and log LRSN for both copy 1 and copy 2 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 Archive log command history BACKUP SYSTEM utility history System CCSID information System-level backup information In a data sharing environment, the DSNJU004 utility can list information from any or all BSDSs of a data sharing group. 

DSN1CHKRThe DSN1CHKR utility verifies the integrity of DB2 directory and catalog table spaces. 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. DSN1CHKR is a diagnosis tool; it executes outside the control of DB2. You should have detailed knowledge of DB2 data structures to make proper use of this service aid. 

DSN1COMP DSN1COMP estimates space savings that are to be achieved by DB2 data compression in table spaces and indexes. You can run this utility on the following types of data sets that contain uncompressed data: DB2 full image copy data sets

 VSAM data sets that contain DB2 table spaces Sequential data sets that contain DB2 table spaces (for example, DSN1COPY output) You cannot run DSN1COMP on concurrent copies. DSN1COMP does not estimate savings for data sets that contain LOB table spaces.

 

DSN1COPY The DSN1COPY utility allows you to copy DB2 VSAM data sets. With the DSN1COPY stand-alone utility, you can 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 A DB2 VSAM data set is a single piece of a nonpartitioned table space or index, or a single partition of a partitioned table space or index. The input must be a single z/OS sequential or VSAM data set. Concatenation of input data sets is not supported. Using DSN1COPY, you can also 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 reset to 0 the log RBA that is recorded in each index page or data page. You cannot run DSN1COPY on concurrent copies. DSN1COPY can operate on both base and clone objects. You can use the DSN1COPY utility on LOB table spaces by specifying the LOB keyword and omitting the SEGMENT and INLCOPY keywords. 

DSN1LOGP The DSN1LOGP utility formats the contents of the recovery log for display. The two recovery log report formats are: A detail report of individual log records. This information helps IBM Software Support personnel analyze the log in detail. (This information does not include a full description of the detail report.)

 A summary report, which helps you: – Perform a conditional restart – Resolve indoubt threads with a remote site – 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 that are identified by URID 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. DSN1LOGP can print the log records for both base and clone table objects. DSN1LOGP cannot read logs that have been compressed by DFSMS. (This compression requires extended format data sets.) 

DSN1PRNT With the DSN1PRNT stand-alone 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, and Sequential data sets that contain DB2 table spaces or index spaces. A DB2 VSAM data set is a single piece of a nonpartitioned table space or index, or a single partition of a partitioned table space or index. The input must be a single z/OS sequential or VSAM data set. Concatenation of input data sets is not supported. 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 DSN1PRNT detects such an error, it 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 index. You can run DSN1PRNT on image copy data sets and on table spaces and indexes. DSN1PRNT accepts an index image copy as input when you specify the FULLCOPY option. You cannot run DSN1PRNT on concurrent copies. DSN1PRNT is compatible with LOB table spaces, when you specify the LOB keyword and omit the INLCOPY keyword.

 DSN1PRNT does not decrypt any encrypted data; the utility displays the data as is. 

DSN1SDMP Under the direction of IBM Software Support, use the IFC selective dump (DSN1SDMP) utility to force dumps when selected DB2 trace events occur, write DB2 trace records to a user-defined z/OS data set, and start another DB2 trace.  To ensure that you do not take action on an IFCID 4 or IFCID 5 start or stop trace record, it is good practice to add P4,00DR,04,X'hhhh'to your control statement, where hhhh is the hex representation of the IFCID that you are trying to trigger on.