Db2 Admin Tool features and benefits

The features of Db2 Admin Tool help you to efficiently and effectively manage Db2 environments.

Db2 Admin Tool features

Display the Db2 catalog tables

Db2 Admin Tool provides extensive support for displaying the Db2 catalog. The scope of information that can be displayed is described in this information.

Display any object in the Db2 catalog: You can retrieve catalog data for any Db2 data object. You can specify the data that is retrieved (for example, you might request that data be retrieved for all databases that are owned by THOMAS and that have the prefix D402).
Db2 Admin Tool retrieves catalog data by using predefined SELECT statements for the more commonly used queries. The rows that are retrieved from the catalog are displayed using the ISPF table-display service. The display panel can be the Db2 Admin Tool default panel, from which you can issue various Db2 Admin Tool line commands, or a panel that you tailor for the result of a particular SQL SELECT. In the latter case, you can use line commands to issue new SQL calls that use information from the columns of rows that have been returned.
Display related Db2 objects using line commands: You can use Db2 Admin Tool line commands to navigate the catalog. For example, from a display panel that shows databases, you can use a line command to show all table spaces in one of the databases. Then, from the table spaces panel, you could issue a line command to show authorizations for a table space or show the status of image copies for the table space.
Display catalog information: You can request detailed information about any object in the Db2 catalog. A request for details about an application plan, for example, returns information such as the plan's owner, latest bind time, and number of bytes in the base section.
Show the authorization for Db2 objects: You can retrieve information about the authorizations for all Db2 objects. From an authorization display panel, you can then grant and revoke privileges.
Display the static SQL from application plans and packages: You can display the static SQL statements in a plan or a package, which is useful if you do not have access to a program's source code.
Display the DDL for existing views: You can display the SQL source that created a view, which is useful if you do not have access to the CREATE VIEW SQL (DDL) statement.
Run with multiple copies of the Db2 catalog: This function allows you to use the Db2 system catalog, one of the many copies of it, or the catalog of a remote site. You might choose to use a different copy of the catalog for each weekday, thus associating a backup with each weekday. Or this feature can allow the system administrator to work on the actual system catalog, while developers use a copy of the catalog, thereby decreasing contention for the catalog.

Execute dynamic SQL statements

You can issue any dynamic SQL statement from your screen or from a data set. You can build and execute an SQL SELECT statement interactively by using line commands.

In addition, by entering required parameters from a panel, you can execute the following SQL statements: GRANT, REVOKE, CREATE, DROP, LABEL ON, and COMMENT ON. This feature allows you to execute the statements without knowing the exact SQL syntax; Db2 Admin Tool provides guidance for the required SQL parameters.

Manage changes to Db2 objects

Use the Change Management function to manage and track the changes that you make to the definitions of your Db2 objects. You can use the Change Management function to complete all of the steps that are typically involved with changing database objects:

Defining your changes.
Resolving any conflicts by applying any pending changes for the objects as virtual changes.
Registering the changes.
Analyzing the changes to generate a work statement list that applies the changes.
Running the changes in the correct order.

Change Management also makes it easy to back out completed changes. Making and managing changes with Change Management provides a convenient audit trail.

Multi-target change enhances change management and provides the following capabilities:

Changes can be deployed from one central system to multiple target locations.
Status and other information about the target change can be communicated back to the central system.
From one centralized view, DBAs can view of all the changes that have been imported across various target systems.

Issue Db2 commands against databases and table spaces

You can issue any Db2 command against any database or table space that you have selected using Db2 Admin Tool. For example, you can issue the DISPLAY, START, and STOP commands against a database.

Db2 commands are passed to the instrumentation facility interface (IFI), and the result is displayed in ISPF browse.

Run Db2 utilities

You can generate the JCL for Db2 utilities and then run them in batch, or you can include the utility statements in a work statement list to be run at another time or on another subsystem. This function applies to the utilities for storage groups, table spaces, tables, and indexes. For example, you can, generate JCL to run the COPY, REORG, and RUNSTATS utilities for a table space.

The generated JCL consists of a JOB statement, EXEC statement, and all required DD statements. When the JCL is generated, Db2 Admin Tool invokes ISPF edit, which lets you change the JCL, submit it, or copy it to another data set.

You can generate utilities using LISTDEFs and TEMPLATEs.

Issue complex queries

You can run performance and space utilization queries against a database. The data that is returned can help you to determine whether you need to:

Run the RUNSTATS or STOSPACE utilities
Reorganize or redesign parts of your database or indexes
Change the locking rule for tables
Drop an index
Move tables to separate table spaces
Extend the primary allocation for a table space or index
Reduce the size of a table space

Use the EXPLAIN function

