DB2 Advanced Enterprise Server Edition at a glance
DB2 AESE includes, at no additional cost, the following features and benefits:
- Storage Optimization Feature (Compression) reduces storage costs.
- IBM InfoSphere® Optim™ Performance Manager Extended Edition with Extended Insight monitors DB2 to identify problems before they affect the business, and supports proactive tuning and capacity planning to better prevent problems.
- Homogeneous Replication (Q-REP) enables seamless version-to-version migration and active-active high-availability topologies (limited to up to three databases of DB2 for LUW).
- IBM Data Studio enables developers and administrators to define, configure deploy, and manage DB2 and applications accessing DB2.
- InfoSphere Data Architect helps you discover, model, relate, and standardize diverse and distributed data assets.
- InfoSphere Optim Configuration Manager supports centralized configuration tracking and storage optimization facilitate enterprise database management and increase storage utilization.
- InfoSphere Optim Query Workload Tuner empowers DBAs to more efficiently manage performance by providing expert, actionable advice to improve the performance of SQL queries and query workloads.
- InfoSphere Optim pureQuery Runtime for LUW speeds development, improves and stabilizes performance, and mitigates SQL injection risk.
- IBM InfoSphere Federation enables cross-application, real-time data integration (limited to DB2, Informix®, and Oracle federation).
- IBM DB2 Workload Manager aligns resource allocation with business priority.
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® 10 databases, including single-partition, multi-partition, and pureScale® databases, to improve overall quality of service. The web 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 example. 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 duration of displayed data. Charts provide instant visualization of trends and can span as much data as of interest in the performance repository.
When InfoSphere Optim Configuration Manager is also deployed, DBAs can contextually launch Configuration Manager to see what configuration changes were made recently that might be related to performance degradation.
Figure 2. Diagnostic dashboards
A unique feature of Optim Performance Manager is Extended Insight. It extends monitoring beyond just the database server to understand the components of application response time across multi-tiered application stacks. Extended Insight captures the transaction when it begins at the database client and tracks its response-time characteristics across the transaction. This provides problem isolation across the application stack so DBAs can tell whether it is their issue to resolve or route to other areas. DBAs can see where transactions are spending their time: in the application, application server, client driver, network, or database and where in the database (see Figure 3). In addition, Extended Insight enables users to configure workloads that they want to track, for example, high-priority users, transactions, or applications, and monitor response-time objectives for those workloads.
Figure 3. Extended Insight dashboard
3. Solve— Resolve query, database design, and database resource issues.
Tools that complement each other can help DBAs and developers resolve issues. For example, filtering or selecting SQL from the Extended Insight dashboard gives the DBA the ability to tune it using the Tune or Tune All buttons, transferring the query(s) to Optim Query Workload Tuner. In Optim Query Workload Tuner (see Figure 4), a DBA can review the formatted query, analyze the access plan, compare access plans, and most importantly, get expert advice on query revisions, access plans, statistics or statistical views, indices, materialized query tables, and more. Results indicate the level of importance of the recommendation, present rationale and estimated improvements, and provide necessary DDL or RUNSTATS statements, making advice actionable. Query Tuner supports what-if analyses before deployment using virtual indices so you can see the effect of candidate indices on the access plans, estimate performance improvements, as well as estimated disk requirements. And most importantly, Query Workload Tuner is designed to take into account the entire workload. Thus, it balances the cost of additional statistics collection or additional indices with the benefit to the overall workload.
Figure 4. View query tuning advice to improve application performance
InfoSphere Optim pureQuery Runtime can also help improve DB2 throughput and mitigate SQL injection risk. DBAs can revise poorly written or generated SQL, convert from dynamic SQL execution to static execution, replace literals with parameter markers and more, without changing the application code, and can quickly mitigate problem situations while a permanent design or application change is developed. In addition, a dominant malware trend is to inject SQL into the data entry fields of websites, giving hackers unsecured access to corporate assets. Optim gives DBAs the ability to control what SQL can be used through a client application blocking unexpected SQL from executing against the database server reducing the risk of data loss or corruption.
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 UIs to help new users deploy the best-practices template for workloads to enable experienced users to fine-tune WLM settings. WLM service classes are available for filtering connection and SQL detail OPM, and reports support configuration tuning and analysis for effective workload management deployment.
Optim Performance Manager also automatically manages longterm data aggregation and retention to enable proactive tuning and capacity planning. Built-in reporting shows memory usage and storage growth trends to help keep pace with business growth. SQL reports make it easy to identify high-cost SQL and packages, and compare SQL performance across migration boundaries. Figure 5 shows reports in Optim Performance Manager used to gauge workload definitions and to determine assignment to service classes.
Figure 5. Optim Performance Manager reports
Where growth trends exceed what was expected or new applications need to come online, organizations may need to scrape together additional storage to avoid budget exceptions. InfoSphere Optim Configuration Manager can help DBAs identify storage savings opportunities. See storage savings that can be achieved through use of reorganization, compression, or, potentially, objects not used recently (see Figure 6).
Figure 6. Visualize storage savings opportunities by database
DB2 AESE includes the following additional IBM InfoSphere Optim solutions that can help you develop and administer business-critical databases throughout their lifecycle:
- Data Studio 3.1.1
- InfoSphere Data Architect 8.1
- InfoSphere Optim Query Workload Tuner 3.1.1
- InfoSphere Optim pureQuery Runtime 3.1.1
- InfoSphere Optim Performance Manager 5.1.1
- InfoSphere Optim Configuration Manager 2.1.1
These have all been updated to support and help exploit new features in DB2 10. Using these tools, developers, DBAs, or security administrators can:
- Use Data Studio and InfoSphere Data Architect to design, develop, and deploy temporal tables, develop and validate time travel queries, and view row history.
- Use Data Studio to help safeguard data and enable security administrators to define, view, and deploy row and column control, and DBAs to analyze impact of new security controls and update routines accordingly.
- Use IBM Data Studio and Optim Performance Manager to monitor multiple standbys and alert availability risk due to stopped systems or standbys not keeping up.
- Use Data Studio and Optim Configuration Manager to get recommendations for adaptive compression use, deploy compression, and validate compression savings.
- Use Optim Configuration Manager and Optim Performance Manager to enhance the value of multi-temperature technology delivery by analyzing storage group growth and contents, enabling WLM configurations to prioritize queries against high-performing storage, and automating period roll-off for range partitioned tables to help lower storage cost.
- Analyze access plan improvements and regressions based on new DB2 10 optimization strategies and get actionable recommendations for improvements. Optim Query Workload Tuner is now the one-stop shop for query improvement recommendations subsuming all DB2 Design Advisor functions under its broader value-add capability.
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 run
INSPECTor to reorganize all tables manually.
- Optional automatic index compression — Unlike data or table
compression, which uses a dictionary, index compression uses up to
three 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 indices 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
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
group by, or
joinlarge 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.
- Adaptive compression — Parallel to the table based row-level compression automatic row compression at page level reduces the need for REORG to update the table-level dictionary. Page-level dictionaries are fully automated and will get recreated when data in the page changes sufficiently.
DB2 AESE is a complete database solution in a single price offering that delivers high value and return on your investment. 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.
- Be sure to read Part 2 of this series.
- DB2 Advanced Enterprise Server Edition has more information.
- Don't miss the IBM DB2 e-kit for Database Professionals.
- Learn more about Data lifecycle management solutions for IBM DB2 (PDF).
- Be sure to check out InfoSphere Optim Performance Manager Extended Edition for DB2 for Linux, UNIX, and Windows or "Real-time monitoring and more cool features in the new InfoSphere Optim Performance Manager V5.1 for DB2 for Linux, UNIX, and Windows" for more information.
- Tune like an expert with InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX and Windows.
- Learn more about IBM Data Studio.
- developerWorks has more information about Optim tools at InfoSphere Optim data lifecycle management.
- 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.
- Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
- Participate in the discussion forum.
- Check out the developerWorks blogs and get involved in the developerWorks community.