DB2 9 Fundamentals: Certification Study Guide

DB2 9's Comprehensive Tool Set

With the exception of DB2 Everyplace, DB2 for i5/OS, and DB2 for z/OS, each edition of DB2 and the DB2 Client comes with a comprehensive set of tools designed to assist in administering and managing DB2 instances, databases, and database objects. The majority of these tools have a graphical user interface (GUI); however, most of the tasks that can be performed with the GUI tools provided can also be performed by issuing equivalent DB2 commands from the operating system prompt or the DB2 Command Line Processor (another tool that we'll look at later in this section). The following subsections describe the most commonly used GUI tools available.

The Control Center

Of all the DB2 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 DB2 systems and performing common administration tasks. With the Control Center, users can:

• Create and delete instances

• Create and delete (drop) DB2 databases

• Catalog and uncatalog databases

• Configure instances and databases

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

• Grant and revoke authorities and privileges

• Export, import, or load data

• Reorganize tables and collect table statistics

• Back up and restore databases and table spaces

• Replicate data between systems

• Manage database connections

• Monitor resources and track events as they take place

• Analyze queries

• Schedule jobs to run unattended

The Control Center interface presents itself using one of three different views:

• Basic: The basic view displays essential objects such as databases, tables, views, and stored procedures, and limits the actions you can perform on those objects. This is the view you should use if you only want to perform core DB2 database operations.

• Advanced: The advanced view displays all objects available in the Control Center and allows you to perform all actions available. This is the view you should use if you are working in an enterprise environment or if you want to connect to DB2 for i5/OS or DB2 for z/OS.

• Custom: The custom view gives you the ability to tailor the object tree and actions allowed to meet your specific needs.

Figure 2-3 shows how the Control Center looks on a Windows XP server when the advanced view is used.

Figure 2-3: The Control Center (advanced view).

If you look closely at Figure 2-3, you will notice that 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 GUI tools available. Figure 2-4 identifies the tools that can be invoked directly from 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 upper 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.)

• An object details pane (located on the lower right-hand side of the Control Center), which contains detailed information about the object selected in the object tree or contents pane.

Figure 2-4: The Control Center Toolbar.

As you can see in Figure 2-3, 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 and toolbar. Figure 2-5 shows what the Legend dialog looks like on a Windows XP server.

Figure 2-5: The Legend dialog.

Users can perform specific tasks on an object by selecting it from the list provided (either in the objects pane or the contents pane) and clicking the right mouse button; when the right mouse button is clicked, 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 database and any other relational database-whether that database is a DB2 database or not. From 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-6 shows how the Replication Center looks when it is first invoked on a Windows XP server.

Figure 2-6: 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 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 each satellite having to be administered 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 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 is needed to support the same 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 Command Editor

The Command Editor is an interactive GUI application that is used to generate, edit, execute, and manipulate SQL statements and DB2 commands; to work with the resulting output; and to view a graphical representation of the access plan chosen for explained SQL statements. From the Command Editor, users can:

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

• 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 invoked 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-7 shows how the Command Editor looks on a Windows XP server after a database connection has been established.

Figure 2-7: The Command Editor.

As you can see in Figure 2-7, the Command Editor is comprised of three different individual pages (which are accessed by tabs): the Commands page, the Query Results page, and the Access Plan page. Users can enter and execute an SQL statement or a DB2 command, create and save a script, run an existing script, or schedule a task from the Commands page. Once a query has been executed, users can see the results, if any, on the Query Results page. And on the Access Plan page, users can see the access plan for any explainable statement that was specified on the Commands page. (If more than one SQL statement is specified on the Commands page, an access plan will only be created for the first statement encountered.)

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 Editor, either by selecting the appropriate menu option or by selecting the appropriate toolbar icon (this icon will not be available until a database connection is established). Figure 2-8 identifies the Command Editor toolbar icon that is used to activate the SQL Assist dialog; Figure 2-9 shows how the SQL Assist dialog might look on a Windows XP server after it has been used to build a complex query.

Figure 2-8: The SQL Assist icon on the Command Editor toolbar.

Figure 2-9: SQL Assist.

Once the desired SQL statement been constructed inside SQL Assist, it can be written back to the Command Editor, where it can then be executed immediately or saved to a script file where it can be executed later using the Task Center.

Visual Explain

Visual Explain is a GUI tool that provides database administrators and application developers with the ability to view a graphical representation of the access plan that has been chosen by the DB2 Optimizer for a particular SQL statement. In addition, Visual Explain allows you to:

