DB2 Universal Database s Comprehensive Tool Set

DB2 Universal Database's Comprehensive Tool Set

With the exception of DB2 Everyplace, each edition of DB2 Universal Database, along with the DB2 Administration Client, comes with a comprehensive set of tools designed to assist in administering and managing DB2 UDB instances, databases, and database objects. The majority of these tools have a graphical user interface (GUI); however, many of the tasks that can be performed with the GUI tools provided can also be performed by issuing equivalent DB2 UDB commands from the operating system prompt. The following sections describe the most commonly used GUI tools available.

The Control Center

Of all the DB2 UDB GUI tools available, the Control Center is the most important and versatile one provided. The Control Center presents a clear, concise view of an entire system and serves as the central point for managing systems and performing common administration tasks. From the Control Center, users can:

• Create and delete instances.

• Create and delete (drop) DB2 UDB databases.

• Catalog and uncatalog databases.

• Configure instances and databases.

• Create, alter, and drop buffer pools, tablespaces, tables, views, indexes, aliases, triggers, schemas, and user-defined data types (UDTs).

• Manage users and groups.

• Grant and revoke authorities and privileges.

• Load, import, or export data.

• Reorganize data and collect table statistics.

• Back up and restore databases and tablespaces.

• Replicate data between systems.

• Manage database connections.

• Monitor resources and track events that take place.

• Analyze queries.

• Schedule jobs to run unattended.

From the Control Center, users can also open other Control Centers, launch other tools, perform data warehousing tasks, and work with DB2 commands. Figure 2-7 shows how the Control Center looks on a Windows 2000 server.

Figure 2-7. The Control Center.

The Control Center is comprised of the following elements:

• A menu bar, which allows users to perform any of the Control Center functions available.

• A toolbar, which can be used to launch the other DB2 UDB GUI tools available. Figure 2-8 identifies the tools that can be invoked directly from the Control Center tool bar.

  Figure 2-8. The Control Center Toolbar.

  

It is important to note that every tool that can be invoked from the Control Center toolbar can also be invoked from the Control Center's menu bar.

• An objects pane (located on the left-hand side of the Control Center), which contains a hierarchical representation of every object type that can be managed from the Control Center.

• A contents pane (located on the right-hand side of the Control Center), which contains a listing of existing objects that correspond to the object type selected in the objects pane. (For example, if the Tables object type were selected in the objects pane, a list of all tables available would be listed in the contents pane.)

As you can see in Figure 2-7, every object listed in the contents pane is preceded by an icon intended to identify the type of object being described in the list. A wide variety of icons are used, and a list of all icons available, along with their corresponding object type, can be seen by viewing the Legend dialog, which can be accessed from the Control Center's menu bar. Figure 2-9 shows what the Legend dialog looks like on a Windows 2000 server.

Figure 2-9. The Legend dialog.

Users can perform specific tasks on any object by selecting the object from the list and clicking the right mouse button; a pop-up menu that lists every action available for that particular object will be displayed, and the user simply selects the desired action from the menu.

The Replication Center

The Replication Center is an interactive GUI application that allows users to administer data replication between a DB2 UDB database and any other relational database, whether that database is a DB2 database or not. Using the Replication Center, users can:

• Define replication environments.

• Create replication control tables.

• Register replication sources.

• Create subscription sets.

• Add members to a subscription set.

• Apply designated changes from one location to another.

• Synchronize data in two locations.

• Monitor the replication process.

• Perform basic troubleshooting for replication operations.

Figure 2-10 shows what the Replication Center looks like on a Windows 2000 server.

Figure 2-10. The Replication Center.

The Satellite Administration Center

The Satellite Administration Center is a GUI application that allows users to set up and administer a group of DB2 servers that perform the same business function. These servers, known as satellites, all run the same application and have the same DB2 UDB database definition needed to support a particular application. With the Satellite Administration Center, users create a group and then define satellites as members of this group. This group of satellites can then be administered as a single entity, as opposed to having to administer each satellite separately. If additional DB2 servers that perform the same business function are acquired later, they are simply added to the group as additional satellites.

