This chapter is from the book
This chapter is from the book
This chapter is from the book
DB2 Administration
DB2 DBAs have a number of graphical-based tools they can use to manage and administer DB2 data servers. Alternatively, a DBA can also use a script-based approach to administer the data environment using the DB2 tools to create and schedule the scripts. This section briefly details the main graphical tools available with DB2.
Control Center
The Control Center is the central point of administration for DB2. The Control Center provides DBAs with the tools necessary to perform typical database administration tasks. It allows easy access to other server administration tools, gives a clear overview of the entire system, enables remote database management, and provides step-by-step assistance for complex tasks.
The All Systems object represents both local and remote data servers. To display all the DB2 systems that your system knows about, expand the object tree by clicking on the plus sign (+) next to All Systems. In Figure 1-17, you can see a DB2 data server called PAULZ contains a DB2 instance called DB2, in which the database TEST is located.
)
Figure 1-17 The DB2 Control Center
When you highlight an object, details about that object are shown in the Contents Pane.
The main components of the Control Center are:
- Menu Bar—Used to access Control Center functions and online help.
- Tool Bar—Used to access other DB2 administration tools, such as the Command Editor, Task Center, and more.
- 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 in 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.
- Task Window—Lists the most common tasks associated with the selected object in the Object Pane. In Figure 1-17 you can see that since a database is highlighted, common tasks and administrative functions related to it are in this window.
- Hover Help—Provides a short description for each icon on the toolbar as you move the mouse pointer over the icon.
The Control Center also comes with personality control that you can use to adjust the view and functions available from the Control Center's tree view of your data server. For example, you can limit the Object Pain view to show just Tables or Views, as well as limit the actions you can perform from the context-sensitive right-click menu options. You can customize your Control Center personalities using Tools → Tools Settings → Customize Control Center.
DB2 Replication Center
The DB2 Replication Center is a graphical tool that allows DBAs to quickly set up and administer all forms of data replication, including the options offered by WebSphere Replication Server. The main functions in setting up a replication environment can be performed with this tool, including:
- Registering replication sources
- Monitoring the replication process
- Operating the CAPTURE and APPLY programs
- Defining alerts
You can use the Replication Center to set up all kinds of DB2 replications, as shown in Figure 1-18.
)
Figure 1-18 The DB2 Replication Center
Other Tools Available from the Control Center
By using the Control Center tool bar, you can access a host of other graphical administration tools to help you manage and administer databases in your environment:
- Satellite Administration Center—Used to manage groups of DB2 data servers through push/pull management scripts and more.
- Command Editor—Provides an interactive window that facilitates the building of SQL statements or DB2 commands, the viewing of execution results, and explain information. This graphical command utility is often the preferred method for text commands as it provides enormous flexibility and functionality.
- Task Center—Used to create, schedule, and manage scripts that can contain SQL statements, DB2 commands, or operating systems commands.
- Journal—Keeps a record of all script invocations, all DB2 messages, and the DB2 recovery history file for a database. It is used to show the results of a job, to display the contents of a script, and also to enable or disable scheduled jobs.
- License Center—Used to manage licenses and check how many connections are used.
- DB2 Web Tools—Allows the DBAs to use an HTTP client to remotely execute SQL statements, DB2 commands, or operating system commands against a DB2 server. Essentially, the Health Center and the Command Editor are exposed in this tool set.
DB2 Health Center
The DB2 Health Center (Figure 1-19) is the central point of information with respect to managing the health of your DB2 system. When you install a DB2 9 data server, out of the box it automatically monitors 27 (and counting) health indicators that proactively monitor the health of your data server. The DB2 Health Center implements a management-by-exception model whereby alerts are surfaced when Warning or Alarm thresholds are breached. Although DB2 configures these thresholds for you out of the box, you can configure them yourself as well as specify scripted actions to occur in the event of an alert.
)
Figure 1-19 The DB2 Health Center
You don't have to use the DB2 Health Center to work with DB2's health information or set triggered actions to occur on threshold breaches. You can use a number of SQL-based user defined functions to work with the DB2 health facilities from the command line.
The DB2 Health Center can monitor indicators across the following health categories:
- Application concurrency (e.g., Deadlock rate)
- DBMS (e.g., Instance operational state)
- Database (e.g., Database operational state)
- Logging (e.g., Log utilization)
- Memory (e.g., Monitor heap utilization)
- Sorting (e.g., Percentage of sorts that overflowed)
- Table space storage (e.g., Table space utilization)
- Database maintenance (e.g., Reorganization required)
- High-Availability Disaster Recovery (e.g., HADR log delay)
- Federated (e.g., Nickname status)
- Package and catalog caches (e.g., Package cache hit ratio)
The DB2 Health Center's graphical user interface allows DBAs to select database objects and drill down on its details, current alerts, and the recommended actions. The DB2 Health Center also includes the DB2 Recommendation Advisor that can be used to walk through potential fixes to alerts and alarms raised in this facility.
DB2 Configuration Assistant
The DB2 Configuration Assistant (DB2 CA) lets you maintain a list of databases to which your applications can connect, as well as manage and administer those connections. It is mostly used for client connectivity configuration. You can start the DB2 CA by entering the db2ca command from your operating system's command-line shell or from the DB2 folder in the Start menu. Some of the functions available in the DB2 CA are shown in Figure 1-20.
)
Figure 1-20 The DB2 Configuration Assistant
Using the DB2 CA, you can work with existing databases, add new ones, bind applications, set client configuration and registry parameters (also shown in Figure 1-20), test connectivity, and import and export configuration profiles.
The DB2 CA's graphical interface makes these complex tasks easier by means of the following:
- Wizards that help you perform certain tasks
- Dynamic fields that are activated based on your input choices
- Hints that help you make configuration decisions
- The Discovery feature, which can retrieve information that is known about databases that reside on your network
The DB2 CA's Discovery feature is very useful because it allows you to add a database connection without having to know the syntax of DB2 CATALOG NODE and DB2 CATALOG DATABASE commands, or even the location information of the remote data server.
As you can see in Figure 1-20, the DB2 CA displays a list of the databases to which your applications can connect from the workstation where it was started. Each database is identified first by its database alias, then by its name. You can use the Change Database Wizard to alter the information associated with databases in this list. The CA also has an Advanced view, which uses a notebook to organize connection information by the following objects:
- Systems
- Instance nodes
- Databases
- Database Connection Services (DCS) for System i and System z databases
- Data sources
Advisors and Wizards
DB2 comes with a set of Wizards and Advisors to help you with day-to-day tasks. Wizards can be very useful to both novice and expert DB2 users. Wizards help you complete specific tasks by taking you through each task one step at a time and recommending settings where applicable. Wizards are available through both the Control Center and the Configuration Assistant.
There are wizards for adding a database to your system, creating a database, backing up and restoring a database, creating tables, creating table spaces, configuring two-phase commit environments, configuring database logging, updating your documentation, setting up a High-Availability Disaster Recovery (HADR) pair, tuning your performance, and more.
Figure 1-21 shows a portion of the Create Database wizard in DB2 9.
)
Figure 1-21 The Create Database wizard
Advisors are special types of wizards that do more than provide assistance in completing a task. Traditional wizards take you step-by-step through a task, simplifying the experience by asking important questions or generating the complex command syntax for the action you want to perform. When a wizard has more intelligence than just task completion and can offer advisory-type functions, DB2 calls them advisors. They operate just like wizards but are intelligent enough (having some pretty complex algorithms) to generate advice based on input factors such as workload or statistics. Advisors help you with more complex activities, such as tuning tasks, by gathering information and recommending options that you may not have considered. You can then accept or reject the advisor's advice. You can call advisors from context menus in the DB2 administration tools, from APIs, and the command-line interface.
Advisors are part of the IBM autonomic computing effort, which aims to make software and hardware more SMART (self-managing and resource tuning). There are three main advisors in DB2 9: the DB2 Configuration Advisor, the DB2 Recommendation Advisor, and the DB2 Design Advisor.
The DB2 Configuration Advisor is automatically run for you whenever you create a new database in DB2 9. It can configure up to 35 instance-level and database-level parameters for you based on responses to high-level questions that describe the data server environment and type of application you plan to support.
The DB2 Recommendation Advisor, as previously mentioned, is closely associated with the DB2 Health Center and is used to offer solutions to raised alerts and alarm breeches in this facility.
The DB2 Design Advisor is used to identify objects such as materialized query tables (MQTs), multidimensional clustering tables (MDCs), indexes, and partitioning keys that could optimize a given SQL workload. The DB2 Design Advisor can also identify indexes that aren't needed as well (shown in Figure 1-22).
)
Figure 1-22 The DB2 Design Advisor
When using this advisor it's important to note that the suggestions it provides are based on a submitted workload. If you've left out significant portions of a workload, the answer will not reflect the impact of the missing workload. In addition, the DB2 Design Advisor gives you the ability to heavily weight SQL statements in a submitted workload over others, giving you more control with respect to how the DB2 Design Advisor will recommend the creation of performance objects with respect to your real workload characteristics.
The DB2 Command Line Processor
The DB2 Command Line Processor (DB2 CLP) is a component common to all DB2 products. It is a text-based application that can be used to issue interactive SQL statements or DB2 commands. For example, you can create a database, catalog a database, and issue dynamic SQL statements all from the DB2 CLP. Your DB2 statements and commands can also be placed in a file and executed in a batch environment, or they can be entered in interactive mode.
Figure 1-23 shows an example of using the DB2 CLP to enter DB2 commands. The DB2 CLP operates in an "interactive" mode and therefore does not require the db2 prefix associated with entering DB2 commands.
)
Figure 1-23 Using the DB2 CLP to enter commands
The DB2 CLP is provided with all DB2 and DB2 Connect products. All SQL statements issued from the DB2 CLP are dynamically prepared and executed on the data server. The output, or result, of the SQL query is displayed on the screen by default. All of the DB2 commands that you can enter in the DB2 CLP are documented in the DB2 Command Reference. You learn more about the DB2 CLP and how to enter DB2 commands from an operating system's native CLP in Chapter 2.
Visual Explain
Other graphical tools can be used for tuning or monitoring performance. Visual Explain is a graphical utility that provides a visual representation of the access plan that DB2 uses to execute an SQL statement.
Visual Explain can be invoked from the Control Center, the DB2 CLP (though the output in textual and not graphical), the DB2 DWB, or from the Command Editor.
Figure 1-24 shows the type of information that is displayed. You can see that the query is accessing two tables and an approximation of the cost of each step of this query is also provided in the Visual Explain output. The estimated query costs represent the complexity and resource usage expected for a given SQL query. There are many more details and features provided in Visual Explain under the Statement menu option.
)
Figure 1-24 Using Visual Explain to look at how your query is run by DB2
DB2 Query Patroller
DB2 Query Patroller (DB2 QP) is an add-on product that can be used to control and monitor query execution, as well as work with queries to prioritize and schedule user queries based on user profiles and cost analysis performed on each query. Large queries can be put on hold and scheduled for a later time during off-peak hours. Queries with high priority (based on user profiles) are promoted to the top of the schedule.
In addition, DB2 QP monitors resource utilization statistics. DB2 QP can use this information to determine the load distribution of the system, which can allow it to balance the number of users allowed to submit queries at any given time.
DB2 QP greatly improves the scalability of a data warehouse by allowing hundreds of users to safely submit queries on multi-terabyte class data servers. Its components span the distributed environment to better manage and control all aspects of query submission. The services provided by this product act as an agent on behalf of the end user. It prioritizes and schedules queries so that query completion is more predictable and system resources are more efficiently utilized. DB2 QP obtains query costs from the DB2 Optimizer and then schedules them for execution—this means that DB2 QP is tightly integrated with DB2 engine.
DB2 QP can also be used to set individual user and user class priorities as well as user query limits. This enables the data warehouse to deliver the needed results to its most important users as quickly as possible. If desired, an end user can choose to receive notice of scheduled query completion through e-mail.
Finally, as discussed earlier in this chapter, DB2 QP offers the ability to perform charge back for data server usage to specific departments identified by accounting strings on the connection context. For example, if marketing is using the data warehouse three times more than accounting, they should pay three times the charge back. In Figure 1-25, you can see one of many canned reports that come with DB2 QP. This one shows the number of statements run by month. You can drill down into this view to the minute and second interval, as well as access a host of other reports such as average execution time, average wait time, average queue time, and more.
)
Figure 1-25 Performing charge back with DB2 Query Patroller
Database Monitoring Tools
The Snapshot Monitor captures database information at specific intervals. The interval time and data represented in the performance graph can be configured. The Snapshot Monitor can help analyze performance problems, tune SQL statements, and identify exception conditions based on limits or thresholds.
The Event Monitor captures database activity events as defined by the event monitor definition. Event Monitor records are usually stored on disk and then analyzed after the data has been captured. The Event Analyzer graphical tool provided with DB2 can be used to analyze the captured data.
The Activity Monitor help you improve the efficiency of database performance monitoring, problem determination, and resolution. By tracking a set of predefined monitor data, the Activity Monitor allows you to quickly locate the cause of a problem. You can then take direct action to resolve the problem or invoke another tool for further investigation. The Activity Monitor can help you monitor application performance, application concurrency, resource consumption, and SQL statement usage. It can also assist you in diagnosing performance problems such as lock waiting situations (as shown in Figure 1-26), and in tuning queries for optimal utilization of the data server's resources.
)
Figure 1-26 Using the Activity Monitor to diagnose a lock waiting problem
The DB2 Storage Management Tool
The DB2 Storage Management Tool can be used to monitor the storage state of a database. You can use this facility to take storage snapshots for a database or a table space. When a database or snapshot is taken, statistical information is collected for all the table spaces defined in the given database (you can also snapshot at the table space level).
The Storage Management Tool enables you to set thresholds for data skew, space usage, and index cluster ratio. If a target object exceeds a specified threshold, the icons beside the object and its parent object in the Storage Management view are marked with a warning flag or an alarm flag—similar to the DB2 Health Center.
You can see in Figure 1-27 that the SYSCATSPACE table space is running out of space as it's 98 percent used.
)
Figure 1-27 Using the DB2 tools to manage storage activity
The DB2 Aftermarket Tools
There are two kinds of tools for DB2: those that are free and those that are add-ons that can be purchased separately. The free tools come as part of a DB2 installation and can be launched from the Control Center, the Configuration Assistant, or on their own. A separate set of purchasable tools are available to help ease a DBA's task of managing and recovering data, tuning performance, and more. The DB2 suite of these tools includes (www-306.ibm.com/software/data/tools/mptools.html):
- DB2 Change Management Expert—Improves DBA productivity and reduces human error by automating and managing complex DB2 structural changes.
- Data Archive Expert—Responds to legislative requirements like Sarbanes-Oxley by helping DBAs move seldom-used data to a less costly storage medium without additional programming.
- DB2 High Performance Unload—Maximizes DBA productivity by reducing maintenance windows for data unloading and repartitioning.
- DB2 Performance Expert—Makes DBAs more proactive in performance management to maximize database performance. (This tool was discussed in the "Add-on Feature Packs for DB2 Enterprise Edition" section earlier in this chapter).
- DB2 Recovery Expert—Protects your data by providing quick and precise recovery capabilities, including operations only provided in this tool like SQL statement undo generation, object recovery, and more.
- DB2 Table Editor—Keeps business data current by letting end users easily and securely create, read, update, and delete (CRUD) data.
- DB2 Test Database Generator—Quickly creates test data and helps avoid liabilities associated with data privacy laws by protecting sensitive production data used in test.
- DB2 Web Query Tool—Broadens end user access to DB2 data using the Web and handheld devices.