DB2 Administration

Team-Fly

---

 

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents
	Chapter 1.  Product Overview

---

DB2 Administration

DB2 has several tools available to help with administration of the subsystem environment and database objects. Some of these tools are optional and Windows-based.

• DB2 Installer. Provides a GUI for installing DB2 from a workstation.

• Control Center. Provides a single point of entry for controlling the entire DB2 family. Can use the Control Center to display database objects and their relationships to each other.

• Visual Explain. Gives the user a graphical tool to analyze the access paths that DB2 chooses for SQL queries or statements.

• Index Advisor. Assists in choosing an optimal set of indexes for table data.

• DB2 Estimator. Provides a facility for estimating the performance of applications.

DB2 Installer

The DB2 Installer (see Figure 1-20) provides a GUI for installing DB2 for OS/390 and z/OS from a workstation. An element of the DB2 Management Tools Package, DB2 Installer provides an alternative to the interactive system product facility (ISPF) installation panels and CLISTs traditionally used on OS/390 systems.

Figure 1-20. DB2 Installer.

The DB2 Installer application illustrates the overall installation process and keeps a graphical record of how each subsystem is defined. You can use DB2 Installer to increase your productivity. It has a command center from which you can install, migrate, or update DB2 for OS/390. The graphical interface follows an easy-to-read map through the entire installation process, including system modification program/extended (SMP/E), fallback, and sample jobs. In addition, it provides a graphical record of completed and uncompleted tasks by subsystem.

DB2 Installer helps you in the following:

• Installation. Prompts you for the appropriate fields necessary to customize DB2 for OS/390 installation jobs. These installation jobs are executed from your workstation or can be uploaded to the host for execution.

• Migration. Generates DB2 for OS/390 migration jobs and makes you aware of parameters you might want to customize to take advantage of new functions. It also generates an optional set of jobs that check for catalog consistency and other migration considerations.

• Update. Allows you to modify the current parameters and saves each updated set of parameters in a unique file.

There is also data-sharing support so you can use DB2 Installer to enable, add, or migrate datasharing members . It also comes with a full help facility.

Control Center

The Control Center supports the entire DB2 family including OS/390. The Control Center can be used as a single point of entry for controlling the entire DB2 family. You can use the Control Center to display database objects and their relationships to each other. The Control Center has a GUI that allows you to easily manage local and remote servers from a single workstation (see Figure 1-21).

Figure 1-21. DB2 Control Center.

The Control Center supports DB2 functions through the following components :

• Menu Bar. Used to access Control Center functions and online help.

• Toolbar. Used to access the other administration tools.

• Objects Pane. This is shown on the left side of the Control Center window. It contains all the objects that can be managed from the Control Center as well as their relationship to each other.

• Contents Pane. This is found on the right side of the Control Center window and contains the objects that belong or correspond to the object selected on the Objects Pane.

• Contents Pane Toolbar. These icons are used to tailor the view of the objects and information in the Contents pane. These functions can also be selected in the View Menu.

You can also launch other tools, such as DB2 Warehouse Manager, DB2 Performance Monitor, DB2 Visual Explain, Command Center, Script Center, Stored Procedure Builder, and more. Some of the types of support items in the Control Center added for OS/390 include

• Utility Support. Support for utility wildcarding gives the ability to execute utilities against a list of objects matching a specified pattern of matching characters . There is also support for restarting utilities from the last committed phase or the last committed point. This is available only for utilities originally started in the Control Center.

• Dataset Management. Ability to manage your datasets via the Control Center. This allows you to retrieve dataset lists, and depending on the type of dataset, you can show, rename, and delete members. Datasets can be dynamically allocated for utilities through the use of dataset templates used to automatically generate datasets based upon a defined set of criteria.

• DDL (Data Definition Language) Generation. A feature used to recreate database objects and dependent objects for the DB2 catalog. The output then can be saved to an OS/390 dataset (maybe for input to sequential processing using file input [SPUFI]) or to a workstation file.

• Integrated SQL Assist. Assists in helping build standalone SQL statements or SQL statements in triggers or views. The SQL statement will be inserted into the text area of the dialog after it is created.

• Wizards. Wizards are help screens that guide you through several different activities, such as creating objects. Each wizard has detailed information available to help perform these tasks. Wizards are integrated into the administration tools and assist in completing various tasks, such as Add Database. Wizards are invoked from the Control Center. They extract information, ask questions about the tasks being performed, then run any necessary calculations to help determine appropriate settings.

Visual Explain

DB2 Visual Explain (see Figure 1-22) lets you graphically analyze the access paths that DB2 chooses for your SQL queries or statements. The graph of the access path is displayed on an IBM OS/2 or Microsoft Windows NT workstation, eliminating the need to interpret the plan table output manually.

Figure 1-22. Visual Explain.

This tool offers suggestions for improving the performance of your SQL queries or statements. You can change an SQL statement and dynamically explain it to see if the access path is improved by the change. You can also use it to browse the current values of subsystem parameters.

Visual Explain issues DRDA queries through a DB2 client on the workstation to get the information it needs.

DB2 Visual Explain helps database administrators and application developers:

• Graphically see the access path for a given SQL statement.

• View statement cost in milliseconds and service units.

• Tune SQL statements for better performance.

• View the current values for subsystem parameters.

• View catalog statistics for tables and indexes.

• Generate custom reports .

Index Advisor

The Index Advisor is a tool to assist in choosing an optimal set of indexes for table data. This tool helps to determine the best indexes for a problem query or for a particular set of queries. It is helpful for testing an index on a workload without having to create the index.

This process is performed by creating a modeling database for DB2 for OS/390 and z/OS on a DB2 for UNIX, Windows, or OS/2 workstation using configuration parameters to mimic DB2 for OS/390. One way to invoke this tool is through the Control Center.

This tool works on a set of dynamic SQL statements, which have to be processed over a given period of time, and it handles SELECT, INSERT, UPDATE, and DELETE statements. In addition, it uses statistics from the system and user input to be able to evaluate strategies for given workloads. It also scans the catalog tables holding SQL from bound applications and can make determinations and suggestions.

DB2 Estimator

DB2 Estimator is an easy-to-use, standalone tool for estimating the performance of DB2 applications. It can be run on PCs that support Microsoft Windows 3.1, 95, 98, NT, 2000, or OS/2. It supports simple table sizing up to a detailed performance analysis of an entire DB2 application. DB2 Estimator saves time and lowers costs by allowing you to investigate the impact of new or modified applications on your production system before you implement them. DB2 Estimator provides information such as

• Elapsed time for a SQL statement that fetches N rows.

• Processor resource used during an N-way join.

• Impact of adding and dropping an index from a table.

• System support ability for an anticipated increase in workload.

• Effects of doubling processor resource on transaction response time.

• Storage required for a new table and its indexes.

• Index support for predicates.

• Effect of doubling table size on performance.

• Ability of application to complete within a batch window.

• Execution time of utility jobs.

• Effects of data compression.

• Effects of data sharing.

• Effects of triggers.

DB2 Estimator lets you change your definitions easily to help you evaluate alternative designs. By comparing the cost and performance of these designs, you can determine which design provides the optimum cost/performance ratio before you invest valuable time and resources coding your application or creating a real database. It helps you build more efficient databases by building a model of your system, based on your data knowledge, with reasonable access paths for your SQL statements. If actual DB2 information is available through DB2 Explain and/or DB2 Performance Monitor, you can tune the model by overriding the model assumptions. You can also import table, index, and SQL statements from your DB2 database to DB2 Estimator, and then modify them to suit your modeling requirements.

---

Team-Fly

Top