The Db2 Admin Tool EXPLAIN function supports the EXPLAIN statement and provides related support. (The EXPLAIN statement gathers information about the access path Db2 chose to process a query.) By using the EXPLAIN function you can:

Create a plan table (PLAN_TABLE) in the wanted database and table space.
Issue an SQL EXPLAIN statement and see the resulting rows in the plan table.
List a plan table to look at rows from previously executed EXPLAIN statements, or rows from BIND and REBIND operations that were executed with EXPLAIN(YES) specified.
With this function, predefined search criteria help you find rows in the plan table. Predefined search criteria exist for application plans, DBRMs, collections, and packages. You can see the access path that is chosen by Db2 to process queries, and the tables and indexes that are accessed by Db2.
Use EXPLAIN (ONLY) to populate EXPLAIN tables but not create a package. This option allows EXPLAIN to be run when the authorization ID of the bind or rebind process does not have the privilege to execute statements in the package.
Upgrade a plan table to the current version of Db2.
Look at the Db2 calculated cost.
Create and display the Db2 explain tables.
Insert and work with Db2 optimizer hints in the plan table.

Manage SQL IDs

You can change the current Db2 SQL ID by entering a new one or by selecting one from a list of secondary SQL IDs. Db2 Admin Tool displays a list of SQL IDs that you are allowed to use. The list is created either by simulating or invoking the authorization exit in your system.

Perform system administration functions

The system administration functions that you can perform using Db2 Admin Tool include:

Displaying threads
Displaying and terminating utilities
Displaying and managing traces
Displaying and updating RLIMITs, including the predictive governing limits in Db2
Displaying and altering buffer pools
Displaying and setting archive log parameters and archiving the log
Displaying Db2 system parameters and updating dynamic parameters
For DDF (distributed data facility):
- Starting and stopping DDF
- Displaying and updating the communications database (CDB)
- Displaying and canceling distributed threads
- Displaying active locations
Dynamically managing system parameters

Reverse engineer Db2 objects

Reverse engineering generates the SQL statements necessary to re-create a Db2 object. You can reverse engineer the Db2 objects in your database catalog.

Typical uses for the Db2 Admin Tool reverse engineering function include the following tasks:

Extracting the DDL for an object before changes are made, so that the changes are applied to the current definition and are available for fallback purposes.
Moving Db2 objects to another Db2 subsystem. By using the reverse engineering function (together with the table unload and load functions), objects can be moved after a few manual modifications to the generated SQL and batch jobs.

The SQL statements can be generated online or with a batch job.

Use the Db2 predictive governing

You can use Db2 Admin Tool to display, insert, update, or delete predictive governing rows in the resource limit tables. Furthermore, if Db2 Admin Tool receives a predictive governing warning (SQLCODE +495) when running a dynamic SQL statement, Db2 Admin Tool ask whether the SQL statement should be executed or cancelled. If the predictive governing estimates that executing a dynamic SQL statement that was issued from Db2 Admin Tool will exceed the error limit (SQLCODE -495), Db2 Admin Tool displays an error message, and the SQL statement is not executed.

You can use predictive governing limits to prevent users from running wild queries on catalog tables or any other tables that aredisplayed using Db2 Admin Tool. By using predictive governing limits for the Db2 Admin Tool package, this type of query can be inhibited either by setting up a predictive governing warning or an error limit in the resource limit table.

Related information:

Limiting resources for SQL statements predictively (Db2 13 for z/OS)

Alter the Db2 table definition

You can alter the definition of a Db2 table. Permissible changes include the following tasks:

Changing the database, table space, owner, and the name of the table
Modifying the definitions of table columns
Changing the sequence of the columns in the table
Inserting and dropping columns

Migrate Db2 data to other Db2 systems

You can copy Db2 data to another Db2 system. This is a useful function if you want to create a separate Db2 test system or if you want to move a test system into production. You can also use this function to consolidate two separate database systems into one.

Extend existing Db2 Admin Tool applications or develop new applications

You can extend Db2 Admin Tool to invoke other ISPF applications that you use for Db2 Admin Tool and application development. Some applications that you might want to invoke from Db2 Admin Tool are as follows:

Security tools
Vendor Db2 utilities
Storage management tools

Db2 Admin Tool also enables you to quickly build new ISPF applications for displaying and maintaining Db2 data. Some of the types of data for which you might build such applications are as follows:

Application definition data
Db2 performance data
Extra security data

A sample application is included with the product to illustrate how you might use Db2 Admin Tool to create new applications.

Perform space management functions

Db2 Admin Tool enables you to perform space-related functions such as resizing page sets, moving page sets to and from STOGROUP- and VCAT-defined space, and estimating space allocations for new table spaces and indexes.

Create and run work statement lists