• See the database statistics that were used to optimize the SQL statement.

• Determine whether or not an index was used to access table data. (If an index was not used, Visual Explain can help you determine which columns might benefit from being indexed.)

• View the effects of performance tuning by allowing you to make "before" and "after" comparisons.

• Obtain detailed information about each operation that is performed by the access plan, including the estimated cost of each.

Figure 2-10 shows how the Visual Explain tool might look like on a Windows XP server when it is displayed by selecting the Access Plan page from the Command Editor.

Figure 2-10: Visual Explain.

The output provided by Visual Explain consists of a hierarchical graph that represents the various components that are needed to process the access plan that has been chosen for a particular SQL statement. Each component is represented as a graphical object known as a node, and two types of nodes can exist:

• Operator: An operator node is used to identify either an action that must be performed on data, or output produced from a table or index.

• Operand: An operand node is used to identify an entity on which an operation is performed (for example, a table would be the operand of a table scan operator).

Typically, operand nodes are used to identify tables (symbolized in the hierarchical graph by rectangles), indexes (symbolized by diamonds), and table queues (symbolized by parallelograms-table queues are used when intrapartition parallelism is used). Operator nodes, on the other hand, are used to identify anything from an insert operation to an index or table scan. Operator nodes, which are symbolized in the hierarchical graph by ovals, indicate how data is accessed, how tables are joined, and other factors such as whether or not a sort operation is to be performed. Arrows that illustrate how data flows from one node to the next connect all nodes shown in the hierarchical graph, and a RETURN operator normally terminates this path.

Visual Explain 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.

Visual Explain can be invoked directly from the Command Editor by selecting the Access Plan page. However, before Explain information can be displayed, an access plan must exist; access plans can be generated for SQL statements entered on the Commands page of the Command Editor by selecting either the Execute and Access plan or the Access Plan icon from the Command Editor toolbar. Figure 2-11 identifies the Command Editor toolbar icon that is used to generate an access plan for an SQL statement. (The Execute and Access plan icon is the icon located to the right of the Access Plan icon.)

Figure 2-11: The Access Plan icon on the Command Editor toolbar

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, operating system commands, or any combination of the three.

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. In addition, 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. As well as 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 the outcome of that task (success or failure).

Figure 2-12 shows how the Task Center might look on a Windows XP server after a Database Backup task has been created.

Figure 2-12: The Task Center.

The Health Center

Database monitoring is such an integral part of database administration that DB2 comes equipped with a monitoring utility, which is known as the Database System Monitor. Although the name suggests that only one monitoring tool is provided, in reality the Database System Monitor is composed of two distinct types of tools-a snapshot monitor and one or more event monitors-that can be used to capture and return system monitor information. The snapshot monitor allows you to capture a picture of the state of a database (along with all database activity) at a specific point in time, while event monitors capture and log data as specific database events occur. Along with the Database System Monitor, DB2 provides two additional tools that are designed to help database administrators monitor DB2 systems under their control. These tools are known as the Health Monitor and the Health Center. Together, these tools provide a management by exception capability that enables administrators to address system health issues before they become real problems.

The Health Monitor is a server-side tool that constantly monitors the health of a DB2 Database Manager instance without a need for user interaction; the Health Monitor uses several health indicators to evaluate specific aspects of instance and database performance. A health indicator is a system characteristic that the Health Monitor monitors continuously to determine whether or not an object is operating normally; each health indicator has a corresponding set of predefined threshold values, and the Health Monitor compares the state of the system against these health-indicator thresholds to see whether they have been exceeded. If the Health Monitor finds that a predefined threshold has been surpassed (for example, if the amount of log space available is insufficient), or if it detects an abnormal state for an object (for example, if the instance is down), it will automatically raise an alert.

The Health Center is a GUI tool that is designed to interact with the Health Monitor. Using the Health Center, you can select the instance and database objects that you want to monitor, customize the threshold settings of any health indicator, and specify where notifications are to be sent and what actions are to be taken if an alert is issued. The Health Center also allows you to start and stop the Health Monitor as well as access details about current alerts and obtain a list of recommended actions that describe how to resolve the situation that caused an alert to be generated. Figure 2-13 shows how the Health Center looks on a Windows XP server (in this case, after two warning-type alerts have been generated).

Figure 2-13: The Health Center.

A Word About the SQL Performance Monitor

If you are running DB2 for i5/OS, the SQL Performance Monitor is a valuable tool that can be used to keep track of the resources SQL statements use. Information on resource usage can help you determine whether your system and/or your SQL statements are performing at optimum level or whether they need to be tuned. There are two types of monitors you can elect to use:

