View figure.
This section introduces Control Center and its tool set, and provides an overview of the Control Center in a DB2 Server for VM environment.
Control Center is an IBM licensed program which works with the DB2 Server for VM licensed program to automate many of the manual Database Administrator (DBA) functions required to support databases within VM/ESA environments. It automates DBA functions such as database archiving and recovery, adding and deleting dbextents, adding dbspaces, database startup and shutdown, startup parameter changing, dbspace-level reorganization (with candidate selection), table-level reorganization and redefinition, catalog index reorganizations, and database monitoring. Each of these functions can be initiated immediately by an authorized user (local or remote), or they can be scheduled to execute at any specified date and time, or repetitive execution interval.
Control Center simplifies the task of supporting databases by automating the complex steps required to perform many DBA activities. These functions can be scheduled and performed automatically during periods of low system usage, improving the operational productivity of the entire system. Control Center also allows these functions to be performed in a consistent and repeatable manner with a high degree of security and control, thereby reducing the amount of support time.
Once the Control Center environment has been established, new databases can be easily incorporated. Due to the automated control provided by the service machine, the workload impact of additional databases to Database Administrators can be minimized.
Four levels of authorization can be specified controlling access to and use of Control Center. Additionally, these levels can be customized, allowing unique user authorization requirements to be implemented.
All database startup parameters are maintained by Control Center and can be updated at any time by the DBA using its user interface. Modified parameters become operational during the next database startup.
The user interface was designed to communicate with multiple service machines, both locally on a single CPU and on remote CPUs. The user interface provides both panel mode and command mode capabilities for maximum flexibility.
Control Center provides the DBA with the capability to perform all operator commands for a database, either in command mode or through the panel mode interface. These functions include all SHOW commands, the FORCE command, COUNTER command, and RESET command.
The Group Authorization Tool assists users in managing the access to database objects, simplifies the process of authorization and shortens the amount of time needed to grant or revoke privileges. For more details, see Chapter 13, Group Authorization Tool.
Archives are supported under all logmodes. Special archive features supported by Control Center include multivolume database archives (including User Archives), multiple log archives between database archives, implicit database and log archives, and archives (log and database) to disk.
Recovery functions provide the DBA with all available recovery sets derived from database archive and user archive events, and enable the DBA to select which recovery set should be used and how it should be applied.
The automated interface to the DB2 Server for VM Data Restore Feature lets users automate, track, and schedule the Data Restore functions on one or more databases, see Chapter 20, Data Restore RESTORE. This capability lets users automate, manage, track and schedule the Data Restore functions on one or more databases.
When the database is started, all logmode switches are automatically handled. This includes any coldlogs, archives, and log archives that are required to properly restart the database with a new logmode.
Many of the periodic monitoring activities performed by the DBA are now automated with the database operator commands. This feature can also be instructed to automatically react to detected conditions. For example, a DBA can enable a log check monitor that will check, at scheduled intervals, the space remaining in a database's log. If the monitor detects that an implicit archive is likely, then it can schedule an explicit log archive for a convenient time in an effort to avoid a possible implicit archive during peak utilization time. Control Center's database monitoring feature also has a report generation capability, that logs database monitor information each time a monitor runs.
Control Center provides a data reorganization tool with the dual advantages of fast execution and high space savings through the DROP DBSPACE command. Execution options also allow for data to be moved within a database (such as to a different storage pool or larger dbspace) or to a different database (for migration or regeneration purposes). Externally stored dbspace data created during the reorganization process can be used for backup purposes. A PAUSE option allows the backup tool to be utilized separately from reorganization.
Databases are analyzed for DBSPACES that need maintenance, and will optionally perform whatever maintenance is required (reorganization or Update Statistics).
A table-level reorganization tool is provided with many options for maximum flexibility. The data-only reorganization option performs a DATAUNLOAD and DATALOAD without dropping the table, which eliminates the need for DBA authority. Additional options allow the table to be dropped and recreated, along with all associated indexes, views, and grants, and for tables to be moved or copied, either within a database or between databases.
This tool also provides the capability to redefine a table definition, allowing columns to be added or deleted, and column characteristics to be changed, including column names, column datatypes, and null attributes.
The index analysis and reorganization tool provides for automated maintenance of indexes. You can choose analysis only, analysis and reorganization, or reorganization based on a previous analysis. You can also define the scope of analysis and reorganization by specifying a DBSPACE owner, DBSPACE name, or range of pages. You can limit run time by specifying a time or quantity limit.
This tool is used for searching for and listing database objects such as DBSPACES, tables, views, indexes, packages, and columns. A search can be conducted using a variety of search arguments. Applicable product tools can then be invoked against objects displayed in a list.
Cascading lists of dependent objects can be generated for an object within a currently displayed list. For example, a list of indexes can be displayed for any table selected within a list of tables.
The job scheduling tool provides the capability to schedule concurrent or single-threaded processing events against one or more databases. Detailed event control information provides the capability to establish dependencies between jobs, a dependency on database status (up or down), a dependency on a particular service machine and a dependency on probable completion within a designated timeframe. This allows for better control and automation of both System Administration tools (such as archiving and adding dbextents) and Database Administration tools (such as DBSPACE reorganization).
Control Center for VM, originally called SQL Master for VM (5684-136), available since June 1990, was designed by database administrators responsible for managing and maintaining large SQL/DS databases for IBM.
Control Center requires an environment provided by these operating system(s):
The following level of DB2 Server for VM is required:
Control Center supports environments without any tape management system, sending tape mount request messages directly to a defined tape operator user ID. It also provides support for CA-DYNAM/T (or DYNAM/T), VMTAPE, and EPIC, if they are used on the target system. In addition, Control Center can be adapted to other tape management products with customization.
A product installation includes a Control Center service machine, which can manage one or more databases, and optionally, one or more support machines. User machines linked to the service machine are given proper database authority. This enables communication with the databases managed by the service machine whether on the same CPU node or a remote node.
The service machine executes on a disconnected virtual machine (referred to as the Control Center service machine) that communicates directly with one or more databases. The service machine is normally in an idle mode until it is interrupted to initiate work, such as performing a database archive, responding properly to specific CMS, CP or database prompts and messages, tracking the status of all databases, and communicating with one or more Database Administrators in your information systems organization. It has the authority and know-how to perform simple and quick one-step operations as well as complex multi-step operations which can span several hours.
Database Administration tools such as Multiple User Mode DBSPACE Reorganization execute on a virtual machine separate from the database machine and can be initiated for immediate execution or scheduled to execute at any specified date and time, or repetitive execution interval. Depending on the tool, the size of the database, and other factors, the activity may take several hours to complete.
Control Center is able to distribute work by scheduling Database Administration tools to execute on one or more support machines where they will execute independent of the service machine and independent of each other. Overall performance and throughput (Database Administration tools can execute concurrently against different databases) is improved, and it eliminates the need to have separately installed service machines for each installed database. 1
The following two scenarios help to illustrate how Control Center manages local and remote databases in a VM environment.
Figure 1. Control Center in a Single-CPU/Multi-Database Environment
| View figure. |
Shown in Figure 1 is a single-CPU VM environment where two databases are being managed by a single service machine.
Two users linked to the service machine and having proper authority are able to perform functions against the database machines as if they were logged on to the machines directly.
For example, user MSTRUSR1 may be issuing operator commands to SQLMACH1 and reviewing the results (communication path (A)) while user MSTRUSR2 is initiating a single user mode DBSPACE reorganization on SQLMACH2 (communication path (B)).
Figure 2. Control Center with Support Machine in a Multi-CPU/Multi-Database Environment
 View figure. |
