Databases

Exploiting DB2 10 Features with IBM InfoSphere Optim Tools: Part 2

Agile development and data management for availability, storage, and performance optimization
This article is continued from Part 1, which discussed adaptive compression and temporal tables.

 

Multi-temperature storage

This feature provides a way to classify and manage data based on its “temperature” and access requirements. You can optimize query performance and storage costs by using faster, more expensive storage only for frequently accessed, critical “hot” data, and dynamically moving infrequently accessed “warm” or “cold” data to slower, less expensive storage.

With IBM Data Studio, you can create and maintain storage groups and related database artifacts. A new category is added for storage group, and normal context menu actions are also available. You can assign “temperature” for the data using a slider calibrated from “hot” to “cold” as shown in Figure 1.

Figure 1. Support for multi-temperature storage in Data Studio

 

Starting with InfoSphere Optim Performance Manager 5.1, storage group metrics can be viewed on various dashboards.

Figure 2. Support for storage groups in the InfoSphere Optim Performance Manager dashboard

 

You can simplify capacity planning with the new Storage Group report. This report shows an overview of the table spaces used by the storage groups during a specified period, which helps users better understand utilization and growth.

Figure 3. An InfoSphere Optim Performance Manager Storage Group report

 

The DB2 Workload Manager Configuration dashboard allows you to map an activity to a different service class that accesses specific storage group at run time. You assign the data tag in Data Studio based on the temperature of the storage group, then specify this in the InfoSphere Optim Performance Manager’s Workload Manager dashboards.

Figure 4. Data tags used in Data Studio and InfoSphere Optim Performance Manager’s Workload Manager dashboard

 

Finally, with InfoSphere Optim Configuration Manager, you can automate the movement of range-partitioned data from one storage group to another by defining jobs that move data to storage groups based on age criteria.

Figure 5. Example of defining data movement to specific storage group based on age in InfoSphere Optim Configuration Manager

 

IBM DB2 pureScale enhancements

DB2 pureScale is a cluster-based, shared-disk architecture for application cluster transparency and continuous availability. It has been enhanced to support multiple active databases for easy multi-tenancy, geographically-dispersed clusters for disaster recovery and range partitioning.

With Data Studio, a new DB2 pureScale Hosts folder in the Administration Explorer shows the list of members, cluster caching facilities (CFs), and associated information. You can launch task assistants to start, stop, quiesce selected members or CFs, change their configuration parameters, and manage the maintenance mode of the DB2 pureScale host.

Figure 6. Information about DB2 pureScale in Data Studio

 

InfoSphere Optim Performance Manager 5.1 added extensive support for monitoring the members, CFs and cluster host status; and tracking global or member-specific metrics. Member-level drill-down of the monitoring metrics is available in all in-flight dashboards. Figure 7 lists the areas where InfoSphere Optim Performance Manager is enhanced to support DB2 pureScale.

Figure 7. Areas where InfoSphere Optim Performance Manager supports DB2 pureScale

 

Member-level drill-down of the monitoring metrics is available in most of the dashboards. For example, CF metrics are highlighted in the overview dashboard.

Figure 8. InfoSphere Optim Performance Manager dashboard with CF metrics

 

You can view the end-to-end response time of applications running against a single member, or compare response times across all members in the Extended Insight dashboard.

Figure 9. DB2 pureScale support in InfoSphere Optim Performance Manager

 

InfoSphere Optim Configuration Manager supports exploring and tracking configuration changes across clients and members. In pureScale systems, InfoSphere Optim Configuration Manager can configure workload balancing among the members.

 

Zigzag join

A new join method called zigzag join (ZZJOIN) is introduced to improve performance of queries based on a star schema. This join method filters the fact table on two or more dimension tables simultaneously and skips inefficient probes into fact table.

You can now visualize zigzag joins in the access plan graph launched from Data Studio and InfoSphere Optim Query Workload Tuner.

Figure 10. Access plan graph with new zigzag join

 

Furthermore, zigzag join is now considered in the Access Plan Explorer and in the generating Index Advisor recommendations.

 

Row and Column Access Control (RCAC)

This feature enhances security for sensitive data through fine-grained access control to a table at the row and/or column levels with filtering and data masking.

With Data Studio, you can enable RCAC conditionally. For example, the first 10 digits of customer credit card number column can be masked when displayed, as shown in Figure 11.

Figure 11. Example of column data masking in Data Studio

 

You can also create row permissions that allow access data in specific or all rows. For example, in Figure 12, only the ADMIN user is allowed to access all rows.

Figure 12. Example of row access control

 

Enhanced High Availability Disaster Recovery (HADR)

This feature enables IT departments to extend their HADR strategies with additional multiple copies of standby databases in various remote locations.

InfoSphere Optim Configuration Manager supports tracking and reporting of configuration changes across clients on HADR servers. With Data Studio, you can configure and manage HADR database using Task Assistants launched from the Administration Explorer.

Figure 13. Managing HADR in Data Studio

 

In addition, in Data Studio Web Console and InfoSphere Optim Performance Manager, the health alerts have been enhanced. New alerts include standby connection and readiness, logging issues between the primary and standby, and distinguishing a single failed standby from last remaining failed standby.

Figure 14. HADR information in InfoSphere Optim Performance Manager dashboard

 

Conclusion

IBM DB2 Advanced Enterprise Server Edition (AESE) provides the most comprehensive version of DB2 10 for Linux, UNIX, and Windows. This package combines storage and performance optimization capabilities with a rich suite of data management solutions. The InfoSphere Optim Tools included in AESE help users make the most of new, powerful features in DB2 10 with simple and intuitive user interfaces throughout the entire data lifecycle.

What do you think? Let us know in the comments!

Previous post

See You in San Diego for the 2013 IIUG Informix Conference

Next post

Large-Scale Data Management in PureData/Netezza: Part 2

Thuan Bui

Thuan Bui has worked more than 25 years for IBM in development, quality assurance, customer support, performance, and management positions in database and data management technology. He currently works on the IBM InfoSphere Optim Solutions Technical Enablement team at Silicon Valley Lab, California.

Marichu Scanlon

Marichu Scanlon is a member of the Optim Tools Enablement Team at the IBM Silicon Valley Laboratory in San Jose, CA. She has actively participated in presenting, demoing, and assisting customers with deploying several Optim Tools products such as InfoSphereOptim Performance Manager, InfoSphere Optim Query Workload Tuner, and InfoSphere Optim Configuration Manager. Before joining the enablement team, she was the team lead for the Data Studio Continuing Engineering team. She was also a member of Data Studio's SQL and Routine tooling development.