![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Performance optimization with Optim™ Performance Manager and DB2 Workload Manager
DB2 AESE replaces manual and costly performance management tasks with an integrated approach to identifying, diagnosing, solving, and preventing performance issues. It combines early-warning notification and deep database analysis tools to support problem resolution as well as proactive workload management and trend analysis. IBM InfoSphere Optim Performance Manager, which is included in DB2 AESE, provides 24x7 monitoring and performance warehousing for DB2 for Linux®, UNIX®, and Windows® V9 databases, including single partition, multi-partition, and pureScale™ databases, to improve overall quality of service. The web user interface (UI) provides use-anywhere monitoring, alerting, and diagnosis of potential performance bottlenecks, and enables access to health summaries and trending reports.
The Optim performance management solution offers the following integrated problem-solving approach:
1. Identify — Monitor key performance indicators and receive alerts of potential problems.
Optim Performance Manager comes with templates for monitoring analytical and transactional systems, including SAP. Users can customize thresholds and the details of performance data collection with at-a-glance alerts on the cross-database health summary and with notifications sent through email or sent to Tivoli® or other operational consoles.
Figure 1 shows the health summary for a three-database environment. Green, yellow, and red icons indicate database health and performance. Clicking a yellow (warning) or red (critical) icon opens the associated alert detail and enables drill-down to the detailed dashboard.
Figure 1. A sample health summary dashboard
2. Diagnose — Drill down into problem detail and analyze captured data.
After a problem is identified, Optim Performance Manager directs you to the content for diagnosing performance issues. It includes diagnostic dashboards for all major operational components to help determine the root cause of an issue, without relying on manual processes for diagnostic collection. Historical data makes it easy to analyze past problems or to compare performance over a period of time (for example, this week vs. last week).
Figure 2 shows the diagnostic dashboards with details for component areas. The warning and critical icons direct you to the KPI violations, and the surrounding information provides additional context and detail. The time slider bar allows you to adjust the time and direction of data.
Figure 2. Diagnostic dashboards
3. Solve — Resolve query, database design, and database resource issues.
Complementary tools can help DBAs and developers resolve issues. The Active SQL dashboard identifies high-cost queries. Select a query and click Tune to transfer the query to IBM Data Studio to format the query, analyze the access plan, or check whether statistics are current and optimal. Use Optim Database Administrator (included with AESE) to adjust DB2 parameters, alter objects, and analyze the impact of potential changes. Figure 3 shows the Active SQL dashboard with a selected query that was transferred to Optim Query Tuner for analysis and advice.
Figure 3. Active SQL dashboard
4. Prevent — Align resource allocation with business priorities and analyze trends to plan for growth.
DB2 Workload Manager gives staff control to ensure that the most critical workloads gain priority access to system resources. Optim Performance Manager provides analysis, reports, and UIs to support effective workload management deployment. Plus, the performance warehouse and built-in reporting show memory usage and storage growth trends to help keep pace with business growth. Figure 4 shows reports in Optim Performance Manager that are used to gauge workload definitions and to determine assignment to service classes.
Figure 4. Optim Performance Manager reports
DB2 AESE includes the following additional IBM InfoSphere Optim solutions that can help you develop and administer business-critical databases throughout their life cycle:
- IBM Optim Database Administrator V2.2.3 — Helps save time and reduce errors in managing databases and database change. You can perform common database administration tasks and simplify the process of comparing, identifying, analyzing, and implementing database schema changes. This tool automatically generates commands to implement changes, manage dependent objects, and take actions to address any side effects caused by schema changes.
- IBM Optim Development Studio V2.2.1 — An IDE that speeds application development
and deployment while increasing data access performance and manageability. You can develop for DB2,
Oracle, and Informix environments, and use the included PL/SQL capabilities. It provides
wizard-driven programming approaches; query and stored procedure development; and drag-and-drop
capabilities, creating web services that do not require coding.
Java™ data access layers are created with best practices-based point-and-click development. The SQL outline feature isolates SQL for review and enables impact analysis by correlating SQL with source code, database objects, andALTERrequests.
The underlying pureQuery technology gathers application metadata for use in impact analysis, privacy analysis, problem isolation, and hot-spot analysis. Manage the captured SQL in the Optim Development Studio SQL outline to replace SQL without changing the application.
You can combine Data Studio, Optim Development Studio, Optim Database Administrator, and Optim Query Tuner in any combination into a seamless, common environment that tailors capabilities to meet the various roles within your organization.
Storage optimization using compression
Compression features in DB2 AESE provide multiple algorithms for automatic row-level, index, and temporary table compression, as well as intelligent compression of XML and large objects. The following compression features are now available:
- Automatic row-level compression — After reaching a predetermined table size, DB2 creates a dictionary based on
sampling and data. All data inserted or updated after dictionary creation is compressed.
(Tables must be defined with
COMPRESS YESto be eligible.) This feature removes the need for the DBA to runINSPECTor to reorganize all tables manually. - Optional automatic index compression — Unlike data or table compression, which uses a dictionary, index compression uses up to three different algorithms designed to provide the best compression and the least performance impact for index compression:
- Prefix compression is used on similar key values.
- RID list compression allows non-unique indexes with entries on the same page to provide only the delta between like keys on the same page, rather than store a complete RID for each entry.
- Slot directory compression allows for variable lengths. An index set with
COMPRESS YESis compressed when the index is created. An altered index cannot be compressed until the index is reorganized or rebuilt. - Temporary table compression — Temporary tables created when users sort data
using an
order by,group by, orjoinlarge tables in their queries are automatically compressed, resulting in less disk space requirements and better performance. - XML compression — Using XML compression in the XDA object when inlined in the row provides storage savings.
- Large object (LOB) inlining — Small LOBs can be stored in the formatted rows of the base table. This inlining is dependent on page size and is analogous to XML inlining for XML data.
- Replication (Q-Rep, SQL-Rep, and CDC) with compression — The db2ReadLog API decompresses log data before sending log records.
DB2 AESE is a complete database solution in a single package for one low price. It provides a host of optimization, development, and management tools for use with data-intensive applications. This article highlighted two of the more important capabilities that come as standard features in DB2 AESE. There are additional tools available at an additional purchase to further enhance your control of data-intensive DB2 environments. To learn more about these tools, as well as the tools we have discussed here, see the resources listed below.
Learn
-
Be sure to read Part
2 of this series.
-
IBM DB2 Advanced Enterprise Server
Edition has more information.
- Don't miss the IBM DB2 e-kit for Database
Professionals.
- Learn more about IBM Tools for DB2.
- Be sure to check out IBM Optim Performance Manager
Extended Edition for DB2 for Linux, Unix, and Windows.
- Learn more about IBM Optim Database Administrator
for DB2 for Linux, Unix and Windows.
- Check out IBM Optim Development Studio.
- And developerWorks has more information
about IBM Optim Tools.
- "What's new in Optim Performance Manager Extended Edition for DB2 for
Linux, UNIX, and Windows, developerWorks article" has more
information.
- Read the IBM Redbooks® publications titled "IBM Optim Performance Manager for DB2 for Linux, UNIX, and
Windows" (SG24-7925) and "Using Integrated Data Management To Meet Service Level
Objectives" (SG24-7769).
- Learn more about Information Management at the developerWorks Information Management
zone. Find technical documentation,
how-to articles, education, downloads, product information, and
more.
- Stay current with
developerWorks technical events and webcasts.
- Follow developerWorks on
Twitter.
Get products and technologies
- Build your next
development project with
IBM trial software,
available for download directly from developerWorks.
Discuss
- Participate in the discussion forum.
- Check out the
developerWorks
blogs and get involved in the
developerWorks community.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
