Exploiting DB2 10 Features with IBM InfoSphere Optim Tools: Part 2
Part 1 of this series discusses adaptive compression and temporal tables.
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).
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).
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).
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).
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).
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).
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).
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).
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).
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.
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).
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).
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).
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).
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).
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.