Information about a satellite environment is stored in a central database referred to as the satellite control database. This database records, among other things, which satellites are in the environment, the group each satellite belongs to, and which version of an end-user business application a satellite is currently running. This database resides on a DB2 UDB server known as the DB2 control server, and it must be cataloged and accessible to the Control Center before the Satellite Administration Center can interact with it.

Groups of satellites are administered by creating batch scripts to set up and maintain the database definition that supports a business application on each satellite in a group. Each satellite then regularly connects to its satellite control server and downloads any scripts that apply to it. The satellite executes these scripts locally and uploads the results back to the satellite control database. This process of downloading batch scripts, executing them, and reporting the results of the batch execution back to the satellite control database is known as synchronization. A satellite synchronizes to maintain its consistency with the other satellites that belong to its group.

The Data Warehouse Center

The Data Warehouse Center is a set of GUI tools that allows users to build, access, and manage DB2 UDB data warehouses. With the Data Warehouse Center, users can automate the extraction, transformation, and loading process that must be performed to populate a data warehouse, as well as schedule, maintain, and monitor each phase of this process. The Data Warehouse Center can also be used to:

• Set up a data warehouse.

• Create a star schema.

• Set up DB2 and non-DB2 data sources.

• Set up access to a data warehouse.

• Calculate statistics.

• Manage metadata and the control database.

Figure 2-11 shows what the Data Warehouse Center looks like on a Windows 2000 server.

Figure 2-11. The Data Warehouse Center.

The Command Center

The Command Center is an interactive GUI application that allows users to:

• Execute SQL statements, DB2 commands, and operating system commands ”operating system commands must be preceded by an exclamation mark (!).

• View the results of the execution of SQL statements and DB2 commands and see the result data set produced in response to a query.

• Save the results of the execution of SQL statements and DB2 commands to an external file.

• Create and save a sequence of SQL statements and DB2 commands to a script file that can be used by the Task Center. (Such a script file can then be scheduled to run at a specific time or frequency.)

• Use the SQL Assist tool to build complex queries.

• Examine the execution plan and statistics associated with a SQL statement before (or after) it is executed.

Figure 2-12 shows what the Command Center looks like on a Windows 2000 server.

Figure 2-12. The Command Center.

As you can see in Figure 2-12, the Command Center is comprised of four different individual pages: the Interactive page, the Script page, the Query Results page, and the Access Plan page. On the Interactive page, users can enter and execute an SQL statement or a DB2 command. On the Script page, users can execute commands in sequence, create and save a script, run an existing script, or schedule a task. On the Query Results page, users can see the results of any query executed. On the Access Plan page, users can see the access plan for any explainable statement that was specified on the Interactive page or the Script page. (If more than one SQL statement is specified on the Script page, an access plan will only be created for the first statement.)

SQL Assist

SQL Assist is an interactive GUI application that allows users to visually construct complex SELECT, INSERT, UPDATE, and DELETE SQL statements and examine the results of their execution. SQL Assist is invoked directly from the Command Center, and once the desired SQL statement has been constructed inside SQL Assist, it can be written back to the Command Center, where it can then be executed immediately or saved to a script file where it can be executed later using the Task Center. Figure 2-13 shows what the SQL Assist tool looks like on a Windows 2000 server after it has been used to build a complex query. Figure 2-14 shows how the results of this query would be displayed if the query were executed from within SQL Assist (by selecting the "Run" push button located at the bottom right corner of the screen).

Figure 2-13. SQL Assist.

Figure 2-14. The Query Results dialog.

Visual Explain

Visual Explain is a GUI application that allows users to view the details of the access plan (including the statistics in the system catalogs) chosen by the DB2 optimizer for a given SQL statement without actually executing the statement.

With Visual Explain, each table, view, and index used, along with the operation performed on each, is represented in a diagram as nodes, and the actual flow of data is represented as links between each node. This information allows users to quickly view the statistics used at the time a particular query was optimized, determine whether or not an index would improve access to a table, obtain information about the cost required to perform a particular operation, and understand how tables have been joined. Armed with this information, administrators can make database design changes, and application developers can fine-tune SQL statements to improve overall performance.