Db2 Admin Tool enables you to create and run work statement lists that include sets of operations. You can execute the entire set, rerun sets, or capture a set of operations that you create on one system for use on another system.

Launch installed IBM Db2 Tools that have an ISPF interface

You can invoke installed IBM Db2 tools that have an ISPF interface—directly from Db2 Admin Tool. The Db2 Admin Launchpad provides a convenient way of creating a centralized ISPF table with the names of your tools. Then, by selecting an entry in this table, you can easily start one of the tools.

Performance

Db2 Admin Tool is equipped with the following performance features:

Db2 Admin Tool uses dynamic SQL to access the Db2 catalog, which ensures that Db2 always uses the most efficient access path to the catalog (provided RUNSTATS statistics are available for the Db2 optimizer).
Before Db2 Admin Tool displays information, it does an SQL commit. By doing so, Db2 Admin Tool ensures that a user cannot lock the catalog for long periods of time. If an SQL error occurs, Db2 Admin Tool rolls back the unit of work before it displays any information.
Db2 Admin Tool has a default limit of 1000 for fetching rows. This limit helps to prevent time-consuming queries. You can change the default of 1000 for an execution of Db2 Admin Tool if more rows are needed. You can set this value permanently or you can set a parameter in the DB2 Admin Defaults (ADB2P2) panel to reset the default value at the next startup.
You can use Db2 resource limit facilities (RLF) to limit the amount of CPU time that a dynamic SQL statement in Db2 Admin Tool can use - either by using the reactive governor facilities of RLF or by using the predictive governing facilities.
Db2 Admin Tool can run on a copy of the Db2 catalog. Besides improving performance, running on a copy of the catalog can reduce contention for the catalog. Db2 Admin Tool provides commands to generate jobs to create and populate copies of the Db2 catalog.

Security

Db2 Admin Tool does not expose the security of the Db2 system. All Db2 access is controlled by the existing security provisions of the Db2 system. You can optionally configure Db2 Admin Tool to allow users to execute DDL generated to re-create views that are dropped as a result of dropping other objects. The user can execute this DDL even if they do not have the direct authority. This is done by using auth-switching and has its own safeguards to ensure the DDL is not changed from that generated by Db2 Admin Tool. A user must have access to a RACF® (or equivalent) profile to use auth-switching.

Db2 Admin Tool benefits

This section describes a few of the many ways in which Db2 Admin Tool is typically used, and gives examples of specific applications.

Explore databases

Db2 Admin Tool lets you quickly navigate the Db2 catalog and display tables, table columns, and indexes. If you are authorized by Db2, you can also display the content of tables either by doing a simple list of the table or by building SQL statements and executing them against a table.

You can use the Db2 Admin Tool functions to explore unknown databases rapidly or get a quick overview of a database. None of these uses require that you remember the exact syntax of Db2 commands or SQL statements.

Determine and correct problems

You can use Db2 Admin Tool to identify and fix problems with your databases. With its ability to navigate the catalog and use Db2 commands on objects, Db2 Admin Tool can help you discover, analyze, and fix database problems in a more user-friendly fashion than is available with native Db2.

Develop small applications

You can use Db2 Admin Tool to rapidly develop small applications. As you become familiar with the tool, you might find the time that it takes to develop small Db2 Admin Tool dialogs is greatly reduced.

Examples

If you have a tool at your installation that manipulates Db2 tables, you can develop your own line command to access it from the Db2 Admin Tool panel that displays tables (implementing the line command as an SMP usermod). Then you can invoke the table tool as a natural follow-on to using Db2 Admin Tool.
Perhaps you want to generate more DECLARE statements for a PL/I table than is possible with the Db2 DCLGEN tool. You can write an application to invoke DCLGEN directly from the Db2 Admin Tool panel that displays tables. You can also modify the output you receive from DCLGEN to, for example, meet your installation's standards and requirements.
You might want to build prototypes of SQL SELECT statements. You can build the statements, test them and, when you are satisfied with them, copy the statements to a data set to include in your application program.
Db2 Admin Tool can help you maintain any Db2 tables that you use for installation standards and special requirements. You can use Db2 Admin Tool to develop a small application that describes all of the applications that you have in the system. Or you can use it to display existing tables that, for example, contain information about Db2 plan performance or batch job execution statistics.

Copy tables from one Db2 system to another

You can use the table utilities that Db2 Admin Tool generates to copy tables from one Db2 system to another. You need to make a few modifications to the generated JCL.

Start Db2 Tools

You can invoke installed IBM Db2 tools that have an ISPF interface directly from Db2 Admin Tool. Db2 Admin Tool guides you through the process of creating a central table with the names of your Db2 utilities. After this table is created, you can select an entry in it to start one of the Db2 tools.