The DB2 Command Line Processor (DB2 CLP) is a component regular to all DB2 items. it is a text-primarily based application that can be used to challenge interactive SQL statements or DB2 instructions. as an instance, that you would be able to create a database, catalog a database, and concern dynamic SQL statements all from the DB2 CLP. Your DB2 statements and instructions can also be positioned in a file and carried out in a batch environment, or they will also be entered in interactive mode.
be aware - commands issued from the DB2 CLP can also be issued from the DB2 Command Editor or from most operating device's command line processor (CLP) using the db2 prefix.
determine 1–23 indicates an instance of the use of the DB2 CLP to enter DB2 commands. The DB2 CLP operates in an "interactive" mode and for this reason doesn't require the db2 prefix associated with coming into DB2 instructions.
figure 1–23Using the DB2 CLP to enter instructions
The DB2 CLP is supplied with all DB2 and DB2 join items. All SQL statements issued from the DB2 CLP are dynamically prepared and finished on the records server. The output, or outcomes, of the SQL query is displayed on the monitor through default. all of the DB2 instructions so that you can enter within the DB2 CLP are documented within the DB2 Command Reference. You study greater concerning the DB2 CLP and how to enter DB2 instructions from an working equipment's native CLP in Chapter 2.visible clarify
other graphical equipment will also be used for tuning or monitoring efficiency. visible explain is a graphical utility that gives a visible illustration of the access plan that DB2 uses to execute an SQL commentary.
determine 1–24Using visual clarify to study how your query is run by using DB2
visual explain can be invoked from the manage center, the DB2 CLP (notwithstanding the output in textual and not graphical), the DB2 DWB, or from the Command Editor.
determine 1–24 shows the class of counsel it truly is displayed. that you could see that the query is having access to two tables and an approximation of the cost of every step of this question is additionally provided within the visual explain output. The estimated query fees represent the complexity and aid utilization expected for a given SQL query. there are lots of extra details and features supplied in visible explain under the statement menu alternative.DB2 question Patroller
DB2 question Patroller (DB2 QP) is an add-on product that can also be used to manage and display screen question execution, in addition to work with queries to prioritize and schedule user queries according to person profiles and price evaluation carried out on each question. tremendous queries will also be placed on hold and scheduled for a later time all through off-top hours. Queries with high precedence (according to person profiles) are promoted to the accurate of the time table.
in addition, DB2 QP displays resource utilization statistics. DB2 QP can use this advice to check the weight distribution of the device, that may enable it to stability the variety of clients allowed to submit queries at any given time.
DB2 QP vastly improves the scalability of a knowledge warehouse by way of allowing a whole bunch of clients to safely submit queries on multi-terabyte type statistics servers. Its add-ons span the allotted environment to enhanced manage and manage all aspects of query submission. The services offered by means of this product act as an agent on behalf of the end person. It prioritizes and schedules queries in order that question completion is more predictable and device supplies are extra efficaciously utilized. DB2 QP obtains question expenses from the DB2 Optimizer and then schedules them for execution — this capacity that DB2 QP is tightly integrated with DB2 engine.
DB2 QP can even be used to set individual person and user classification priorities in addition to consumer question limits. This makes it possible for the statistics warehouse to deliver the necessary consequences to its most critical users as right now as feasible. If desired, an conclusion person can choose to obtain word of scheduled query completion via email.
finally, as discussed previous during this chapter, DB2 QP offers the ability to operate can charge returned for facts server usage to certain departments identified through accounting strings on the connection context. as an example, if advertising is the usage of the information warehouse 3 times more than accounting, they should still pay 3 times the charge lower back. In determine 1–25, you could see one in all many canned reviews that include DB2 QP. This one shows the variety of statements run with the aid of month. you could drill down into this view to the minute and 2nd interval, as well as entry a bunch of other reports equivalent to ordinary execution time, normal wait time, normal queue time, and more.
determine 1–25Performing can charge back with DB2 query PatrollerDatabase Monitoring equipment
The picture computer screen captures database counsel at specific intervals. The interval time and records represented in the efficiency graph can be configured. The photograph monitor can assist analyze performance issues, tune SQL statements, and establish exception circumstances in accordance with limits or thresholds.
The adventure display screen captures database recreation activities as defined through the event monitor definition. experience computer screen records are continually kept on disk and then analyzed after the records has been captured. The adventure Analyzer graphical device supplied with DB2 will also be used to research the captured statistics.
The exercise computer screen help you enhance the efficiency of database performance monitoring, problem resolution, and backbone. by monitoring a set of predefined computer screen data, the pastime monitor lets you straight away find the explanation for an issue. you could then take direct motion to unravel the difficulty or invoke a further device for additional investigation. The undertaking computer screen can assist you monitor utility performance, software concurrency, resource consumption, and SQL commentary utilization. it could possibly additionally assist you in diagnosing efficiency complications corresponding to lock ready situations (as proven in determine 1–26), and in tuning queries for top-rated utilization of the information server's substances.
determine 1–26Using the recreation display screen to diagnose a lock ready issueThe DB2 Storage management tool
The DB2 Storage administration tool can be used to display screen the storage state of a database. that you can use this facility to take storage snapshots for a database or a table space. When a database or image is taken, statistical information is accumulated for all of the table areas described in the given database (which you can also photograph on the desk space degree).
The Storage management tool permits you to set thresholds for records skew, area usage, and index cluster ratio. If a goal object exceeds a unique threshold, the icons beside the thing and its dad or mum object within the Storage administration view are marked with a warning flag or an alarm flag — akin to the DB2 fitness middle.
determine 1–27Using the DB2 equipment to manage storage activity
which you could see in determine 1–27 that the SYSCATSPACE desk house is running out of area as it's 98 p.c used.The DB2 Aftermarket equipment
There are two types of tools for DB2: those that are free and people that are add-ons available for purchase one after the other. The free equipment come as part of a DB2 installation and may be launched from the manage center, the Configuration Assistant, or on their personal. A separate set of purchasable tools can be found to support ease a DBA's task of managing and convalescing information, tuning performance, and more. The DB2 suite of those tools contains (www-306.ibm.com/software/statistics/equipment/mptools.html):
DB2 alternate administration knowledgeable — Improves DBA productivity and reduces human error by using automating and managing complex DB2 structural changes.
facts Archive knowledgeable — Responds to legislative requirements like Sarbanes-Oxley by means of assisting DBAs move seldom-used records to a lower priced storage medium with out additional programming.
DB2 excessive efficiency dump —Maximizes DBA productiveness through decreasing renovation home windows for records unloading and repartitioning.
DB2 efficiency professional — Makes DBAs extra proactive in efficiency administration to maximize database efficiency. (This device become discussed in the "Add-on feature Packs for DB2 commercial enterprise version" area prior during this chapter).
DB2 recovery skilled — Protects your information by way of offering short and specific restoration capabilities, together with operations simplest supplied in this device like SQL observation undo technology, object recuperation, and greater.
DB2 desk Editor — keeps enterprise statistics existing through letting conclusion clients simply and securely create, study, replace, and delete (CRUD) statistics.
DB2 test Database Generator — quickly creates verify information and helps avoid liabilities linked to facts privacy legal guidelines via retaining delicate production information used in look at various.
DB2 net query device — Broadens end user entry to DB2 facts the use of the web and handheld gadgets.
This chapter discussed the DB2 products for LUW. There are a few choices attainable, together with:
These items give the pliability to execute database applications running on pervasive instruments up to multi-node clusters. DB2 provides guide for the customary communication protocols.
each and every of the DB2 data server editions comes with a set of purchasable add-on function packs that you can use to prolong the capabilities of the core facts server. There are additionally number of add-on equipment and items so that you can additionally purchase for DB2, together with DB2 question Patroller and a myriad of DBA-focused equipment corresponding to high performance sell off, the DB2 restoration expert, and more.
SQL-based mostly replication is built-in into all DB2 for Linux, UNIX, and home windows servers. Replication can be used to movement statistics between members of the DB2 household, or from and to individuals of the DB2 household and non-DB2 records servers (like Oracle, SQL Server, and extra) if you are the usage of WebSphere Replication edition. DB2 connect is used to entry DB2 information that resides on DB2 for i5/OS, VM/VSE, and z/OS working techniques, and it comes with federated capabilities that may even be brought to a DB2 records server installing.
DB2 9 contains a couple of software construction enhancements, together with the DB2 Developer Workbench and integration into the realm's most everyday IDEs such as Rational utility Developer, Zend Core, and Microsoft visual Studio 2005.
big new points have been delivered to make DB2 simpler to manipulate. wise technology (Self-managing and resource Tuning) has been built-in into a few DB2 add-ons, together with installation, configuration, utilities, difficulty choice and resolution, and availability. this is a part of IBM's autonomic computing initiative and new features will proceed to be delivered to make DBAs more productive.
This chapter additionally added some of the graphical and command line equipment obtainable in DB2. The Command Line Processor (CLP) is a textual content-primarily based utility that means that you can enter DB2 instructions and SQL statements and is present in all DB2 items. From the computer, an administrator can configure faraway and local methods, administer situations and databases, and create database objects graphically using the control core or the Configuration Assistant. equipment like the DB2 fitness middle and DB2 endeavor computer screen also help manage DB2 environments while export aid is provided in the kind of Wizards and Advisors. in the closing chapters, additional DB2 capabilities and tools may be examined for how they aid the end user, software developer, and administrator.
Copyright © 2007 Pearson training. All rights reserved.