Like SQL Assist, Visual Explain can be invoked directly from the Command Center. However, unlike SQL Assist, Visual Explain can also be invoked from the Control Center menu bar. If invoked from the Command Center, Visual Explain can generate access plan information for complex queries that have been created with SQL Assist. Figure 2-15 shows what the Visual Explain tool might look like on a Windows 2000 server when invoked from the Command Center.

Figure 2-15. Visual Explain.

Before Visual Explain can be used, Explain tables must be added to the appropriate database. Additionally, if Visual Explain is to be used from the Command Center, the Automatically generate access plan Command Center option must be turned on before the query to be explained is executed. Often, the DB2 Database Manager will attempt to create Explain tables automatically the first time Visual Explain is used. Explain tables can also be created by executing the script EXPLAIN.DDL, which can be found in the misc subdirectory of the sqllib directory where the DB2 Universal Database product was installed. Refer to the header portion of this file for specific information on how to execute it.

The Task Center

The Task Center is an interactive GUI application that allows users to schedule tasks, run tasks, and send notifications about completed tasks to other users. A task is a script together with any associated success conditions, schedules, and notifications. Users can create a task within the Task Center, generate a task by saving the results from a DB2 dialog or wizard, create a script within another tool and save it to the Task Center, or import an existing script. Such scripts can contain DB2 commands, SQL statements, or operating system commands.

The Task Center uses success code sets (the return codes or range of return codes that, if received, indicate the task was executed successfully) to evaluate the success or failure of any task it executes. Return codes that fall outside the range specified are considered failures. Furthermore, the Task Center evaluates the SQLCA return code of every SQL statement executed in a DB2 script, and if any statement fails, the entire task fails.

In addition to evaluating the success or failure of a particular task, the Task Center can perform one or more actions if a particular task succeeds and perform other actions if the same task fails. The Task Center can also be configured to perform one or more actions each time a scheduled task completes, regardless of whether the task executed successfully or failed.

Figure 2-16 shows what the Task Center looks like on a Windows 2000 server.

Figure 2-16. The Task Center.

If you run a script from the Task Center, instead of from the Command Center or a command prompt, the results will be logged in the Journal. Using the Journal, you can see a list of jobs that use a particular script along with the status of all scheduled jobs.

The Information Catalog Center

The Information Catalog Center is an interactive GUI application that allows users to manage descriptive data (business metadata) about source information. This metadata typically contains items such as the type of information being referenced, a description of the information, what the information contains, who owns and updates it, where it is stored, and how to get to it. Essentially, the Information Catalog Center does for an organization what an electronic card catalog does for a library, and more.

With the Information Catalog Center, users can search for specific objects stored in the information catalog, view any relationships that an object participates in, view an object's lineage, and create comments for objects. Users with the appropriate authority can also create new objects for a particular information catalog.

The Information Catalog Center helps administrators organize metadata objects by requiring that each object be based on an object type and by allowing administrators to define relationship types and additional object types. Furthermore, the Information Catalog Center provides security at the object level, so privileges are set for each object, allowing greater control of business information.

The Health Center

The Health Center is an interactive GUI application that allows users to monitor the state of the database environment. Whenever DB2 UDB is active, a background process continuously monitors a set of health indicators for problem areas. If the current value of a health indicator is outside the acceptable operating range, as defined by its warning and alarm thresholds, the health monitor generates an alert. (DB2 UDB comes with a set of predefined threshold values, which can be customized to meet your needs.)

With the Health Center, users can:

• View the status of the database environment. An icon indicating the alert level for a particular object (or for any objects contained by that object) is displayed beside every object listed in the Health Center's navigation tree. If that icon is a green diamond, the corresponding object does not currently have any alerts.

• View the alerts associated with a particular instance or database. When an object in the Health Center's navigation tree is selected, the alerts for that object are displayed in the pane located just to the right of the navigation tree.