• Summary SQL Performance Monitor: The Summary SQL Performance Monitor is the iSeries Navigator version of the Memory Resident Database Monitor, found on the system interface. As the name implies, this monitor resides in memory and only retains a summary of the data collected. When the monitor is paused or ended, this data is written to disk; once written to disk, the data collected can be analyzed. Because the Summary SQL Performance Monitor stores its information in memory, the performance impact to the system is minimized. However, some of the details are lost.

• Detailed SQL Performance Monitor: The detailed SQL performance monitor is the iSeries Navigator version of the Database Monitor, found on the system interface. This monitor saves detailed data to disk, as it is collected, and does not need to be paused or ended in order to analyze the results. Data collected by this monitor can also be used as input to Visual Explain, whereas data collected by the Summary SQL Performance Monitor cannot. However, since this monitor does save data in real time, it may have a negative impact on system performance.

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 table space backup

• Database or table space 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-14 shows how the Messages view of the Journal might look on a Windows XP server.

Figure 2-14: 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 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-15 shows how the License Center might look on a Windows XP server.

Figure 2-15: The License Center.

The Configuration Assistant

The Configuration Assistant is an interactive GUI application that allows users to configure clients so that 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 first be cataloged in the node directory of the client workstation, and information about the remote database must be cataloged in the database directory (and on the client workstation). The Configuration Assistant provides a way to catalog nodes and databases quickly without having to know the inherent complexities involved with performing these tasks. And because the Configuration Assistant maintains a list of databases to which users and applications can connect, it can act as a lightweight alternative to the Control Center in situations where the complete set of GUI tools available has not been installed.

From the Configuration Assistant, users can:

• Catalog new databases

• Work with or uncatalog existing databases

• Bind applications

• Set DB2 environment/registry variables

• Configure the DB2 Database Manager instance

• Configure ODBC/CLI parameters

• Import and export configuration information

• Change passwords

• Test connections

Figure 2-16 shows how the Configuration Assistant might look on a Windows XP server.

Figure 2-16: The Configuration Assistant.

The Design Advisor

As with other relational database systems, the primary purpose of an index in a DB2 database is to help the database engine quickly locate records stored in a table; if a table is referenced in a query and no corresponding index exists, the entire table must be scanned sequentially to locate the desired data. And if the table contains a large amount of data, such a scan can take a significant amount of time. In most cases, if an index is created for frequently used columns in a table, performance can often be greatly improved for data access operations. That's because index files are generally smaller and require less time to read than their corresponding table files, particularly as tables grow in size. Furthermore, the entire index may not need to be scanned; predicates can be applied to an index to reduce the number of rows that must actually be read.

Therefore, indexes are important because they:

• Provide a fast, efficient method for locating specific rows of data in very large tables.

• Provide a logical ordering of the rows of a table. Data is stored in a table in no particular order; when indexes are used, the values of one or more columns can be sorted in ascending or descending order. This is very beneficial when processing queries that contain ORDER BY and GROUP BY clauses.

• Improve overall query performance. If no index exists on a table, a table scan must be performed for each table referenced in a query. The larger the table, the longer a table scan takes because a table scan requires each table row to be accessed sequentially.

• Can be used to enforce the uniqueness of records stored in a table.

• Can require a table to use clustering storage, which causes the rows of a table to be physically arranged according to the ordering of their index column values. Although all indexes provide a logical ordering of data, only a clustering index provides a physical ordering of data.

• Can provide greater concurrency in multi-user environments. Because records can be located faster, acquired locks do not have to be held as long.

However, there is a price to pay for these benefits:

• Each index created requires additional storage or disk space. The exact amount of space needed is dependent upon the size of the associated table, along with the size and number of columns contained in the index.

• Every insert and update operation performed on a table requires additional updating of the indexes associated with that table. This is also true when data is bulk-loaded into a table using DB2's LOAD utility.

• Each index potentially adds an alternative access path that the DB2 optimizer must consider when generating the optimum access plan to use to resolve a query. This in turn increases compilation time when static queries are embedded in an application program.

So how do you decide when having an index would be beneficial and how do you determine what indexes should exist? And how do you decide whether to use materialized query tables (MQTs), partitioning, or both to help improve index performance? Even if you have a lot of experience with database and database application design, the task of selecting which indexes, MQTs, clustering dimensions, or database partitions to create for a complex workload can be quite daunting. That's where the Design Advisor comes in.

