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

IBM® DB2® 10 Advanced Enterprise Server Edition (AESE) is a complete database solution that provides a host of development, optimization, and management tools for use with data-intensive applications. Running on Linux, UNIX, and Windows, DB2 10 includes dynamic new features that IBM InfoSphere® Optim™ Database Tools are designed to exploit and enhance. And because they are included in DB2 10 AESE, these tools help lower operational costs, ease development, and provide greater reliability—right out of the box (see Figure 1).

Figure 1. Supported DB2 10 features in InfoSphere Optim Tools.

Adaptive compression

This feature provides better compression ratios at the table and page levels than previous versions of DB2. In addition, the feature adapts to changing data patterns, which helps reduce total database costs by saving storage space and allowing more data to be loaded into memory. As a result, organizations can maintain a smaller database footprint with increased buffer pool capacity, less I/O, and faster backup, restore, and reorg.

IBM Data Studio helps database developers and administrators manage and create database environments for increased productivity and collaboration. In Data Studio, you can create a new table or index with the new adaptive compression feature. With existing tables, you can edit their properties to use adaptive compression and Data Studio will generate the needed statements (see Figure 2).

Figure 2. DDL generated by Data Studio for changing an existing table with adaptive compression.

Data Studio also provides statistics for the compression (see Figure 3).

Figure 3. Statistics with compression data provided in Data Studio.

InfoSphere Optim Configuration Manager offers centralized management of database and client configurations. To quantify the advantage of using adaptive compression over regular compression, you can use Optim Configuration Manager to discover these compression-saving opportunities for tables and indexes.

Figure 4. Estimated storage savings for compression per database object.

For each database, Optim Configuration Manager provides estimated storage savings for all the tables with trapped storage that can be reclaimed by running reorg. Optim Configuration Manager also identifies database objects that would benefit the most from compression. To reduce storage requirements, you can also use Optim Configuration Manager to explore and monitor databases for opportunities to reclaim trapped storage in tables, apply compression to tables, and eliminate seldom-used tables.

Temporal tables

This feature allows you to have versioned data with different values at various points in time to support history or audit queries. As the logic moves from the application layer to the database layer, you can store and retrieve time-based data using the built-in functionality without having to build your own customized solution.

InfoSphere Data Architect provides a comprehensive development environment for data modeling, relating and integrating data assets, and developing database applications. In InfoSphere Data Architect 8.1 or later, you can create, model, and reverse-engineer system-period, business-period, and bi-temporal tables. When creating a temporal table in a logical model, you can specify the time period columns in the table’s properties (see Figure 5).

Figure 5. A logical data model for a temporal table.

InfoSphere Data Architect allows transformation of a logical model of a temporal table to a physical model with automatic addition of the history table (see Figure 6).

Figure 6. A physical data model transformed from a logical model with a temporal table.

InfoSphere Data Architect also generates the DDL for you with the needed temporal attributes (see Figure 7).

Figure 7. DDL generated by InfoSphere Data Architect for a temporal table.

Similarly, Data Studio provides extensive support for temporal tables. When you create a table with temporal attributes, Data Studio detects this and gives you the option to establish a versioning relationship with a history table (see Figure 8).

Figure 8. A table with temporal attributes specified in Data Studio.

After the temporal table is defined, Data Studio provides support for deploying and managing changes to the tables. Data Studio generates the DDL required to create both the temporal table and the corresponding history table (if selected), as shown in Figure 9.

Figure 9. DDL generated by Data Studio for temporal and history tables.

Now, assume you want to add a column called VACATION_DAYS to the EMPLOYEES table. Data Studio knows that the EMPLOYEES table is a temporal table and generates the ALTER statements for the EMPLOYEES table and the history table, along with the needed utility commands (see Figure 10).

Figure 10. Commands generated to deploy a temporal table.

InfoSphere Optim Performance Manager and Extended Insight provide a comprehensive, proactive performance management solution for database applications. InfoSphere Optim Query Workload Tuner offers expert recommendations to DBAs to help improve query workload performance. Both Optim Performance Manager and Optim Query Workload Tuner have added support for monitoring and tuning workloads containing temporal tables. For example, Optim Query Workload Tuner includes the history table in the access path analysis (see Figure 11).

Figure 11. Access path analysis with a history table.

This article is continued in Part 2.