• View detailed information, along with recommended actions to take, for a particular alert. When a particular alert item is selected, a dialog containing a notebook is displayed. The first page of this notebook shows detailed information about the alert. The second page recommends actions to take to correct the situation that caused the alert to occur.

• Configure the health monitor settings for a specific object and specify the default settings for an object type or for all objects within an instance.

• Select which contacts will receive an email or pager message whenever an alert occurs.

• Review a historical list of all alerts generated for a particular instance.

Figure 2-17 shows what the Health Center looks like on a Windows 2000 server.

Figure 2-17. The Health Center dialog.

The Journal

The Journal is an interactive GUI application that tracks historical information about tasks, database actions and operations, Control Center actions, messages, and alerts. To present this information in an organized manner, the Journal uses several different views. They are:

• Task History

• Database History

• Messages

• Notification Log

The Task History view shows the results of tasks that have already been executed. This view contains one entry for each individual task (regardless of how many times the task was executed) and allows users to:

• View details of any task that has been executed.

• View the results any task that has been executed.

• Edit any task that has been executed.

• View execution statistics associated with any task that has been executed.

• Remove any task execution record from the Journal.

The Database History view shows information stored in a database's recovery history file. The recovery history file is automatically updated whenever any of the following operations are performed:

• Database or tablespace backup

• Database or tablespace restore

• Roll-forward recovery

• Load

• Table reorganization

The Messages view shows a running history of messages that were issued from the Control Center and any other GUI tool, and the Notification Log view shows information from the administration notification log.

Figure 2-18 shows what the Messages view of the Journal looks like on a Windows 2000 server.

Figure 2-18. The Messages view of the Journal.

The License Center

The License Center is an interactive GUI application that allows users to view information about the license associated with each DB2 UDB product installed on a particular system. Such information includes processor status information, concurrent users policy information, license information, and user statistics or details. This tool can also be used to add or remove licenses or registered users, change license type policies, change the number of concurrent users, change the number of licensed processors, change the number of internet processor licenses, and configure a particular system for proper license monitoring. Figure 2-19 shows what the License Center looks like on a Windows 2000 server.

Figure 2-19. The License Center.

The Development Center

The Development Center is an interactive GUI application that provides users with a single development environment that supports the entire DB2 UDB Family. With the Development Center, users can:

• Create, build, and deploy Java and SQLJ stored procedures.

• Create, build, and deploy SQL scalar, SQL table, and OLE DB table user-defined functions.

• Create, build, and deploy user-defined functions that read MQSeries messages.

• Create, build, and deploy user-defined functions that extract data from XML documents.

• Debug SQL stored procedures using the integrated debugger.

• Create and build structured data types.

• View and work with database objects, such as tables, triggers, and views.

Figure 2-20 shows what the Development Center looks like on a Windows 2000 server.

Figure 2-20. The Development Center.

The Development Center also provides a DB2 Development Add-In for each of the following development environments:

• Microsoft Visual C++

• Microsoft Visual Basic

• Microsoft Visual InterDev

With these add-ins, users can easily access the features of the Development Center directly from a Microsoft development environment. This makes it easier to develop and incorporate stored procedures and user-defined functions into a DB2 UDB application development.

The Information Center

The Information Center is an interactive GUI application that allows users to access an electronic copy of the DB2 Universal Database product documentation. The Information Center makes it easy to quickly locate desired information, because it divides the available product documentation into distinct categories. A list of major topics for each category is presented in separate views. The views available include:

Tasks. This view can be used to locate instructions on how to perform a particular task. Topics presented with this view include common tasks users can perform with the Control Center, along with other major administration and application development tasks.

Concepts. This view can be used to see which DB2 UDB online manuals are available and to look for information in a particular book.

Reference. This view can be used to locate reference information. This view is designed to provide quick access to information contained in the DB2 UDB reference manuals (the Command Reference, the SQL Reference, the Call Level Interface Guide and Reference, and the Administrative API Reference ).

