DB2 Support Tools

The information contained here is designed to introduce the NWRDC community to the wide range of DB2 tools now available through the NWRDC. Product descriptions and documentation can be found at the Available Products Documentation and Manuals section of this web site.

DB2 TSO Panel
In TSO, from the Northwest Regional Data Center/INFORMATION CENTER panel select: 2 (DB2 - DB2/QMF & Related functions.)

This panel provides interactive utilities and tools for preparing and processing the tables within DB2.


1) DB2IDB2 Interactive Functions
1. SPUFISQL Processing Using File Input
2. DCLGENGenerate SQL & Source Declarations
3. Program PreparationPrepare DB2 Application to Execute
4. PrecompileInvoke DB2 Precompiler
5. Bind/Rebind/FreePerform Plan and Package Functions
6. RunRun an SQL Program
7. DB2 CommandsIssue DB2 Commands
8. UtilitiesInvoke DB2 Utilities
D. DB2I DefaultsSet Global Parameters
B) BMC AdminBMC Administrative Products
1. Alter for DB2Change/migrate DB2 Objects/structures
2. Catalog ManagerExecute DDL or query DB2 catalogue
D) NW/DASDIn-house DB2 Space Management Tool
L) CA/Log_AnalyzerDB2 Log Reporting Tool
R) R-ToolsDB/Access (Editor) and MOVE
Q) QMFQMF Query Management Facility


1. DB2I - DB2 Interactive Functions DB2I provides a series of task panels that allow you to perform DB2 programmer, administrator and operator tasks interactively.

1.1 SPUFI (SQL Processor Using File Input) SPUFI processes SQL statements that are not embedded in a program. It is especially useful for granting an authorization or creating a table when a host language is not necessary and for testing statements that are to be embedded in a program later. You may name a new member of a previously allocated library. Using the ISPF/PDF editor, you may enter one or more SQL statements to be processed. Each statement must end with a semicolon. Other choices on the task panel allow you to process the statement(s), automatically commit (or not) any changes to the database, and browse the output.

1.2 DCLGEN Produces data declaration statements for tables for COBOL data structures. The statements are placed in a copy library and can be included in programs by the use of INCLUDE statements.

1.3 Program Preparation Prepares programs to run by guiding you through the preparation tasks in sequence (compile, assemble, link or run). Programs that run under TSO can be run by the program preparation panels; however, programs that run under CICS can be prepared but not executed.

1.4 Precompile Replaces embedded, coded SQL statements in host language programs with comments followed by calls to the DB2 language interface. It also creates a data base request module (DBRM) that describes the SQL statements to the bind process.

1.5 Bind/Rebind/Free Modifies the relationship between the DB2 plan/package and the associated application. Bind - Establishes or replaces an application plan/package. Rebind - Modifies an existing application plan/package. Free - Deletes an application plan/package.

1.6 Run Executes programs that will run under the TSO environment.

1.7 DB2 Commands Allow you to issue up to four lines of DB2 commands. The command recognition character '-' is required.

1.8 Utilities Allow you to control the operation of DB2 utility programs, which run separately from the DB2 processor. Using the task panel, you can create and submit a utility job, create and edit a utility job, display the STATUS of or STOP a utility.

CHECK DATAIndicates which rows in the table space are to be checked for referential constraints.
CHECK INDEXTests whether indexes are consistent with the data they index, and issues a warning message if it finds an error.
CHECK LOBIdentifies any structural defects in a LOB table space and any invalid LOB values.
COPYCopies data from a table space to a sequential data set. It can be used to copy the entire space or only the data that changed since the last copy was made.
DIAGNOSEGenerates information useful in diagnosing problems.
LOADLoads data from a sequential data set to DB2 tables.
MERGECOPYMerges several partial copies of a table space into one copy and merges partial copies with a full image copy to make a new full image copy.
MODIFYDeletes a row from the SYSIBM.SYSCOPY catalog table and the SYSIBM.SYSLGRNG directory table.
QUIESCEEstablishes a point of recovery for a table space, partition, table space set or a list of table spaces and table space sets.
Reconstructs indexes from the table they reference.
RECOVERRecovers data to the current state or to a previous point in time by restoring to an image copy, then applying log records.
Reorganizes indexes & unloads data in the format acceptable to the LOAD utility.
Reorganizes table spaces & unloads data in the format acceptable to the LOAD utility.
REPAIRAllows you to change specific values within a DB2 database without using SQL.
REPORTReports table space information, including recovery history from SYSIBM.SYSCOPY catalog table, log ranges from SYSIBM.SYSLGRNG,volume serial numbers where archive log datasets reside, and name of all table spaces in a table space set.
RUNSTATSGathers summary information about the characteristics of data in table spaces and indexes.
STOSPACEUpdates DB2 catalog columns that indicate how much space is allocated for storage groups and related table spaces and indexes.
UNLOADUnloads data from one or more table spaces or image copy data sets to one or more sequential datasets in an external format.

1.D DB2I Defaults
Allows you to change default values that are used for several DB2I tasks. (The task panel may also be invoked during program preparation).

B. BMC Admin – BMC Administrative Products BMC Admin provides interactive tools for maintaining your DB2 objects.

B.1 BMC Alter for DB2
ALTER for DB2 is an ISPF-based software product which works in combination with your DB2 system to facilitate alteration, duplication and/or migration of DB2 data structures. Through the use of interactive panels, users can specify changes, analyze the effects of these changes and execute the changes when analysis is complete.

B.2 BMC Catalog Manager
CATALOG MANAGER provides interactive access to DB2 catalog information and application data with easy-to-use menus, panels and online Help.

D. NW/DASD – In-house DB2 Space Management Tool
Provides DB2 Space Management Information.  It gives information on the physical data structure of DB2 Table Spaces and Indexes.  The information is extracted on a nightly basis and is accurate as of the time of extract.

L. Log/Analyzer – CA Log Analyzer for DB2
Log Analyzer allows you to report on and analyze the data changes that are recorded in the DB2 active and archive log datasets. The DB2 logs contain a record of every database change. Log Analyzer enables you to monitor the changes made by specific plans or users to selected tables.

R. R-Tools - DB/ACCESS (Editor) and MOVE
ACCESS for DB2 (sometimes called R-Tools) is a Relational Editor for directly editing data in DB2 tables. Move for DB2 is the relational copy facility component of the Relational Tools. It is used to migrate relationally intact sets of data from multiple source tables to corresponding destination tables.

Q. QMF - Query Management Facility
The Query Management Facility is an interactive query product that allows you to create reports and charts from relational data under MVS/XA and MVS/ESA. With QMF, you can:

  • Access data kept in DB2 tables 
  • Perform calculations on that data 
  • Insert new data and change or delete existing data 
  • Produce and print customized reports for your data 
  • Create and format charts 
  • Communicate with other products