Shown in Figure 2 is a complex multi-CPU environment where two service machines and one support machine are managing the activities of four databases.
As an example, user MSTRUSR1 may be issuing operator commands to database SQLMACH1 (communication path (A)) while concurrently service machine MSTRSRV1 is running a scheduled database archive of database SQLMACH2 automatically from start to finish (communication path (D)). User MSTRUSR2 may be initiating a database recovery from tape for database SQLMACH3 on remote node VMSYSTM2 (communication path (B)) while the same managing service machine has initiated a table reorganization process to execute on support machine MSTRSUP1 (communication path (C)).
There are three types of Control Center tools: System Administration, Database Administration, and Control Center Administration. The System Administration and Database Administration tools work directly with database environments, whereas Control Center Administration tools help you to utilize the product as a whole. It is important for you to fully understand these three types of tools and how they work.
System Administration tools work with the database virtual machine consoles. They manage the operational needs of the database virtual machines such as:
Before using any of the System Administration tools you must have a basic understanding of how these tools work. These tools work very differently from the Database Administration tools and it is important that you understand the differences in order to avoid incorrect use of the Control Center product.
The System Administration tools all share the same basic operational characteristics; each of these tools is involved either directly or indirectly with the management of activities that run on database virtual machines. These tools do not in any way work with database data or data-related objects, and therefore, they do not require any of its connect authorization.
Each of these tools manage database generated console messages. These messages can be generated on a database virtual machine console during either single user or multiple user modes of operation. Many of these messages are database prompts for information or resources that must be responded to before processing can continue. During the running of a database archive, for example, many messages and prompts for resources (tape drives, or disks) and information (when to start, or what address to archive to) are displayed on the database's console. It is the System Administration tools related to database archiving that manage the archive activity by responding to each of the database's console messages and prompts.
These tools respond to every message generated on a database virtual machine console. This includes messages generated by the database, VM (CP or CMS), tape management products (VMTAPE, DYNAM/T, EPIC, and other), or by any message-generating program or service. All messages are routed from the database virtual machine to the Control Center service machine through the VM-provided Single Console Image Facility (SCIF) messaging service. SCIF enables the service machine to become the database machine's virtual console and keyboard, as if the service machine were logged on to the database virtual machine itself. This enables the service machine to view all messages as they appear on the database console, as well as directly feed input to the database's console.
When the service machine is routed a message through SCIF, Control Center determines what the message is related to (such as archive, or addition of a dbextent) and invokes the appropriate System Administration tool to review the message and determine if any information or resources are required by the database machine. Any required feedback is routed back to the database's console through the SCIF service.
Except for the Direct Operator Command Interface tool, System Administration tools run on the Control Center service machine. It is the service machine that invokes tools as required to manage a database machine's console. These tools have been designed to support multiple database machines simultaneously. This means that the service machine does not become locked into and dedicated to the management of a single database machine, but rather remains available to respond to other database machines as required. Control Center, for example, can manage the processing of multiple database archives occurring on multiple database machines simultaneously.
Table 1. System Administration Tools
| Tool Name | Database Connect Authority Required | Control Center Authority Required | Control Center Multiple User Mode
or Single User Mode | Managed by Control Center
or Runs on User's Machine |
|---|---|---|---|---|
| Database Archiving and Recovery Tools | ||||
| Database Archiving | No | Yes | SUM/MUM | Control Center |
| Database Recovery | No | Yes | SUM/MUM | Control Center |
| Database Monitoring Tools | ||||
| Database Log Check | No | Yes | MUM | Control Center |
| Database Up and Running Check | No | Yes | SUM/MUM | Control Center |
| Users Active | No | Yes | MUM | Control Center |
| Users Connected | No | Yes | MUM | Control Center |
| Users Locking | No | Yes | MUM | Control Center |
| DBSPACE Usage | No | Yes | MUM | Control Center |
| Database Counters | No | Yes | MUM | Control Center |
| Reset Counters | No | Yes | MUM | Control Center |
| POOL Usage | No | Yes | MUM | Control Center |
| Database Startup and Termination Tools | ||||
| Database Parameters | No | Yes | MUM | Control Center |
| Database Startup | No | Yes | SUM | Control Center |
| Database Termination | No | Yes | SUM | Control Center |
| Database Status Tool | ||||
| Database Status | No | Yes | MUM | Control Center |
| Database Operation Interface Tools | ||||
| Operator Command Interface | No | Yes | MUM | Control Center |
| Direct Operator Command Interface | Yes | No | MUM | User's Machine |
| Single User Mode Tools | ||||
| Add DBEXTENTS | No | Yes | SUM | Control Center |
| Delete DBEXTENTS | No | Yes | SUM | Control Center |
| Copy/Expand Database Directory | No | Yes | SUM | Control Center |
| Copy/Move Database Log Disk | No | Yes | SUM | Control Center |
| Copy/Move Data Disk | No | Yes | SUM | Control Center |
| Add DBSPACE | No | Yes | SUM | Control Center |
| Database COLDLOG | No | Yes | SUM | Control Center |
| Reorganize Catalog Indexes | No | Yes | SUM | Control Center |
Database Administration tools are database application programs, and therefore require Database Administrator authority. These tools do not work with or manage the database virtual machine. They are programs that connect to a database and perform data retrievals and updates using database queries while the database is running in multiple user mode (SYSMODE=M). Since these tools require the database to be in multiple user mode (with the exception of the Single User Mode Reorganization tools), they are sometimes referenced in this documentation as multiple user mode tools.
The virtual machine that runs these tools must have database Database Administrator authority. (An exception to this is the Table Reorganization and Redefinition tool, which has one option that only requires connect authority). When invoked, the tool runs a database application program that connects to the database. The program then runs database queries to manage (reorganize, backup, redefine, or migrate) data and data-related objects.
Unlike the System Administration tools, the Database Administration tools require a dedicated user machine console. This means that during the running of a Database Administration |tool, the virtual machine running the tool cannot do any other processing. For example, when you run the Multiple User Mode DBSPACE Reorganization tool on your virtual machine you will not be able to perform any other work until the tool completes processing. This is contrasted with the System Administration tools, which can manage the processing of multiple database console activities simultaneously.
|You should never run a Database Administration tool on the |service machine because these tools require a dedicated virtual machine |console. If the Control Center service machine runs this type of tool, then it will not be available to respond to database machine console messages and prompts. Therefore, these tools should be run on virtual machines that can afford to dedicate their consoles to the processing of a single activity. The Control Center support machines should be installed and used to run these tools.
When scheduling Database Administration tools, be sure to specify a support machine (or other non-Control Center service machine) that will run the tool. If you do not specify a support machine, the tool will run on the service machine, making it unavailable to process any other requests.
Table 2. Database Administration Tools
| Tool Name | Database Connect Authority Required | Control Center Authority Required | Control CenterMultiple User Mode
or Single User Mode | Managed by Control Center
or Runs on User's Machine |
|---|---|---|---|---|
| Automated DBSPACE Maintenance Tools | ||||
| DBSPACE Reorganization Maintenance | Yes | No | MUM | User's Machine |
| Statistics Maintenance | Yes | No | MUM | User's Machine |
| Index Reorganization | Yes | No | MUM | User's Machine |
| DBSPACE Reorganization Tools | ||||
| Multiple User Mode DBSPACE Reorganization | Yes | No | MUM | User's Machine |
| Single User Mode DBSPACE Reload | Yes | Yes | SUM/MUM | Control Center |
| Single User Mode DBSPACE Reorganization | Yes | Yes | SUM | Control Center |
| DBSPACE Reorganization Driver | Yes | No | MUM | User's Machine |
| Table Reorganization and Redefinition Tool | ||||
| Table Reorganization and Redefinition | Yes | No | MUM | User's Machine |
| Object Search and List Tools | ||||
| DBSPACE Search and List | Yes | No | MUM | User's Machine |
| Package Search and List | Yes | No | MUM | User's Machine |
| Table Search and List | Yes | No | MUM | User's Machine |
| Table Index List | Yes | No | MUM | User's Machine |
| Table View List | Yes | No | MUM | User's Machine |
| Rebind Package Tool | ||||
| Rebind Package | Yes | No | MUM | User's Machine |
Control Center Administration tools do not work directly with a database environment, but rather work in support of service and support machines. For example, the Job Scheduling tool enables the product to manage the processing of jobs. These tools have been designed to work with and enhance the usability of System Administration and Database Administration tools. You can use the Job Scheduling tool to schedule database archives (a System Administration tool), as well as dbspace reorganizations (a Database Administration tool).
In addition to supporting the Control Center environment, these tools also allow you to manage the related service and support machines. Management of these types of machines includes activities such as starting and stopping Control Center, authorizing new users, and listing and reviewing control files.
These tools are invoked by the service and support machines as required. You can access them through the menu interface. Each of these tools has been designed to minimize the use of Control Center service and support machine consoles. When run, they require only brief periods of service and support machine processing, keeping the machine consoles available for the servicing of database-related activities.
Control Center Administration tools run on the service and support machines. As mentioned above, they have been designed to minimize the use of the console activity, leaving the consoles more available for use by the System Administration and Database Administration tools.
Table 3. Control Center Administration Tools
| Tool Name | Database Connect Authority Required | Control Center Authority Required | Control Center Multiple User Mode
or Single User Mode | Managed by Control Center
or Runs on User's Machine |
|---|---|---|---|---|
| Job Scheduling Tools | ||||
| Job Scheduling | No | Yes | n/a | Control Center |
| Job Schedule List | No | Yes | n/a | Control Center |
| Master Scheduling | No | Yes | n/a | Control Center |
| Control Center Administration Tools | ||||
| View Message Log | No | Yes | n/a | Control Center |
| Issue Database Commands | No | Yes | n/a | Control Center |
| List Control Center VM Database Files | No | Yes | n/a | Control Center |
| Query DBEXTENTS/STORPOOL Mapping | No | Yes | n/a | Control Center |
| Control Center Database Tape Management Tool | ||||
| Tape Management | No | Yes | n/a | Control Center |