The Design Advisor is a special tool that is designed to capture specific information about typical workloads (queries or sets of SQL operations) performed against your database and recommend changes based upon the information provided. When given a set of SQL statements in a workload, the Design Advisor will make recommendations for:

• New indexes

• New materialized query tables (MQTs).

• Conversions of base tables to multidimensional clustering (MDC) tables

• Redistribution of table data

• Deletion of indexes and MQTs that are not being used by the specified workload

You can have the Design Advisor implement some or all of these recommendations immediately or arrange for them to be applied at a later time. Furthermore, the Design Advisor can be used to aid in the design of a new database or to improve performance of a database that is already in operation. For example, in designing a database, the Design Advisor can be used to:

• Generate design alternatives of a partitioned database environment and of indexes, MQTs, and MDC tables.

• Determine the best database partitioning strategy to use (in a partitioned environment).

• Assist in migrating from another database product to a multiple-partition DB2 database.

Once a database is in production, the Design Advisor can be used to:

• Improve performance of a particular SQL statement or workload

• Improve general database performance, using the performance of a sample workload as a gauge

• Improve performance of the most frequently executed queries, for example, as identified by the Activity Monitor

• Determine how to optimize the performance of a new key query

• Respond to Health Center recommendations regarding shared memory utility or sort heap problems encountered by a sort-intensive workload

• Find objects such as indexes and MQTs that are not used in a workload

• Assist in migrating from a single-partition DB2 database to a multiple-partition DB2 database

• Evaluate indexes, MQTs, MDC tables, or database partitioning strategies that have been generated manually

Figure 2-17 shows how the second page of the Design Advisor looks on a Windows XP server.

Figure 2-17: The second page of the Design Advisor wizard.

The Command Line Processor

The Command Line Processor (CLP) is a text-oriented application that allows users to issue DB2 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 command or SQL statement, preceded by the characters db2, followed by a space, 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) for each command/SQL statement entered. 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 mode, 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 SQL statements to be executed have been stored in an ASCII-format 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-18 shows how the Command Line Processor looks on a Windows XP server when it is run in interactive input mode.

Figure 2-18: 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 from the Command Line Processor prompt (when the Command Line Processor is run in interactive input mode).

The Developer Workbench

When you set up a remote DB2 database server and access it from one or more DB2 client workstations, you have, in essence, established a basic DB2 client/server environment. In such an environment, each time an SQL statement is executed against the database on the remote server, the statement itself is sent through a network from the client workstation to the database server. The database server then processes the statement, and the results are sent back, again through the network, to the client workstation. (This means that two messages must go through the network for every SQL statement executed.)

To take advantage of this architecture, client/server application development focuses on breaking an application into two parts, storing those parts on two platforms (the client and the server), and having them communicate with each other as the application executes. This allows the code that interacts directly with a database to reside on a database server or midrange computer, where computing power and centralized control can be used to provide quick, coordinated data access. At the same time, the application logic can reside on one or more smaller (client) workstations so that it can make effective use of all the resources the client workstation has to offer without causing a bottleneck at the server.

If you have an application that contains one or more transactions that perform a relatively large amount of database activity with little or no user interaction, each transaction can be stored on the database server as a stored procedure. With a stored procedure, all database processing done by the transaction can be performed directly at the database server. Because a stored procedure is invoked by a single SQL statement, fewer messages have to be transmitted across the network-only the data that is actually needed at the client workstation has to be sent across.

As you might imagine, the complexity of developing, debugging, and deploying stored procedures increases as the amount of work a stored procedure is expected to do increases. However, this complexity can be greatly reduced when stored procedures are developed using a special tool known as the Developer Workbench.

The Developer Workbench is an Eclipse-based, comprehensive development environment that can be used to create, edit, debug, deploy, and test DB2 stored procedures and user-defined functions. The Developer Workbench can also be used to develop SQLJ applications and to create, edit, and run SQL statements and XQuery expressions. Figure 2-19 shows how the Developer Workbench looks when it is first activated on a Windows XP server.

Figure 2-19: The Developer Workbench.

By using the Developer Workbench, application developers can focus on creating and testing stored procedures and user-defined functions without having to concern themselves with the details of registering, building, and installing the procedures and functions on a DB2 server. When the Developer Workbench is used to build a stored procedure or user-defined function, it compiles the source code on the client workstation (Java routines) or server (SQL routines), copies the source code and resulting library to the server, and registers the routine in the system catalog of the database being used.