Technologies

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

Agile development and data management for availability, storage, and performance optimization

Part 1 of this series discusses adaptive compression and temporal tables.

Multi-temperature storage

The multi-temperature feature provides a way to classify and manage data based on its temperature and access requirements. Organizations can optimize query performance and storage costs by using fast, costly storage only for frequently accessed, critical—hot—data and dynamically moving infrequently accessed—warm or cold—data to slower, cost-effective storage.

Organizations can deploy IBM® Data Studio software to create and maintain storage groups and related database artifacts. A new category is added for the storage group, and normal context menu actions are also available. Temperature can be assigned for the data using a slider calibrated from hot to cold (see Figure 12).

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

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

 
Starting with IBM InfoSphere® Optim™ Performance Manager 5.1 software, storage group metrics can be viewed on various dashboards (see Figure 13).

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

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

 
Capacity planning can be simplified with the Storage Group report. This report shows an overview of the table spaces used by the storage groups during a specified period, which helps end users better understand utilization and growth (see Figure 3).

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

Figure 14. An InfoSphere Optim Performance Manager Storage Group report

 
The IBM DB2® Workload Manager Configuration dashboard allows organizations to map an activity to a different service class that accesses specific storage groups at run time. Assign the data tag in Data Studio based on the temperature of the storage group, and then specify this assignment in the Workload Manager dashboards in InfoSphere Optim Performance Manager (see Figure 15).

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

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

 
Using InfoSphere Optim Configuration Manager, 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 (see Figure 16).

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

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

IBM DB2 pureScale enhancements

IBM DB2 pureScale™ software provides 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 (see Figure 17).

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

Figure 17. Information about DB2 pureScale in Data Studio

 
InfoSphere Optim Performance Manager 5.1 adds 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. There are several areas in which InfoSphere Optim Performance Manager is enhanced to support DB2 pureScale (see Figure 18).

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

Figure 18. 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 (see Figure 19).

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

Figure 19. InfoSphere Optim Performance Manager dashboard with CF metrics

 
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 (see Figure 20).

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

Figure 20. 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 (see Figure 21).

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

Figure 21. 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 (see Figure 22).

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

Figure 22. Example of column data masking in Data Studio

 
Creating row permissions that allow access data in specific or all rows is now possible. For example, only the ADMIN user is allowed to access all rows (see Figure 23).

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

Figure 23. 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 (see Figure 24).

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

Figure 24. 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 (see Figure 25).

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

Figure 25. HADR information in InfoSphere Optim Performance Manager dashboard

 
IBM DB2 Advanced Enterprise Server Edition (AESE) provides a highly 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 end users make the most of new, powerful features in DB2 10 with simple and intuitive user interfaces throughout the entire data lifecycle.

Please share any thoughts or questions in the comments.

Previous post

IIUG Informix Conference in San Diego

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™ software Solutions Technical Enablement team at the Silicon Valley Lab in San Jose, 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.