Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

Chapter 9. Managing data lifecyle with InfoSphere Warehouse 339

򐂰 DB2 Workload Manager

DB2 Workload Manager (DB2 WLM) can be configured and used in

conjunction with storage group data tags to manage the resources assigned

to workloads that access data in a multi-temperature database.

For example, by creating a tag on a storage group, DB2 WLM can be

configured to assign a workload to a certain service subclass depending on

the storage group accessed, and then effect the resources and concurrency

rules applied to that workload. Using this approach you can change the

workload priority of a query based on the storage groups it touches. This

gives the administrator more control over queries that might access both hot

and cold data.

򐂰 Temporal data

Creating a temporal table allows you to pass the processing task of

maintaining dimensional history over to the database engine. Deploying

temporal features for dimension tables further enhances your ability to

manage the data lifecycle, because temporal history data can be managed

separately from active data.

A change to a data row in a temporal table generates data in the associated

temporal history table. The temporal history table can be physically

implemented independently of the parent table. This means that it can be

partitioned, stored, and maintained (pruned) separately based on your

requirements.

For example, although a parent table might be range partitioned, a history

table with a smaller volume of rows does not have to be range partitioned,

correlating the maintenance effort with the table volume. Similarly, data can

be archived from the history table independently of the parent table.

򐂰 Federated database

In situations where you need to archive aged or inactive data out of the

primary production database but retain data availability, you can migrate the

data to a federated database from where it can be referenced.

9.2.1 Using multi-temperatures features in a sample scenario

This section describes how to implement the concepts discussed to effectively

manage the cost of storage. The example uses a standard FACT table, which is

typically the largest table in a data warehouse.

The target environment has three distinct types of storage available to the

database which represent distinct cost groups for storage. The object of the

exercise as discussed in this chapter is to place data for the fact table on the

appropriate storage based on age. Figure 9-1 on page 340 shows the target

340 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

environment where SSD, SAS, and SATA storage is utilized in placing data for a

single fact table across multiple storage tiers.

The approach to the data lifecycle is through the implementation of storage

group, table partitioning, and table space objects for a sales transactions fact

table. Each data partition and table space is designed to hold data for a calendar

quarter. Data for the current quarter is always maintained on the fastest storage.

As data ages, the table space associated with the data partition is moved to

warm storage and then cold storage.

Figure 9-1 Physical implementation of a fact table to support multi-temperature storage through the data

lifecycle

The following steps show how this environment was created, using the example

of the sales transactions table CUSTOMER_TXN as shown in Figure 9-1. Each

row in the table will be placed in a data partition, table space and storage group

based on the transaction date.

1. Create a storage group.

A storage group is characterized by having one or more storage paths and

performance statistics for the DB2 optimizer to reference. Using IBM Data

sg_coldsg_hot

SSD RAID Array FC/SAS RAID Array SATA RAID Array

2011Q1 2010Q4 2010Q3 2010Q2 2010Q1 2009Q4 2007Q3

sg_warm

TbSpc14

TbSpc13 TbSpc12 TbSpc11 TbSpc10 TbSpc9 TbSpc1

...

...

Data partition

Table: Sales

Range

Partitions

Table

Spaces

Storage

Groups

Legend

...