Troubleshooting. This view can be used to quickly locate detailed information about any problem encountered while using DB2 UDB. Often, this information contains recommendations that, when followed, will lead to problem resolution.

Samples. This view can be used to view descriptions of, and the source code for, the sample programs that are provided with any Developer's Edition of DB2 UDB or a DB2 UB Application Development Client. When the sample programs provided are viewed using the Information Center, they are formatted with color and hypertext links to make them easier to understand.

Tutorials. This view can be used to locate tutorials that can be used to quickly learn how to perform a particular task or use a particular tool that is part of the DB2 UDB Family.

After selecting the most appropriate view available, the user drills down through the list of topics provided until the desired topic is found. Then, when the appropriate topic has been selected, the corresponding page of the DB2 UDB documentation will be displayed in a Web browser window.

Figure 2-21 shows what the Information Center looks like on a Windows 2000 server.

Figure 2-21. The Information Center.

The Command Line Processor

The Command Line Processor (CLP) is a text-oriented application that allows users to issue DB2 UDB commands, system commands, and SQL statements, as well as view the results of the statements/commands executed. The Command Line Processor can be run in three different modes:

Command mode. When the Command Line Processor is run in command mode, the user simply enters a DB2 UDB command, system command, or SQL statement, preceded by the characters "db2", at the system prompt. (For example, the command connect to SAMPLE would be entered as db2 connect to SAMPLE). If the command contains characters that have a special meaning to the operating system being used, it must be enclosed in quotation marks to ensure that it will be properly executed (for example, db2 "select count(*) from employee"). If the command to be executed is too long to fit on a single line, a space followed by the line continuation character (\) can be placed at the end of the line that is to be continued , and the rest of the command can follow on a new line.

Interactive Input mode. When the Command Line Processor is run in interactive input mode, the "db2" prefix is automatically provided (as characterized by the db2 => input prompt). To run the Command Line Processor in interactive input mode, you simply enter the command "db2" at the system prompt. To exit out of interactive, you enter the command "quit" at the Command Line Processor prompt. Aside from that, the rules that apply to using the command mode of the Command Line Processor also apply to using the interactive input mode.

Batch mode. When the Command Line Processor is run in batch mode, it is assumed that all commands and/or SQL statements to be executed have been stored in an ASCII text file. (The characters "db2" should not precede the commands/statements stored in this file.) To run the Command Line Processor in batch mode, you simply enter the command db2 “f xxxxxxxx (where xxxxxxxx is the name of the file that contains the set of commands that are to be executed) at the system prompt.

Figure 2-22 shows what the Command Line Processor looks like on a Windows 2000 server when it is run in interactive input mode.

Figure 2-22. The Command Line Processor (in interactive input mode).

There are various command-line options that can be specified when the Command Line Processor is invoked; a list of all options available can be obtained by executing the command LIST COMMAND OPTIONS, either from the system prompt or the Command Line Processor prompt (when the Command Line Processor is run in interactive input mode).

The Configuration Assistant

The Configuration Assistant (Figure 2-23) is an interactive GUI application that allows users to configure clients so they can access databases stored on remote DB2 servers; in order to access an instance or database on another server or system, that system must be cataloged in the node directory of the client workstation, and information about the database must be cataloged in the database directory of the client workstation. The Configuration Assistant provides a way to maintain a list of databases to which users/applications can connect. With it, users can quickly catalog nodes and databases without having to know the inherent complexities involved with performing these tasks. The Configuration Assistant can also act as a lightweight alternative to the Control Center in situations where the complete set of GUI tools available have not been installed.

Figure 2-23. The Configuration Assistant.

From the Configuration Assistant, users can:

• Add new database objects.

• Work with or delete existing database objects.

• Bind applications.

• Set DB2 registry parameters.

• Configure the DB2 Database Manager.

• Configure ODBC/CLI parameters.

• Import and export configuration information.

• Change passwords.

• Test connections.

Figure 2-23 shows what the Configuration Assistant looks like on a Windows 2000 server.