An inside look at DB2 Advanced Enterprise Server Edition, Part 1: Performance management and storage optimization

IBM® DB2® Advanced Enterprise Server Edition (AESE) is a high-value, single-price offering that includes all the functionality of the IBM DB2 Enterprise Server Edition 10, and it includes compression, workload classes prioritization, and heterogeneous replication. In addition, DB2 AESE includes tools to help with performance optimization, Java™ application development, and system management. A comprehensive solution for managing critical aspects of DB2 environments, DB2 AESE simplifies the acquisition, and use of core database features and complementary tools for development and deployment, administration, storage optimization, and performance optimization. With the inclusion of these capabilities in a single offering, you can reduce costs and improve your ability to manage the DB2 environment. This article focuses on performance management and storage optimization.

Share:

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
Figure 1 shows the health summary for a three-database environment

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
Figure 2 shows the diagnostic dashboards with details for component areas

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
Figure 3 shows the 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
Figure 4 shows the Query Tuner Wokrflow Assitant

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
Figure 5 shows reports in Optim Performance Manager

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
Figure 6 shows InfoSphere Optim Configuration Manager visualizing potential storage savings

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 YES to be eligible.) This feature removes the need for the DBA to run INSPECT or 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 set with COMPRESS YES is 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, or join large 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.

Conclusion

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.

Resources

Learn

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.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=643727
ArticleTitle=An inside look at DB2 Advanced Enterprise Server Edition, Part 1: Performance management and storage optimization
publish-date=06142012