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.
Note - Commands issued from the DB2 CLP can also be issued from the DB2 Command Editor or from most operating system's command line processor (CLP) using the db2 prefix.
Figure 123 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.
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.
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.
Using Visual Explain to look at how your query is run by DB2
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 124 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.
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
125, 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.
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
126), and in tuning queries for optimal utilization of the data server's resources.
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.
Using the DB2 tools to manage storage activity
You can see in Figure 127 that the SYSCATSPACE table space is running out of space as it's 98 percent used.
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.
This chapter discussed the DB2 products for LUW. There are a number of offerings available, including:
These products provide the flexibility to execute database applications running on pervasive devices up to multi-node clusters. DB2 provides support for the commonly used communication protocols.
Each of the DB2 data server editions comes with a set of purchasable add-on feature packs that you can use to extend the capabilities of the core data server. There are also number of add-on tools and products that you can also buy for DB2, including DB2 Query Patroller and a myriad of DBA-focused tools such as High Performance Unload, the DB2 Recovery Expert, and more.
SQL-based replication is integrated into all DB2 for Linux, UNIX, and Windows servers. Replication can be used to move data between members of the DB2 family, or from and to members of the DB2 family and non-DB2 data servers (like Oracle, SQL Server, and more) if you're using WebSphere Replication Edition. DB2 Connect is used to access DB2 data that resides on DB2 for i5/OS, VM/VSE, and z/OS operating systems, and it comes with federated capabilities that can also be added to a DB2 data server installation.
DB2 9 includes a number of application development enhancements, including the DB2 Developer Workbench and integration into the world's most popular IDEs such as Rational Application Developer, Zend Core, and Microsoft Visual Studio 2005.
Significant new features have been added to make DB2 easier to manage. SMART technology (Self-managing and Resource Tuning) has been integrated into a number of DB2 components, including installation, configuration, utilities, problem determination and resolution, and availability. This is part of IBM's autonomic computing initiative and new features will continue to be added to make DBAs more productive.
This chapter also introduced some of the graphical and command line tools available in DB2. The Command Line Processor (CLP) is a text-based application that allows you to enter DB2 commands and SQL statements and is found in all DB2 products. From the desktop, an administrator can configure remote and local systems, administer instances and databases, and create database objects graphically using the Control Center or the Configuration Assistant. Tools like the DB2 Health Center and DB2 Activity Monitor also help manage DB2 environments while export support is provided in the form of Wizards and Advisors. In the remaining chapters, additional DB2 functions and tools will be examined for how they assist the end user, application developer, and administrator.
Copyright © 2007 Pearson Education. All rights reserved.